postgresql INSERT UPSERT - INSERT ... ON CONFLICT DO UPDATE...
Example
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
