Upsert with INSERT ON CONFLICT

"Insert this row, or update it if it is already there" is a race condition when it is written as two statements. Cartwheel meets that race four times an hour. Each warehouse posts a stock snapshot every fifteen minutes — about 4,000 rows against an inventory table of 12,000 — and the transport retries on timeout, so whole batches replay while a second worker is still loading the first copy.

INSERT … ON CONFLICT does the whole thing as one atomic statement, and the part that decides whether an upsert is correct or merely usually-correct is understanding what it conflicts on. One thing it does not do is settle Cartwheel's other open wound: the box of strawberries sold to two customers. That failure is a decision the application made between two statements — a different problem with a different fix, and Chapter 6 is where it gets one.

Why Check-Then-Insert Races

The pattern the loader grew into is a SELECT to see whether the key exists, then an INSERT or an UPDATE depending on the answer. Under Read Committed each statement takes a fresh snapshot, and neither session can see a row the other has inserted but not yet committed. Both loaders therefore get the same answer, and both act on it.

-- worker A                       -- worker B
SELECT 1 FROM inventory           SELECT 1 FROM inventory
 WHERE product_id = 4471           WHERE product_id = 4471
   AND warehouse_id = 2;             AND warehouse_id = 2;
-- 0 rows                         -- 0 rows

INSERT INTO inventory …           INSERT INTO inventory …
-- commits                        -- blocks on the unique index,
                                  -- then ERROR: duplicate key

The second insert does not fail immediately. It blocks on the unique index until the first transaction ends, and only then discovers whether the outcome is a duplicate-key error or a clean insert. That error aborts the whole transaction, not just the statement, so the application either retries everything or wraps each row in a savepoint and pays for a subtransaction per row.

The Conflict Target

An upsert needs something to arbitrate the conflict, and in Postgres that something is always a unique index. ON CONFLICT (product_id, warehouse_id) infers the arbiter: every unique index on the table that contains exactly those columns, in any order, qualifies. ON CONFLICT ON CONSTRAINT inventory_pkey names one instead of inferring it. Only unique indexes and NOT DEFERRABLE constraints can arbitrate — exclusion constraints cannot, which rules out courier_shifts and its EXCLUDE rule as an upsert target. If the index is partial, its predicate has to be repeated in the conflict target for inference to match it.

The consequence people trip on is that only conflicts on the inferred arbiter are handled. A table with two unique indexes — a primary key and a natural key, say — still raises a duplicate-key error when the incoming row collides on the one that was not named, exactly as a plain insert would, and that is correct: the statement declared which conflict it knew how to resolve, and this was not it. DO NOTHING may omit the target entirely, in which case conflicts with every usable constraint are swallowed; DO UPDATE must always name one, because it has to know which existing row it is updating. One more edge deserves a note in the runbook: while CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is running against a unique index, INSERT … ON CONFLICT on that table can fail with a unique violation it would not otherwise have raised.

DO NOTHING, DO UPDATE, and EXCLUDED

DO NOTHING is the idempotent insert: the row goes in if it is new and is silently skipped if it is not. That is the correct shape for delivery_events, where a replayed message must not become a second row and must not rewrite the first one either. DO UPDATE merges instead, and the incoming row — the one that would have been inserted — is available under the name EXCLUDED, while the row already in the table is reachable through the table's own name or alias. Any per-row BEFORE INSERT trigger has already run by then, so its changes are visible in EXCLUDED.

INSERT INTO inventory (product_id, warehouse_id, on_hand)
VALUES (4471, 2, 18)
    ON CONFLICT (product_id, warehouse_id) DO UPDATE
   SET on_hand = EXCLUDED.on_hand
 WHERE inventory.on_hand IS DISTINCT FROM EXCLUDED.on_hand;

-- arbitrated by a unique index on the natural key, created in the
-- same migration as this statement; id still comes from the sequence
INSERT INTO delivery_events (order_id, event_type, occurred_at, payload)
VALUES (918273645, 'picked_up', '2026-08-13 07:41:02+02',
        '{"courier_id": 88}')
    ON CONFLICT (order_id, event_type, occurred_at) DO NOTHING;

The WHERE on the DO UPDATE is not decoration. An update that sets a column to the value it already holds still writes a new row version and leaves the old one dead, exactly as any other update does. The same clause carries the conditional-merge pattern: when the incoming row has a timestamp, comparing it against the stored one makes a late-arriving replay harmless instead of destructive. Of the 4,000 rows in a warehouse snapshot, roughly 400 have actually moved; without that predicate every load writes 4,000 new row versions into a table with 12,000 live rows, four times an hour, and Chapter 7 arrives to explain why inventory occupies 900 MB.

