Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2); 

I've now switched over to PostgreSQL and apparently this is not correct. It's referring to all the correct tables so I assume it's a matter of different keywords being used but I'm not sure where in the PostgreSQL documentation this is covered.

To clarify, I want to insert several things and if they already exist to update them.

like image 800
Teifion Avatar asked Jul 10 '09 11:07

Teifion


People also ask

Does update insert in Postgres?

Introduction to the PostgreSQL upsert The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).

What is needed for an insert on conflict update to work?

You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is present, UPDATE privilege on the table is also required. If a column list is specified, you only need INSERT privilege on the listed columns.

What is upsert in Postgres?

The UPSERT statement is a DBMS feature that allows a DML statement's author to either insert a row or if the row already exists, UPDATE that existing row instead. That is why the action is known as UPSERT (simply a mix of Update and Insert).


1 Answers

PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL)

INSERT INTO the_table (id, column_1, column_2)  VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z') ON CONFLICT (id) DO UPDATE    SET column_1 = excluded.column_1,        column_2 = excluded.column_2; 

Searching postgresql's email group archives for "upsert" leads to finding an example of doing what you possibly want to do, in the manual:

Example 38-2. Exceptions with UPDATE/INSERT

This example uses exception handling to perform either UPDATE or INSERT, as appropriate:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);  CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN     LOOP         -- first try to update the key         -- note that "a" must be unique         UPDATE db SET b = data WHERE a = key;         IF found THEN             RETURN;         END IF;         -- not there, so try to insert the key         -- if someone else inserts the same key concurrently,         -- we could get a unique-key failure         BEGIN             INSERT INTO db(a,b) VALUES (key, data);             RETURN;         EXCEPTION WHEN unique_violation THEN             -- do nothing, and loop to try the UPDATE again         END;     END LOOP; END; $$ LANGUAGE plpgsql;  SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis'); 

There's possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the hackers mailing list:

WITH foos AS (SELECT (UNNEST(%foo[])).*) updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id) INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id) WHERE updated.id IS NULL; 

See a_horse_with_no_name's answer for a clearer example.

like image 119
Stephen Denne Avatar answered Oct 05 '22 21:10

Stephen Denne