Upsert with ON CONFLICT: "insert if missing, update if present" without error 23505 Intermediate

Problem

The product page records “user 7 viewed product 42” in a product_views table: one row per (user_id, product_id) pair, with a view_count. The original code: SELECT; if missing, INSERT; if present, UPDATE with the value read + 1.

The user opens two tabs: both requests see “missing”, and the second gets 23505 unique_violation. When the row already exists, both read view_count = 1 and both write 2: one view is lost.

The question: how can a single statement choose INSERT or UPDATE by itself, without errors and without lost updates under concurrency?

What to choose (summary first)

Situation	Choose	Condition
Insert if missing, skip if present	ON CONFLICT (cols) DO NOTHING	A unique index exists on cols
Insert or accumulate / overwrite	ON CONFLICT (cols) DO UPDATE SET ...	No duplicate keys within the batch
Write only when data changed	DO UPDATE ... WHERE t.col IS DISTINCT FROM EXCLUDED.col	The conflicting row is still locked
Need the id whether the row is new or old	CTE + SELECT with retry, or a “no-op” DO UPDATE	See the RETURNING section
Sync a whole table from a source, including deletes	MERGE	No concurrent writes to the same keys, or retry 23505

Core concepts

Upsert (“UPDATE or INSERT”) in PostgreSQL is INSERT ... ON CONFLICT.

Part	Meaning
Arbiter index	A unique index or NOT DEFERRABLE constraint used to detect conflicts
ON CONFLICT (user_id, product_id)	Infers the arbiter: every unique index on exactly these columns
ON CONFLICT ON CONSTRAINT name	Names it explicitly; the docs prefer inference
ON CONFLICT (cols) WHERE pred	Infers a partial unique index; pred must satisfy the index predicate
EXCLUDED	The row proposed for insertion, after BEFORE INSERT triggers
DO NOTHING / DO UPDATE	Skip the conflicting row / update the existing row; DO UPDATE requires a conflict target

How it works

Why do SELECT then INSERT race, even inside a Read Committed transaction? SELECT does not lock the “empty slot” where the row will appear; another transaction can still insert that key.

SELECT then INSERT: both see 0 rows, B waits for A then gets 23505

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

Request A looks for the row (user 7, product 42): it does not exist yet. SELECT holds no lock.

1. A and B both read 0 rows; nobody holds a lock.
2. A runs INSERT: the unique index entry belongs to an uncommitted transaction.
3. B runs INSERT with the same key: it waits for A (the demo measured wait_event = transactionid).
4. A commits, B gets 23505, and B’s transaction is aborted.

Higher levels do not fix it: in testing, Repeatable Read still gets 23505; Serializable turns it into 40001 (B read the key before inserting), which is retryable but still a failure. With ON CONFLICT DO UPDATE:

ON CONFLICT DO UPDATE: B waits for A to commit, then updates A's row, no error

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

A runs the upsert: no row exists yet, so it INSERTs with view_count = 1, not committed.

The docs guarantee: under Read Committed, each proposed row is either INSERTed or UPDATEd, barring unrelated errors, even when the conflicting row is not yet visible to the snapshot.

SET view_count = pv.view_count + 1 reads the current value of the row after locking it, so no view is lost.

Operational details

Loading diagram…

• Only Read Committed has the guarantee above. Under Repeatable Read/Serializable, if the conflicting row belongs to a transaction outside the snapshot, both DO UPDATE and DO NOTHING throw 40001 (demo step 10). Retry the whole transaction.
• Sequence gaps: nextval (identity included) runs before the conflict is detected and is not given back. Demo: three conflicts, and the next row gets id = 5.
• DO UPDATE ... WHERE: the condition is evaluated last; a row that fails it is still locked until the end of the transaction.
• Duplicate keys in a batch: VALUES (7, 50), (7, 50) with DO UPDATE throws 21000 “cannot affect row a second time”; DO NOTHING inserts only 1 row. Aggregate with GROUP BY first.
• Partial index: without a WHERE matching the predicate you get 42P10 “there is no unique or exclusion constraint matching the ON CONFLICT specification”.
• While CREATE INDEX CONCURRENTLY runs on a unique index, ON CONFLICT may throw a unique violation.

RETURNING on conflict

With DO NOTHING, a skipped row is not in RETURNING (demo: rowCount=0). A way to get the id without extra writes:

