UPSERT - INSERT ... ON CONFLICT DO UPDATE...

since version 9.5 postgres offers UPSERT functionality with INSERT statement.

Say you have a table called my_table, created in several previous examples. We insert a row, returning PK value of inserted row:

b=# INSERT INTO my_table (name,contact_number) values ('one',333) RETURNING id;
 id
----
  2
(1 row)

INSERT 0 1

Now if we try to insert row with existing unique key it will raise an exception:

b=# INSERT INTO my_table values (2,'one',333);
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (id)=(2) already exists.

Upsert functionality offers ability to insert it anyway, solving the conflict:

b=# INSERT INTO my_table values (2,'one',333) ON CONFLICT (id) DO UPDATE SET name = my_table.name||' changed to: "two" at '||now() returning *;
 id |                       name                             | contact_number
----+-----------------------------------------------------------------------------------------------------------+----------------
  2 | one changed to: "two" at 2016-11-23 08:32:17.105179+00 |            333
(1 row)

INSERT 0 1

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents