Upsert in PostgreSQL
PostgreSQL supports update
ON CONFLICT clause for
INSERT statement to update existing record if it already exists.
Having a table of people:
To upsert a new record,
ON CONFLICT with
DO UPDATE for
INSERT command is required:
INSERT INTO people(id, first_name, last_name) VALUES (1, 'Alice', 'Turner') ON CONFLICT(id) DO UPDATE SET first_name = 'Alice', last_name = 'Turner';
Comparing to other database engines, it seems a bit primitive - i.e. SQL Server
MERGE command can be used to synchronize data with support for removing orphan records. Full command looks as follows:
MERGE INTO people USING (VALUES (1, 'Alice', 'Turner'), (3, 'Charlie', 'Ross') ) friends(id, first_name, last_name) ON people.id = friends.id WHEN NOT MATCHED THEN INSERT (first_name, last_name) VALUES (friends.first_name, friends.last_name) WHEN MATCHED THEN UPDATE SET people.first_name = friends.first_name, people.last_name = friends.last_name WHEN NOT MATCHED BY SOURCE THEN DELETE;
MERGE was planned to be added to PostgreSQL 11. Unfortunately it was reverted by the author.