New answers tagged postgresql
0
votes
Mismatch between the total number of rows in a sql join
I would like each row in the points table to join to just one candidate row in the mid_pts table.
Simple solution:
SELECT DISTINCT ON (p.ctid)
m.geom, m.lic_li_no, m.orig_licli, p.id, p....
- 163k
0
votes
Are 'Functional Indexes' Supported in MS SQL Server?
You could emulate functional indexes via computed columns:
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns
There are few restrictions:
All functions ...
- 457
0
votes
Performant design for updating/creating multiple rows at high frequency
Generally speaking, since we don't know the details of your implementation, you should consider this:
Establishing (opening) a connection with the database is a heavy operation, so of course you have ...
- 618
0
votes
Filter by list of values pairs
adjust you query for:
select count(*) from optprice
left join prod on optprice.prod = prod.id
where price between 10 and 500 limit 20;
and create a index optprice.price
i believe that performance is ...
- 1
0
votes
Insert values and add values to foreign key
So you want to get IDs from a lookup table, or generate new ones on the fly if missing.
First, sanitize your relational design.
Proper design
CREATE TABLE est (
est_id serial PRIMARY KEY
, est_nom ...
- 163k
0
votes
Insert statement fails silently when a FK constaint can't be statically evaluated
Try to use the semicolon at the end of your query to get the debug information.
In my case case it was failing because it was not able to find the end of insert statement.
- 101
0
votes
How to improve performance on query with nested INs?
in postgres 15, you can use MERGE.
BEGIN;
CREATE TABLE challenge_progress (
"current" int,
status text,
milestone_id int
);
CREATE TABLE challenge_milestones (
milestone_id ...
- 263
1
vote
Insert values and add values to foreign key
Join with est and don't insert the id column, so that it is auto-generated:
INSERT INTO bm (est_nom, nom)
SELECT est.est.id, v.nom
FROM est
JOIN (VALUES
('DAPR', 'leopold stotch'),
...
- 40.6k
4
votes
Why not use two columns instead of collation for case insensitive data?
I did some testing with all approaches plus the case-sensitive one as a baseline by inserting 1,000,000 entries to each table with random strings of random case of equal length, and here are the ...
- 251
1
vote
PostgreSQL group by with just primary key is allowed on tables but why not views?
Duplicate of GROUP BY on view queries found by a coworker.
- 121
0
votes
Is it safe to use lastval/currval for multiple inserts?
currval is safe in the face of concurrent threads/sessions doing inserts at the same time.
But it is not safe in the face of triggers. Your best bet is to use a RETURNING clause. See also this answer ...
- 8,830
0
votes
Is it safe to use lastval/currval for multiple inserts?
A method using sequences and functions. Slightly different building and families table definitions.
I think it'll only work when called from a programming language (i.e. not directly in psql), since ...
- 559
-1
votes
How to transpose values from one column into columns with a value from a different column?
select
role,
sum(case when type = 'Cleaning' then duration else 0 end) as Cleaning,
sum(case when type = 'Food preparation' then duration else 0 end) as Food preparation
from work
group by role;
I ...
11
votes
Why not use two columns instead of collation for case insensitive data?
Third option:
CREATE TABLE test
(
original_email TEXT NOT NULL
);
CREATE UNIQUE INDEX test_email_uniq on test(lower(original_email));
INSERT INTO test (original_email)
VALUES ('IVAN@email.com');
...
- 2,374
7
votes
Why not use two columns instead of collation for case insensitive data?
The downsides of using a generated column are the wasted space and the processing time it takes to calculate the computed column whenever you modify the row. On the other hand, the index would be ...
- 40.6k
3
votes
Accepted
Need assistance converting datetimeoffset in SQL Server
SQL Server: 2022-06-06 10:31:13.1707995 -05:00
PostgreSQL: 2022-06-06 10:31:13.1708 -05:00
Is there a way I can convert the value in SQL Server to the PostgreSQL
value using existing SQL Server ...
- 26.3k
0
votes
Getting ERROR: invalid transaction termination CONTEXT: PL/pgSQL function run(text,text,text,double precision) line 147 at COMMIT
Your indentation is creative, but the problem is here:
BEGIN
[...]
commit;
EXCEPTION
WHEN others THEN
[...]
END;
When you enter a BEGIN / END block that has an EXCEPTION handler, ...
- 40.6k
0
votes
How come systemctl status postgresql.service and systemctl status postgresql@10-main show different results?
I have faced the same issue when I changed the pg_hba.conf file.I am using Linux VM in order to change the pg_hba.conf file I had to change the /car/lib/pgsql/15/data permission but after the changes,...
- 1
1
vote
Accepted
Return value from WITH clause in PL/pgSQL
I don't see any reason why INSERT and MERGE would have to be in the same statement. (The whole function runs within the same transaction in any case.) Just split them up, and save the resulting id in ...
- 163k
1
vote
Why can't three non-unique indexes be used to enforce a unique constraint?
A multi-column unique constraint can only be enforced by keeping track of every combination of values, as that is what a multi-column constraint represents.
I created a separate btree index on every ...
- 8,830
1
vote
Return recursive self joins in PostgreSQL with aggregated data
After more research and referencing
Database model for a hierarchical content, I have the following query for some of my requirements. It is not recursive though.
SELECT
c.id AS c_id,
c....
- 65
2
votes
Index for searching in column where the column starts with the provided value
It seems like all possible substrings of your input are delimited with the character /. To illustrate my point:
SELECT string_agg(part, '/') OVER (ORDER BY ord) AS prefix
FROM string_to_table('...
- 163k
1
vote
PostgreSQL: Do regclass/oid columns persist across backups?
Whether or not a OID persists across backups depends on whether you use a “raw” oid type or one of its subcatalog-specific aliases. The reason has everything to do with the fact that when you cast oid ...
- 439
1
vote
Index for searching in column where the column starts with the provided value
Q: SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%') which gives what I want, but it always does a seq
scan of the table, even though that I have an index on the ...
- 29.2k
0
votes
Create Sequence Permission for Postgres
An example of how to do this with an event trigger is
CREATE OR REPLACE FUNCTION check_ddl_event_user()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (SELECT current_user) = 'USER_TO_BLOCK'...
- 1
0
votes
Accepted
Need to drop a function by oid value (I think)
Don't cause sorrow for all involved by messing with the catalogs.
Instead, figure out in which schema the functions are. Unless you are dealing with actual data corruption, tow functions can only have ...
- 40.6k
1
vote
Accepted
pg_dump: error: no matching tables were found
You are confused between long and short options. The documentation says:
-t pattern
--table=pattern
That means that pg_dump is looking for a table called =dba.mytable, which explains the error ...
- 40.6k
-1
votes
Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?
This should work:
SET LOCAL statement_timeout = 100;
BEGIN;
SELECT pg_sleep(10);
COMMIT;
- 99
1
vote
Accepted
Add composite primary key on existing PostgreSQL table
Will there be any downtime?
While CREATE INDEX only blocks concurrent writes and still allows reads, you need ALTER TABLE to add the PRIMARY KEY constraint, and that takes an ACCESS EXCLUSIVE lock on ...
- 163k
1
vote
Is it possible to insert cyclic foreign keys that are AUTO INCREMENT primary keys?
Cyclic FK constraints are always a problem.
One way to deal with this, is to fetch the "next ID" manually using nextval(). In order to not hardcode the underlying sequence names, ...
1
vote
Window function with subquery results to unexpectedly slow query plan
Example of join instead of IN
SELECT s.*,
lag(quantity)
over (partition by s.product_id, s.size_id, s.warehouse_id
order by s.size_id, s.warehouse_id, s.created_at ASC) -
s.quantity ...
- 22.6k
1
vote
How can I exclude some tables when using pg_restore?
These options exist for pg_dump, but not for pg_restore. So one thing you can do is dump the data again and use the options with pg_dump.
Other than that, you can create an inventory file with
...
- 40.6k
1
vote
How to determine if a query can potentially deadlock in Postgres?
I don't think that there is a principled formal approach to this. Besides, while it can happen, it is not a frequent occurrence that a single statement deadlocks with the same statement run in a ...
- 40.6k
0
votes
Window function with subquery results to unexpectedly slow query plan
Without knowing the full table schema and number of rows in each table, here is an example of query using join. You need to update it for the partition as required
select prd.check_id, stk.*
from ...
- 973
0
votes
Using pgagent with docker?
I would suggest creating one from scratch.
Install pgagent inside the postgres container:
docker exec -it <postgresql_container> bash
apt-get update && apt-get install pgagent
Install ...
0
votes
Accepted
Should I create another table for expensive query?
This is a classic reason for the existence of Timescale. This basic candlestick query becomes something like:
SELECT symbol, first(price,time), last(price, time), price,
...snip
your calculations ...
- 41
0
votes
Window function with subquery results to unexpectedly slow query plan
As @mustaccio points out, you have an ORDER BY in the subquery, and this is defeating the optimizer. This sub-select is a great candidate to be "pulled up" into a join and optimized as join ...
- 41
0
votes
Is it possible to insert cyclic foreign keys that are AUTO INCREMENT primary keys?
You should rearrange your design so that you instead have an is_correct flag on the answer table.
You can enforce a unique correct answer by using a UNIQUE NULLS DISTINCT constraint, keeping the ...
- 8,830
0
votes
Accepted
pg_stat_statements slows down the database
Try turning off/ setting zero for all the pg_stat_statements parameters in the parameter group. I had the same problem and this fixed the issue.
Also see the related issue pg_stat_statements causing ...
4
votes
Accepted
PostgreSQL Row Level Security: Can we have an Update-policy only?
The principle is: everything that is not explicitly allowed is forbidden. So as soon as you run
ALTER TABLE tab ENABLE ROW LEVEL SECURITY;
only the owner, superusers and roles with BYPASSRLS can ...
- 40.6k
1
vote
Postgres long transaction holding advisory lock
If your database transaction does nothing except for holding an advisory lock, it won't cause any trouble in the database, even if it takes a very long time. You can safely use that for application ...
- 40.6k
0
votes
Accepted
Will partitions speed up data ingestion for Postgresql?
Partitioning might help you here, by keeping the partition's UID index smaller. Maintaining the index on the random UID will involve jumping to some random leaf page of the index and dirtying it. ...
- 35.6k
2
votes
Accepted
Collation version mismatch
I solved the problem as follows
I didn't need to rebuild my database, I don't know if this could cause a problem in the future.
Before performing the steps here, make a backup of your database.
My ...
6
votes
PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
first_last_agg
There is the additional module first_last_agg to make this simple.
It's available from apt.postgresql.org (among others). Read instructions in the Postgres Wiki. Install it once per ...
- 163k
9
votes
Accepted
Query performance on static, large PostgreSQL table
Based on this:
protein_id has 1,000 unique values
snp_id has 7,500,000 unique values
the pair (snp_id, protein_id) uniquely determines a row in the table
Typical queries:
(a) single SNP/protein ...
- 163k
0
votes
Accepted
Query optimisation - how to reference lateral join table to avoid duplicate lookup
If they were both slow, it could be that combining them reduce the time. But one of the lateral joins is essentially free, and it is hard to see how combining them would make the slow one any faster ...
- 35.6k
0
votes
ERROR: extra data after last expected column when inserting data into PostgreSQL from a CSV file
If you look in the server's log file, it will give you the text of the command which failed. Although that usually shouldn't be necessary; if you are running interactively, you should know what ...
- 35.6k
2
votes
Accepted
How can I finish a "RANGE type" partition in PostgreSQL?
I cannot be certain that I understood what you want, but perhaps this can help you. To transform your table into a partitioned table, you have to copy the data (which means a longer down time). ...
- 40.6k
0
votes
Postgres long transaction holding advisory lock
I have used a dedicated table (call ot locks or backends) to insert a row that contains the server name and timestamp.
Each backend creates a row if missing or the timestamp is too old (say by 10 ...
- 973
Top 50 recent answers are included