“Insert this row, or update it if it already exists” sounds like one operation. Written as two statements, a SELECT to check and then an INSERT or UPDATE, it’s a race: two requests both see nothing and both insert, and one gets a duplicate key error. PostgreSQL’s INSERT ... ON CONFLICT makes the decision atomically against a unique index. Interviewers ask about it because the syntax is easy and the contract is not: which conflict, which values win, what happens with stale data, and what comes back to the caller.
All of this ran on PostgreSQL 16.15, using an inventory table that’s kept in sync from a warehouse system sending versioned updates.
The contract starts with a unique index
CREATE TABLE inventory (
id bigserial PRIMARY KEY,
sku text NOT NULL UNIQUE,
qty int NOT NULL,
source_version int NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);ON CONFLICT (sku) means: if inserting this row would violate the unique index on sku, take the conflict action instead. The target must match a unique index or constraint exactly (columns, expressions, and the predicate for a partial index). That’s why the target is part of the contract: it names which business key defines “the same row”.
Conflicts on any other unique index aren’t handled. With ON CONFLICT (sku) DO NOTHING, inserting a row whose id already exists still fails:
ERROR: duplicate key value violates unique constraint "inventory_pkey"
DETAIL: Key (id)=(1) already exists.ON CONFLICT DO NOTHING without a target swallows conflicts on every unique index, which is convenient and occasionally hides a bug you’d want to see. DO UPDATE always needs a target.
DO NOTHING and what RETURNING gives back
INSERT INTO inventory (sku, qty, source_version) VALUES ('MUG-01', 10, 3)
RETURNING id, (xmax = 0) AS inserted;
-- id | inserted
-- 1 | t
INSERT INTO inventory (sku, qty, source_version) VALUES ('MUG-01', 12, 4)
ON CONFLICT (sku) DO NOTHING
RETURNING id;
-- (0 rows)On conflict, DO NOTHING returns no row. Code that expects RETURNING id to always give it the id (“get or create”) breaks here. Either follow up with a SELECT id FROM inventory WHERE sku = $1 when nothing came back, or use a DO UPDATE that changes nothing meaningful (SET sku = EXCLUDED.sku) so a row is always returned. The no-op update writes a new row version, so it isn’t free.
The (xmax = 0) AS inserted trick tells you which path was taken: a freshly inserted row has no xmax, while an updated one carries the updating transaction’s id. It’s widely used but relies on an implementation detail, not a documented API, so treat it as a convenience.
DO UPDATE, EXCLUDED and the stale-write problem
EXCLUDED is the row you tried to insert; the table name refers to the existing row:
INSERT INTO inventory (sku, qty, source_version) VALUES ('MUG-01', 12, 4)
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty,
source_version = EXCLUDED.source_version,
updated_at = now()
WHERE inventory.source_version < EXCLUDED.source_version
RETURNING id, qty, source_version, (xmax = 0) AS inserted; id | qty | source_version | inserted
----+-----+----------------+----------
1 | 12 | 4 | fThe WHERE on DO UPDATE is the part most people leave out, and it’s the part that makes a sync idempotent. Messages from queues arrive late, get redelivered and come out of order. Here’s version 2 arriving after version 4:
INSERT INTO inventory (sku, qty, source_version) VALUES ('MUG-01', 9, 2)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty, ...
WHERE inventory.source_version < EXCLUDED.source_version
RETURNING id, qty, source_version;
-- (0 rows)The guard rejected it, the row still says 12 at version 4, and RETURNING produced nothing, which your consumer can log as “stale, skipped”. Without the guard, a redelivered old message would silently roll inventory back. Using updated_at timestamps from different machines as the guard is weaker, because clocks drift. A monotonically increasing version from the source system is better.
Decide column by column what the update should do. SET qty = EXCLUDED.qty is “last write wins”. SET qty = inventory.qty + EXCLUDED.qty accumulates, which is right for counters and wrong for snapshots. Columns you leave out of SET keep their existing values, so created_at isn’t overwritten unless you list it.
Concurrency: what ON CONFLICT guarantees
Two sessions ran the same accumulating upsert for a new SKU at the same moment, each inside a transaction that stayed open for a second:
INSERT INTO inventory (sku, qty, source_version) VALUES ('DESK-8', 3, 1)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
RETURNING qty, (xmax = 0) AS inserted;The first returned qty = 3, inserted = t. The second waited for the first to commit, then took the update path and returned qty = 6, inserted = f. No error, no lost increment. PostgreSQL guarantees that ON CONFLICT DO UPDATE ends in either an insert or an update, even under concurrency, as long as no other error occurs. That’s the guarantee hand-written check-then-insert code can’t give you.
The same shape is the standard counter: INSERT INTO page_views VALUES ('/pricing', 1) ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1 returned 1 then 2. Every upsert on a hot key serialises on that row’s lock, so a single extremely popular key becomes a bottleneck.
Batch upserts and duplicate keys
A batch with the same key twice fails as a whole:
INSERT INTO inventory (sku, qty, source_version)
VALUES ('CAP-03', 1, 1), ('CAP-03', 2, 2)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.PostgreSQL won’t guess which of the two should win. Deduplicate before inserting, keeping the row your rule prefers:
INSERT INTO inventory (sku, qty, source_version)
SELECT DISTINCT ON (sku) sku, qty, v
FROM (VALUES ('CAP-03', 1, 1), ('CAP-03', 2, 2)) AS b(sku, qty, v)
ORDER BY sku, v DESC
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty, source_version = EXCLUDED.source_version
RETURNING sku, qty, source_version;
-- CAP-03 | 2 | 2Large batches also hold row locks on every touched row until commit. Sorting the batch by key keeps lock order consistent between concurrent batches, which avoids deadlocks.
Side effects people don’t expect
Sequence gaps. After three conflicting inserts on MUG-01, inserting a brand-new SKU got id = 5, not 2. Every attempted insert calls nextval() before the conflict is detected, and sequences never roll back. Gaps are normal; never use a sequence where gap-free numbering is a requirement (invoice numbers in some jurisdictions).
Triggers. BEFORE INSERT triggers fire for every attempted row, including ones that end up as updates. Then UPDATE triggers fire on the update path.
Updates you didn’t need. DO UPDATE without a WHERE writes a new row version even if every value is identical, which costs WAL and vacuum work. A guard like WHERE inventory.qty IS DISTINCT FROM EXCLUDED.qty skips no-op writes.
What about MERGE?
PostgreSQL 15 added the SQL-standard MERGE, which handles richer logic (insert, update or delete depending on conditions). It isn’t a drop-in replacement for ON CONFLICT under concurrency. Two sessions running the same MERGE for a new key:
ERROR: duplicate key value violates unique constraint "inventory_sku_key"
DETAIL: Key (sku)=(LAMP-7) already exists.Both sessions evaluated NOT MATCHED against their snapshots and both tried to insert. The PostgreSQL docs point to INSERT ... ON CONFLICT as the alternative when a concurrent insert must turn into an update. Use MERGE for batch reconciliation where you control concurrency (one sync job at a time) or can retry on 23505. If you’re debugging those errors, the duplicate key guide covers the other common causes.
Interview practice: idempotent payment webhooks
A payment provider sends webhooks for each payment: payment_id, status (pending, succeeded, failed, refunded) and a sequence number that increases per payment. Webhooks are retried on timeout and can arrive out of order. Design the table and the write so that processing is idempotent and the stored status never goes backwards.
Answer
CREATE TABLE payments (
payment_id text PRIMARY KEY,
status text NOT NULL,
sequence int NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO payments (payment_id, status, sequence)
VALUES ($1, $2, $3)
ON CONFLICT (payment_id) DO UPDATE
SET status = EXCLUDED.status,
sequence = EXCLUDED.sequence,
updated_at = now()
WHERE payments.sequence < EXCLUDED.sequence
RETURNING status, sequence;The primary key on payment_id is the conflict target, so the provider’s id defines “the same payment”. The WHERE payments.sequence < EXCLUDED.sequence guard makes retries and late deliveries harmless: a duplicate of sequence 5 or an older sequence 3 arriving after 5 updates nothing. An empty RETURNING tells the handler the event was stale or a duplicate, so it can acknowledge the webhook with a 200 without re-running side effects such as sending a receipt email.
Side effects should only run when the upsert actually inserted or advanced the row, ideally by writing an outbox row in the same transaction rather than calling the email service directly. I’d also keep the raw webhook in an append-only payment_events table with UNIQUE (payment_id, sequence) and ON CONFLICT DO NOTHING, which gives an audit trail and a cheap duplicate check. I wouldn’t use MERGE here, since retries of the same webhook can arrive concurrently and ON CONFLICT handles that without unique violations.