Postgres on conflict do update on composite primary keys

Just place both keys in the ON CONFLICT clause: INSERT INTO answer VALUES (1,1,’q1′) ON CONFLICT (person_id,question_id) DO UPDATE SET answer = EXCLUDED.answer; Example: INSERT INTO answer VALUES (1,1,’q1′) ON CONFLICT (person_id,question_id) DO UPDATE SET answer = EXCLUDED.answer; SELECT * FROM answer; person_id | question_id | answer ———–+————-+——– 1 | 1 | q1 (1 Zeile) … Read more

Is SELECT or INSERT in a function prone to race conditions?

It’s the recurring problem of SELECT or INSERT under possible concurrent write load, related to (but different from) UPSERT (which is INSERT or UPDATE). This PL/pgSQL function uses UPSERT (INSERT … ON CONFLICT ..) to INSERT or SELECT a single row: CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) LANGUAGE plpgsql AS $func$ BEGIN … Read more

How to update all columns with INSERT … ON CONFLICT …?

The UPDATE syntax requires to explicitly name target columns. Possible reasons to avoid that: You have many columns and just want to shorten the syntax. You do not know column names except for the unique column(s). “All columns” has to mean “all columns of the target table” (or at least “leading columns of the table”) … Read more

How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?

I believe xmax::text::int > 0 would be the easiest trick: so=# DROP TABLE IF EXISTS tab; NOTICE: table “tab” does not exist, skipping DROP TABLE so=# CREATE TABLE tab(id INT PRIMARY KEY, col text); CREATE TABLE so=# INSERT INTO tab(id, col) VALUES (1,’a’), (2, ‘b’); INSERT 0 2 so=# INSERT INTO tab(id, col) VALUES (3, … Read more

How to correctly do upsert in postgres 9.5

The ON CONFLICT construct requires a UNIQUE constraint to work. From the documentation on INSERT .. ON CONFLICT clause: The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or … Read more

UPSERT *not* INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE BAD: This will insert or replace all columns with new values for ID=1: INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, ‘John Foo’, ‘CEO’); BAD: This will insert or replace 2 of the columns… the NAME column will be set to NULL or the … Read more

UPSERT in PostgreSQL using jOOQ

jOOQ 3.7+ supports PostgreSQL 9.5’s ON CONFLICT clause: https://github.com/jOOQ/jOOQ/issues/4299 http://www.postgresql.org/docs/9.5/static/sql-insert.html The full PostgreSQL vendor-specific syntax is not yet supported, but you can use the MySQL or H2 syntax, which can both be emulated using PostgreSQL’s ON CONFLICT: MySQL INSERT .. ON DUPLICATE KEY UPDATE: DSL.using(configuration) .insertInto(TABLE) .columns(ID, A, B) .values(1, “a”, “b”) .onDuplicateKeyUpdate() .set(A, “a”) … Read more

Does DB2 have an “insert or update” statement?

Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert). MERGE INTO target_table USING source_table ON match-condition {WHEN [NOT] MATCHED THEN [UPDATE SET …|DELETE|INSERT VALUES ….|SIGNAL …]} [ELSE IGNORE] See: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873.html https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en