44 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 , ...
user avatar
34 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 ...
user avatar
  • 1,128
33 votes
Accepted

Delete all data in Postgres database

You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using drop owned by adminuser If you then create the SQL dump including the create ...
user avatar
30 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 ...
user avatar
24 votes

Querying JSONB in PostgreSQL

This does not work because it's trying to cast a jsonb value to integer. select data->'name' as name from persons where cast(data->'age' as int) > 25 This would actually work: SELECT data->'name' ...
user avatar
22 votes
Accepted

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads. This means that before that time, Los Angeles used actual local time, based on mean solar time. ...
user avatar
  • 544
22 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 ...
user avatar
22 votes

Constraint - one boolean row is true, all other rows false

Variant 1 Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it: CREATE TABLE ...
user avatar
21 votes

Inserting 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) ;
user avatar
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;...
user avatar
17 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 ...
user avatar
17 votes

Constraint - one boolean row is true, all other rows false

You can use a Filtered index create table test ( id int primary key, foo bool ); CREATE UNIQUE INDEX only_one_row_with_column_true_uix ON test (foo) WHERE (foo); --> where foo is ...
user avatar
  • 22.7k
16 votes

What is the best way to create Primary Key on a large table in Postgresql 9.5?

You can create a unique index with the option concurrently which will allow read and write access to the table while the index is created. However, building an index concurrently will take much longer ...
user avatar
16 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" (space advantage). It might also ...
user avatar
  • 12.4k
16 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 ...
user avatar
15 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, ...
user avatar
  • 1,378
14 votes
Accepted

How to get the ID of the conflicting row in upsert?

This will work (as far as I tested) in all 3 cases, if the to-be-inserted values are all new or all already in the table or a mix: WITH val (name) AS ( VALUES -- rows ...
user avatar
13 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 – ...
user avatar
  • 55.7k
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)...
user avatar
12 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 $$ ...
user avatar
  • 1,924
12 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 ...
user avatar
  • 251
10 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 ...
user avatar
10 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: ...
user avatar
10 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 ...
user avatar
10 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 (...
user avatar
  • 28.7k
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)...
user avatar
  • 28.7k
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 ...
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: ...
user avatar
10 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,...
user avatar
9 votes
Accepted

Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

No, You can't control a transaction inside a plpgsql function (or anonymous block). The only option that you have its creating a transaction outside the block, eg: BEGIN; DO $$ -- function stuff ...
user avatar

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