New answers tagged

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....
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 ...
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.
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 ...
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('...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...

Top 50 recent answers are included