Upserts with ON CONFLICT

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Predict the output

Two upserts for the same SKU. What does the SELECT print?

SQL · PostgreSQL 17

CREATE TABLE stock (sku text PRIMARY KEY, qty int NOT NULL);
INSERT INTO stock VALUES ('A1', 5)
  ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;
INSERT INTO stock VALUES ('A1', 3)
  ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;
SELECT sku, qty FROM stock;

OptionsA1|8A1|3A1|5A unique violation

Hint 1 of 1

What does EXCLUDED hold?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A A1|8

Correct. The first inserts 5; the second conflicts and adds EXCLUDED.qty (3).

ON CONFLICT turns a duplicate-key insert into an update of the existing row. stock.qty is the stored value and EXCLUDED.qty is the value you tried to insert, so 5 + 3 = 8.

Why not the others?

B A1|3. That's SET qty = EXCLUDED.qty, which overwrites instead of adding.
C A1|5. That's DO NOTHING behaviour.
D A unique violation. ON CONFLICT turns the duplicate into an update.

Result on PostgreSQL 17 (psql)

sku	qty
A1	8

(1 row)

30-second answer

Use INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col. The database checks the unique index and either inserts or updates atomically, so two concurrent requests can't both insert. Checking with a SELECT first and then inserting has a race: both requests see no row and one fails with a unique violation. DO NOTHING skips duplicates instead.

Practise saying it · 30 s. No recording, just a timer

Explain upserts.

Say your answer out loud, then check which of these points you covered:

INSERT ... ON CONFLICT
Atomic under concurrency
EXCLUDED is the proposed row
Select-then-insert races

Deep dive

EXCLUDED refers to the row you tried to insert.

ON CONFLICT needs a unique index or constraint that matches the conflict target.

A WHERE on DO UPDATE can skip stale writes: WHERE t.updated_at < EXCLUDED.updated_at.

PostgreSQL 15+ also has MERGE, but ON CONFLICT is the concurrency-safe upsert.

Common mistakes

SELECT then INSERT in application code.
Using DO NOTHING and assuming RETURNING gives back the existing row (it returns nothing for skipped rows).
Upserting into a table without the unique index the conflict target names.

Senior take & follow-ups

Show the atomic statement and contrast it with the racy select-then-insert.

Follow-up questions

Think of your answer first, then open the hint.

How do you get the id whether inserted or existing?
Show hint
DO UPDATE SET a no-op column with RETURNING, or a follow-up SELECT.
Is MERGE safe for concurrent upserts?
Show hint
Not in the same way; concurrent MERGEs can still raise unique violations.

Similar on Android: Cache freshness and stale data On Android, Room's @Upsert writes to the local SQLite cache; there's no concurrent writer from other servers.

Suggest a fix if something here is wrong or out of date.