easyDSA

UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

Q: UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

`INSERT … ON CONFLICT (unique_col) DO UPDATE SET col = EXCLUDED.col` is an atomic upsert. It requires a `UNIQUE`/`PRIMARY KEY` constraint on the conflict target. `EXCLUDED` refers to the row that *would* have been inserted. UPSERT: INSERT … ON CONFLICT (key) DO UPDATE — atomic, race-free vs SELECT-then-INSERT Race (Bad) SELECT exists? if not → INSERT concurrent txn inserts → duplicate key error! <rect x="290" y="35" width="240" height="110" rx="14" fill

388 views

Understand the problem

Use ON CONFLICT DO UPDATE vs DO NOTHING, the EXCLUDED pseudo-table, and required unique constraints.

upserton-conflictpostgres

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

INSERT … ON CONFLICT (unique_col) DO UPDATE SET col = EXCLUDED.col is an atomic upsert. It requires a UNIQUE/PRIMARY KEY constraint on the conflict target. EXCLUDED refers to the row that would have been inserted.

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 11 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

What do COALESCE and NULLIF do?

Continue