The Sequence Numbers It Burns

A default or identity value is computed while the row is being prepared, which is before there is any way to know whether it will conflict. So an upsert that mostly conflicts still calls nextval for every attempted row, and nextval is never rolled back — that is deliberate, because a sequence that participated in transactions would serialize every writer behind it. On a bigint sequence that is a curiosity; on the 4-byte orders.id that Chapter 3 spent a whole topic widening, it would not have been. The delivery_events ingest shows it plainly: the day's replays make it attempt noticeably more inserts than the roughly 4 million rows that end up stored, and every skipped attempt still took an id with it, so the sequence runs permanently ahead of the row count.

Row Locks and Deadlocks

For a single row the promise is a strong one: ON CONFLICT DO UPDATE guarantees an atomic insert-or-update outcome, and one of the two happens even under high concurrency, provided nothing else errors. It buys that with row locks. When the update action is taken, every conflicting row is locked — including rows the WHERE clause then declines to update. Two batches that touch overlapping keys in different orders will therefore lock them in different orders, and a deadlock is the arithmetic consequence, reported to the application as a random-looking deadlock detected.

INSERT INTO inventory (product_id, warehouse_id, on_hand)
SELECT DISTINCT ON (product_id, warehouse_id)
       product_id, warehouse_id, on_hand
  FROM jsonb_to_recordset(:batch)
       AS f(product_id bigint, warehouse_id int, on_hand int,
            seen_at timestamptz)
 ORDER BY product_id, warehouse_id, seen_at DESC
    ON CONFLICT (product_id, warehouse_id) DO UPDATE
   SET on_hand = EXCLUDED.on_hand
 WHERE inventory.on_hand IS DISTINCT FROM EXCLUDED.on_hand;

Sorting by the conflict key means every worker takes its row locks in the same order, which is the ordered-locking discipline that removes the cycle — the same rule Chapter 6 states for locks in general. The DISTINCT ON, borrowed a topic early, collapses repeats within one batch and keeps the newest reading of each key. It has to: a statement may not affect the same existing row twice, so a batch carrying the same product and warehouse twice raises a cardinality violation and takes the whole load down with it.

MERGE, and Where It Fits

MERGE arrived in 15 and is the SQL-standard way to express insert, update and delete against a source relation in one statement. It reads better than an upsert for genuine reconciliation, and 17 filled in most of what was missing: a RETURNING clause, the merge_action() function that tells you which branch produced each returned row, and WHEN NOT MATCHED BY SOURCE — the branch for target rows the source never mentioned, which is a Postgres extension to the standard rather than part of it.

MERGE INTO inventory i
USING (SELECT * FROM jsonb_to_recordset(:snapshot)
         AS f(product_id bigint, warehouse_id int, on_hand int)) s
   ON i.product_id = s.product_id AND i.warehouse_id = s.warehouse_id
 WHEN MATCHED AND i.on_hand IS DISTINCT FROM s.on_hand
      THEN UPDATE SET on_hand = s.on_hand
 WHEN NOT MATCHED
      THEN INSERT (product_id, warehouse_id, on_hand)
           VALUES (s.product_id, s.warehouse_id, s.on_hand)
 WHEN NOT MATCHED BY SOURCE AND i.warehouse_id = 2
      THEN DELETE
RETURNING merge_action(), i.product_id, i.on_hand;

That statement makes inventory match the snapshot for warehouse 2 exactly: rows that moved are updated, rows that are new are inserted, and rows the snapshot no longer mentions are deleted. The AND i.warehouse_id = 2 on the delete branch is the difference between reconciling one warehouse and emptying the table for all of them, and it is the clause that gets left out. What MERGE does not carry across is the concurrency guarantee: it matches, then acts, and a row inserted by another session in between produces a plain unique violation. INSERT … ON CONFLICT is the only one of the two that can turn a concurrent insert into an update.

So the division is not about elegance. ON CONFLICT owns the live path where two writers can arrive at the same key at the same instant — the feed, the event ingest, the checkout write. MERGE owns the scheduled reconciliation where you control who is running, and if it must run against live traffic it needs SERIALIZABLE and a retry loop around it. Neither of them touches the strawberries: no single statement can arbitrate a decision the application made between two statements. That one stays open until Chapter 6.