PostgreSQL Upsert Done Right: ON CONFLICT Usage and Traps

Infrastructure PostgreSQL Database
4 min read

“Update if present, insert if not” is not something you check with a SELECT and branch on in the application — it’s one statement: INSERT ... ON CONFLICT. The database atomically closes the gap where another transaction slips in between your check and your insert. Upsert is the flagship case of the principle set in basics chapter 6: read-decide-write belongs in one atomic statement.

Why SELECT-then-INSERT breaks #

Anti-pattern: SELECT then INSERT 
-- anti-pattern: there's a gap between the check and the insert
SELECT id FROM users WHERE email = 'kim@example.com';
-- (between the "not found" reply and your INSERT, another request inserts the same email)
INSERT INTO users (email, name) VALUES ('kim@example.com', 'Kim Dev');
-- ERROR: duplicate key value violates unique constraint

It doesn’t blow up under light traffic — then duplicate requests from the same user (double clicks, retries) overlap and it explodes as a unique-constraint violation. When you notice try-catch-and-retry code accumulating around this error, that’s where ON CONFLICT belongs.

DO NOTHING and DO UPDATE #

DO NOTHING and DO UPDATE 
-- silently skip if already present: idempotent insert
INSERT INTO subscriptions (user_id, plan)
VALUES (42, 'basic')
ON CONFLICT (user_id) DO NOTHING;

-- update if already present: the real upsert
INSERT INTO users (email, name, last_seen_at)
VALUES ('kim@example.com', 'Kim Dev', now())
ON CONFLICT (email) DO UPDATE
SET name         = EXCLUDED.name,
    last_seen_at = EXCLUDED.last_seen_at;

The syntax has three parts. The conflict target (email) must be a column with a unique constraint (or unique index) — the premise of upsert is that a unique constraint exists (the constraint design of basics chapter 2 pays its dividend here). EXCLUDED is a virtual table meaning “the new row that tried to insert and collided,” used to update with the new values. And DO UPDATE takes a WHERE for conditional updates.

Conditional update 
-- update only when the new value is fresher (defends against event reordering)
ON CONFLICT (device_id) DO UPDATE
SET reading = EXCLUDED.reading, reported_at = EXCLUDED.reported_at
WHERE EXCLUDED.reported_at > sensor_readings.reported_at;

Three traps worth knowing #

  • Naming the conflict target is the standard. Bare ON CONFLICT DO NOTHING (no target) is legal syntax, but on a table with several unique constraints it erases from the code which conflict you meant to absorb. Name the intended constraint with (column) and violations of the other constraints still error properly instead of hiding bugs.
  • DO UPDATE writes the row even when the values are identical. The conflicting row is locked and a new version is created (per MVCC, dead tuples too). For batch jobs repeatedly upserting mostly-unchanged data, filter out meaningless updates with a WHERE (old values) IS DISTINCT FROM (new values) condition — it lightens the VACUUM burden.
  • Sequences are consumed even on conflict. IDENTITY numbering happens before conflict resolution, so an insert absorbed by DO NOTHING still burns a number. Gappy ids are normal; requirements for gapless sequential numbers (invoices and the like) call for a dedicated numbering design, not a sequence, in the first place.

The relationship with MERGE #

Since PostgreSQL 15 there’s also standard SQL MERGE. The fork is simple: for concurrency-safe upsert, ON CONFLICT (atomic handling grounded in a unique constraint); for batch synchronization that applies inserts, updates, and deletes by condition, MERGE (WHEN MATCHED / NOT MATCHED branches; RETURNING supported from PostgreSQL 17). There’s almost no reason to use MERGE for a web application’s everyday upsert — unlike ON CONFLICT, MERGE can raise unique-violation errors under concurrent insert races, so without retry logic it’s not an upsert substitute.

The application sequence #

  1. Confirm the upsert column has a unique constraint (or unique index). If not, creating one comes first.
  2. Idempotent insertion (ignore duplicates) is DO NOTHING; if updating is needed, DO UPDATE + EXCLUDED. Name the conflict target.
  3. For large repeated batches, filter no-change updates with IS DISTINCT FROM to cut dead tuples.
  4. Consider MERGE only for synchronization work mixing conditional inserts, updates, and deletes.

Summary #

  • Upsert is one INSERT … ON CONFLICT statement, not SELECT-then-branch. The database closes the race atomically.
  • Ignore duplicates with DO NOTHING; update with DO UPDATE + EXCLUDED. DO UPDATE’s WHERE gives conditional updates too.
  • Naming the conflict target is the standard — violations of other unique constraints stay loud instead of being swallowed.
  • DO UPDATE writes a new version even for identical values, and sequences burn numbers on conflicts. Both are normal behavior.
  • Concurrency-safe upsert is ON CONFLICT; condition-branching batch sync is MERGE. MERGE is not the answer for everyday upsert.
X