postgres-syntax-upsert-merge

Quick Reference :

PostgreSQL has two idempotent-write primitives :

1. INSERT ... ON CONFLICT (UPSERT, v9.5+) : single conflict target, single action (DO NOTHING or DO UPDATE). Fast, simple, the default.
2. MERGE (v15+) : multi-action sync against a source relation. Multiple WHEN MATCHED and WHEN NOT MATCHED branches, each with INSERT / UPDATE / DELETE / DO NOTHING. v17 adds RETURNING and WHEN NOT MATCHED BY SOURCE.

Pick UPSERT for "insert this row, but if it conflicts, update it". Pick MERGE for "take this batch and conditionally INSERT / UPDATE / DELETE depending on per-row state".

Two-line minimum-viable UPSERT :

INSERT INTO users (id, email, name) VALUES (1, 'a@b.c', 'Alice')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, name = EXCLUDED.name;

Two-line minimum-viable MERGE (v15+) :