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';

Result:

MERGE alternative

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;

Result:

MERGE was planned to be added to PostgreSQL 11. Unfortunately it was reverted by the author.

Full examples can be found on dbfidle: upsert, merge.