Skip to main content
46 votes

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Multiple problems. Your setup, extended: CREATE TABLE a ( pk_a int PRIMARY KEY , a int , comment text -- added column to make effect clear ); CREATE TABLE b ( pk_b int PRIMARY KEY , b int , ...
Erwin Brandstetter's user avatar
36 votes
Accepted

psql 9.5: gen_random_uuid() not working

you can check if the functions are defined using select pg_get_functiondef(to_regproc('gen_random_bytes')); select pg_get_functiondef(to_regproc('gen_random_uuid')); or: select * from pg_proc where ...
cohenjo's user avatar
  • 1,188
34 votes
Accepted

PostgreSQL UPSERT issue with NULL values

Clarify ON CONFLICT DO UPDATE behavior Consider the manual here: For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by ...
Erwin Brandstetter's user avatar
26 votes

How to insert 10000 new rows?

You can use the generate_series() set returning function: INSERT INTO bins (id) SELECT g.id FROM generate_series(1, 10000) AS g (id) ;
ypercubeᵀᴹ's user avatar
23 votes
Accepted

How to create an index on CASE expression in Postgres

You need to add extra parentheses around the CASE expression: CREATE UNIQUE INDEX test_index ON test ((CASE WHEN i=1 THEN j END)) ; As the docs state in CREATE INDEX: The key field(s) for the ...
ypercubeᵀᴹ's user avatar
19 votes

Postgres: How is SET NOT NULL "more efficient" than CHECK constraint

My wild guess: "more efficient" means "less time is required to perform the check" (time advantage). It may also mean "less memory is required to perform the check" (...
joanolo's user avatar
  • 13.5k
19 votes
Accepted

How can I return multiple rows of records in PL/pgSQL

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in: CREATE FUNCTION test() RETURNS SETOF RECORD AS $$ DECLARE rec record;...
Daniel Vérité's user avatar
18 votes
Accepted

Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file

These are 16 MB WAL segments by default. The manual: The system physically divides this sequence into WAL segment files, which are normally 16MB apiece (although the segment size can be altered ...
Erwin Brandstetter's user avatar
18 votes
Accepted

How to use function parameters in dynamic SQL with EXECUTE?

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here. CREATE OR REPLACE FUNCTION insert_records_for_notification( _username text ...
Erwin Brandstetter's user avatar
16 votes
Accepted

Permission denied to create "pg_catalog.tablename" during pg_dump targetting another machine

This turns out to be due to a somewhat obscure error message which happens when the schema you are dumping from with pg_dump does not exist on the target database/machine. I will leave this question, ...
John Powell's user avatar
  • 1,578
16 votes

How can I return multiple rows of records in PL/pgSQL

Use setof record and return next rec if you want to return multiple records from a function, example: create or replace function test_function() returns setof record language plpgsql as $$ ...
klin's user avatar
  • 2,184
15 votes
Accepted

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead. This works: INSERT ...
ypercubeᵀᴹ's user avatar
14 votes

Refresh a PostgreSQL materialized view automatically without using triggers

As a_horse_with_no_name said in a comment: No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – ...
Evan Carroll's user avatar
  • 63.6k
14 votes
Accepted

PostgreSQL 9.5 won't start after Windows 10 fall update

Thanks to suggestion from Daniel Vérité I was able to fix it without dumping the entire db server. My understanding of locales on Windows is very limited but from what I've learned it seems like ...
jvb's user avatar
  • 281
13 votes

Alter default privileges for role

janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. You got at least one aspect of the command backwards in all your attempts: Alter default privileges ...
Erwin Brandstetter's user avatar
12 votes

UPSERT with ON CONFLICT using values from source table in the UPDATE part

When doing upserts in PostgreSQL 9.5+ you must refer to the excluded data (that which failed to insert) by the alias excluded. Also, the on conflict option must refer to the key: (pk_b) rather than (b)...
Ezequiel Tolnay's user avatar
12 votes
Accepted

Find which functions are used

Yes, use the statistics collector. The manual: PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. [...] It can also ...
Erwin Brandstetter's user avatar
11 votes
Accepted

How to compare xmin and txid_current() after transactions ID wraparound?

You can strip the added epoch to match the value in xmin, i.e. extract the 4-byte integer from the bigint. Since xmin is type xid and not (signed!) integer, we compare the text representation instead: ...
Erwin Brandstetter's user avatar
11 votes
Accepted

Why do my queries take longer together than when I run them separately?

Debunking If you are doing all your updates in the same transaction, each of them will have to work an increasingly bigger set of (physical) tuples. See the following example: CREATE TABLE explode (...
András Váczi's user avatar
11 votes
Accepted

Find rows in which the column doesn't contain a "space"

I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex: where name !~ '[ \t\v\b\r\n\u00a0]' Characters: \s white space (space, \r,...
ypercubeᵀᴹ's user avatar
11 votes
Accepted

Changing foreign key to ON DELETE CASCADE with least amount of impact

The docs say this about NOT VALID ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently ...
Evan Carroll's user avatar
  • 63.6k
11 votes
Accepted

PostgreSQL; Update different column on condition using CASE statement

It is to be something like (logic only) update table_A set column_A = case when (column_A>table_B.balance) then value else column_A end, column_B = case when not (column_A>table_B....
Akina's user avatar
  • 20k
11 votes

Postgresql extremely slow count (with index, simple query)

Fetching and counting 5 million rows is slow business. There are two problems: The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with ...
Laurenz Albe's user avatar
  • 53.4k
10 votes
Accepted

Execute a series of commands in PSQL

If you are using psql 9.6+, there is a very convenient command called \gexec: Sends the current query input buffer to the server, then treats each column of each row of the query's output (if any)...
András Váczi's user avatar
10 votes
Accepted

Aggregated column causes full table scan even though the right index is present

The problem is that your 2nd query: SELECT datasets.id FROM datasets GROUP BY datasets.id ORDER BY date_added LIMIT 25 ; does not mean what you expect. It does give you the first 25 rows ordered ...
ypercubeᵀᴹ's user avatar
10 votes
Accepted

Why does this implicit join get planned differently than an explicit join?

Isn't the implicit syntax just rewritten to be the same as the explicit syntax? Not necessarily. You are building on slightly incorrect assumptions. Like I explained under the referenced question: ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Width in Postgres EXPLAIN?

Answer is in the article you are referring to: Here the planner has decided to use a two-step plan: the child plan node visits an index to find the locations of rows matching the index ...
Stoleg's user avatar
  • 2,388
9 votes
Accepted

How to use RETURNS TABLE with an existing table in PostgreSQL?

PS: sorry for my comments, I was developing a complex code and I have a small error that seems a stupid PostgreSQL restriction on a "returing table" part... I was stupid, instead of ...

Only top scored, non community-wiki answers of a minimum length are eligible