WITH ins AS (
  INSERT INTO product_views (user_id, product_id) VALUES (7, 42)
  ON CONFLICT (user_id, product_id) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM product_views
WHERE user_id = 7 AND product_id = 42 AND NOT EXISTS (SELECT 1 FROM ins);

The gap: if the conflicting row comes from another transaction that commits while the statement is running, the SELECT branch uses the same snapshot and does not see it. The demo measured 0 rows; you must retry.

The other way is DO UPDATE SET user_id = EXCLUDED.user_id RETURNING id: it always yields an id, but every call is a real UPDATE. The demo measured ctid (0,2) → (0,4), a changed xmin, and the AFTER UPDATE trigger firing once.

Real-world example

Cart sync: one active cart per user

The carts table has a partial unique index (user_id) WHERE status = 'active'. The mobile app sends the whole cart on every edit, sometimes as two concurrent requests. The upsert states the exact predicate and writes only when the content changes:

INSERT INTO carts (user_id, items) VALUES (7, '[{"sku":"A","qty":2}]')
ON CONFLICT (user_id) WHERE status = 'active'
DO UPDATE SET items = EXCLUDED.items
WHERE carts.items IS DISTINCT FROM EXCLUDED.items
RETURNING id;

Resending the same content gives an empty RETURNING (demo: rowCount=0): “unchanged”, not “no cart”. Preventing an old version that arrives late from overwriting a newer one needs an additional version column.

The demo uses two pg connections:

npx --prefix examples ts-node examples/src/upsert-on-conflict/demo.ts

Output on PostgreSQL 17 (trimmed; ctid, xmin, id change every run)

--- 1. SELECT then INSERT (Read Committed)
B is waiting: { wait_event_type: 'Lock', wait_event: 'transactionid' }
B INSERT failed: 23505 duplicate key value violates unique constraint "product_views_user_id_product_id_key"
--- 2. SELECT view_count then UPDATE = value read + 1
A read 1, B read 1; after two views view_count = 2
--- 4. DO NOTHING + RETURNING
CTE + UNION ALL when A commits midway: 0 rows
--- 6. Identity consumed on conflict
id=1 product=42, id=5 product=43
--- 9. MERGE with a concurrent INSERT
B MERGE failed: 23505 duplicate key value ...
--- 10. ON CONFLICT under Repeatable Read
B upsert failed: 40001 could not serialize access due to concurrent update

MERGE cannot replace ON CONFLICT for concurrent writes

MERGE has existed since PostgreSQL 15. Version 17 adds RETURNING with merge_action() and WHEN NOT MATCHED BY SOURCE (handling rows present in the target but not in the source).

The Read Committed docs state clearly: a MERGE with both INSERT and UPDATE does not guarantee that one of them happens. If the INSERT branch hits a row inserted concurrently by another transaction, MERGE throws a unique violation instead of going back to WHEN MATCHED.

Demo step 9: B gets 23505; rerunning gives merge_action = UPDATE. Use MERGE for bulk sync from a staging table; use ON CONFLICT for writes from parallel requests.

Trade-offs

Pros

• Guarantees INSERT or UPDATE under Read Committed, with no 23505 on the arbiter.

• Accumulates on the current row, avoiding lost updates; one round-trip.

< slot="cons">

• Needs a unique index on the right columns and predicate.

• Sequences are consumed on every conflict; hot keys produce many row versions and lock contention.

• RETURNING does not return skipped rows; it does not handle deletes like MERGE.

Cons

Production checklist

1. Every upsert has a matching unique index; write the conflict target by column names, with WHERE for a partial index.
2. Accumulate with SET col = t.col + EXCLUDED.col; do not read in the app and write back.
3. Add WHERE t.col IS DISTINCT FROM EXCLUDED.col for repeatedly synced data.
4. Batches: merge duplicate keys first, and sort by key to reduce deadlocks between overlapping batches.
5. The CTE + UNION ALL pattern must retry when it returns 0 rows.
6. Run upserts under Read Committed; at higher levels, retry 40001.
7. Do not rely on contiguous sequences; UPDATE triggers must tolerate firing when values do not change.

Common mistakes

The most common mistake is believing a transaction prevents the race.

Check first, then insert inside a transaction

✕ Common

BEGIN;
SELECT id FROM product_views WHERE user_id = 7 AND product_id = 42;  -- 0 rows
INSERT INTO product_views (user_id, product_id) VALUES (7, 42);       -- 23505 if inserted first by someone else
COMMIT;

✓ Prefer

INSERT INTO product_views AS pv (user_id, product_id) VALUES (7, 42)
ON CONFLICT (user_id, product_id)
DO UPDATE SET view_count = pv.view_count + 1, last_viewed_at = EXCLUDED.last_viewed_at;

Why

A transaction does not lock a key that does not exist yet; only the unique index serializes inserts of the same key. After 23505, the transaction is broken until ROLLBACK (see savepoints in Transactions in code).

The second mistake is treating the “no-op” version as free:

A no-op DO UPDATE just to get the id

SET col = EXCLUDED.col is still a full UPDATE: it locks the row, creates a new row version, writes WAL, and fires triggers. On a key called continuously, the cost accumulates into bloat and lock contention. Use WHERE ... IS DISTINCT FROM or the CTE pattern with retry.

Misconception	Consequence	What to do
MERGE is a concurrency-safe upsert	23505 when two requests insert together	Use ON CONFLICT or retry
ids are contiguous because conflicts insert nothing	Gaps in numbering	Use another mechanism for business numbering
A batch takes the raw array from the client	21000 when keys repeat	Merge before upserting

Self-check

1. Why does B wait for A before getting 23505, instead of failing immediately on INSERT?
2. Why does SET view_count = pv.view_count + 1 not lose any views?
3. When does the CTE + UNION ALL pattern return 0 rows, and why does the SELECT in the same statement not see that row?

Summary

Key takeaways

• SELECT then INSERT races at every isolation level (23505, or 40001 under Serializable); only a unique index blocks duplicates.
• ON CONFLICT DO UPDATE guarantees INSERT or UPDATE under Read Committed (higher levels may throw 40001).
• DO NOTHING does not return conflicting rows in RETURNING; a no-op DO UPDATE returns the id but creates a row version and fires triggers.
• A batch must not contain duplicate keys; sequences are consumed even on conflict.
• MERGE can throw 23505 on concurrent inserts; use it for bulk sync.

References

• PostgreSQL 17 — INSERT, ON CONFLICT: conflict target, EXCLUDED, WHERE, cardinality violation, RETURNING.
• PostgreSQL 17 — Read Committed: the ON CONFLICT guarantee and why MERGE hits unique violations.
• PostgreSQL 17 — MERGE: NOT MATCHED BY SOURCE, RETURNING, merge_action().
• PostgreSQL 17 — Sequence Functions: why ON CONFLICT creates gaps.
• PostgreSQL 17 — Release Notes: MERGE in version 17.