Upsert rows in PostgreSQL with INSERT ... ON CONFLICT, and when to use MERGE instead

Documented steps

1. Ensure a unique index or constraint exists on the conflict column(s) — ON CONFLICT arbitrates on unique violations.
2. Insert-or-skip: INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo') ON CONFLICT (did) DO NOTHING;
3. Insert-or-update, referencing the proposed row via the EXCLUDED pseudo-table: INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
4. Conditionally update with a WHERE clause on the existing row: INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> '21201';
5. You can also name the constraint explicitly: ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;
6. Reach for MERGE instead when you need INSERT, UPDATE, and DELETE actions in one statement, conditions across multiple WHEN clauses, or handling of target rows with no source match (WHEN NOT MATCHED BY SOURCE).
7. Official docs: https://www.postgresql.org/docs/current/sql-insert.html and https://www.postgresql.org/docs/current/sql-merge.html