Upserts with ON CONFLICT

"Insert it, or update it if it is already there." Everybody needs this, the obvious implementation has a race condition, and SQL has one statement that does it correctly.

The problem with the obvious version

row = db.query("SELECT * FROM tags WHERE name = 'poetry'")
if row:
    db.execute("UPDATE tags SET uses = uses + 1 WHERE name = 'poetry'")
else:
    db.execute("INSERT INTO tags (name) VALUES ('poetry')")

Two requests arrive together. Both SELECT and find nothing. Both INSERT.

ERROR:  duplicate key value violates unique constraint "tags_pkey"
DETAIL:  Key (name)=(poetry) already exists.

That is the read-decide-write shape from the concurrency lesson, and the error is the good outcome — the UNIQUE constraint caught it. Without the constraint you would have two rows and no error, which is worse.

`ON CONFLICT`

INSERT INTO tags (name) VALUES ('poetry')
ON CONFLICT (name) DO NOTHING;

INSERT 0 0

Zero rows inserted, no error. The row was already there and you did not care.

INSERT INTO tags (name) VALUES ('poetry')
ON CONFLICT (name) DO UPDATE SET uses = tags.uses + 1
RETURNING name, uses;

  name  | uses
--------+------
 poetry |    3
(1 row)

One statement, atomic, no race. The conflict is detected and resolved inside the statement, so there is no window for anything to happen in between.

The conflict target

ON CONFLICT (name)                                -- a unique column
ON CONFLICT (book_id, category_id)                -- a composite key
ON CONFLICT ON CONSTRAINT tags_pkey               -- by constraint name
ON CONFLICT DO NOTHING                            -- any conflict at all

There must be a unique constraint or unique index on the target. ON CONFLICT works by detecting a uniqueness violation, so with nothing to violate it does nothing useful:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Naming the target is better than the bare DO NOTHING. The bare form swallows every conflict, including one on a constraint you did not have in mind, and hides a genuine bug.

`EXCLUDED`

In the DO UPDATE, EXCLUDED is the row that would have been inserted:

INSERT INTO books (isbn, title, copies, price_paise)
VALUES ('978-81-1234-567-8', 'Malgudi Days', 3, 29500)
ON CONFLICT (isbn) DO UPDATE
SET title       = EXCLUDED.title,
    copies      = books.copies + EXCLUDED.copies,
    price_paise = EXCLUDED.price_paise,
    updated_at  = now();

Two names, and the distinction is the whole feature:

EXCLUDED.copies — the value you were trying to insert.
books.copies — the value already in the table.

So books.copies + EXCLUDED.copies adds the delivery to the existing stock, while title = EXCLUDED.title simply overwrites. You choose per column, which is what makes this genuinely useful rather than a blunt overwrite.

A conditional upsert

INSERT INTO books (isbn, title, price_paise) VALUES (...)
ON CONFLICT (isbn) DO UPDATE
SET price_paise = EXCLUDED.price_paise
WHERE books.price_paise IS DISTINCT FROM EXCLUDED.price_paise;

The WHERE on the DO UPDATE means only actually update when something changed.

That matters more than it looks: an UPDATE that changes nothing still writes a new row version, still updates every index, and still creates bloat. On a nightly sync of a million rows where twelve have changed, this turns a million writes into twelve.

IS DISTINCT FROM rather than <> because it handles NULL correctly — NULL <> NULL is UNKNOWN, so <> would skip rows where the value went from NULL to a value.

Bulk upsert

INSERT INTO tags (name, uses) VALUES ('poetry', 1), ('fiction', 1), ('essays', 1)
ON CONFLICT (name) DO UPDATE SET uses = tags.uses + EXCLUDED.uses;

Works per row: each conflicting row is updated, each new row inserted, all in one statement and one transaction.

One caveat: a single statement cannot affect the same row twice. If your VALUES list contains 'poetry' twice you get:

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.

Deduplicate your input first. This bites on bulk imports from a CSV that turns out to have duplicates.

`MERGE`

Standard SQL, and in PostgreSQL since version 15:

MERGE INTO tags t
USING (VALUES ('poetry')) AS s(name)
ON t.name = s.name
WHEN MATCHED THEN UPDATE SET uses = t.uses + 1
WHEN NOT MATCHED THEN INSERT (name) VALUES (s.name);

More flexible — it can DELETE as well, and match on any condition rather than requiring a unique constraint.

For the ordinary upsert, use ON CONFLICT. It is shorter, it is what PostgreSQL code looks like, and it has stronger concurrency guarantees: MERGE can still raise a unique violation under concurrency, where ON CONFLICT cannot. Reach for MERGE when you need its extra branches.

Where upserts belong

Four situations, all of them common:

Idempotent writes. A webhook delivered twice, a retried request. ON CONFLICT (event_id) DO NOTHING makes the second delivery a no-op — the Full-Stack course's idempotency lesson, in one clause.

Counters and tallies. "Increment, creating the row if needed."

Syncing from another system. Insert what is new, update what changed, in one statement.

Settings and preferences. "Set this value" without caring whether a row exists.

What it does not solve

Being clear about the boundary, because ON CONFLICT is sometimes reached for as a general concurrency fix.

It resolves uniqueness conflicts on insert. It does not help with the lost update from two lessons ago — UPDATE seats SET free = ... has no conflict to detect, because the row exists and you are simply overwriting it. That needs a conditional UPDATE or a lock.

ON CONFLICT is for "does this row exist". Compare-and-swap is for "has this value changed". Different problems, different tools.

Check your work

Why the read-then-insert version is wrong. Two requests both find nothing and both insert.

Why the resulting error is the good outcome. The UNIQUE constraint caught it; without it you would have two rows silently.

What ON CONFLICT requires. A unique constraint or index on the target.

Why name the conflict target. The bare form swallows every conflict, including ones you did not mean.

What EXCLUDED is. The row that would have been inserted.

How to add to the existing value. books.copies + EXCLUDED.copies.

What a WHERE on the DO UPDATE saves. Writes, index updates and bloat for rows that did not change.

Why IS DISTINCT FROM rather than <>. It handles NULL correctly.

What breaks a bulk upsert. The same constrained value twice in one statement.

MERGE or ON CONFLICT. ON CONFLICT for ordinary upserts — shorter, and stronger under concurrency.

What ON CONFLICT does not fix. The lost update. That is compare-and-swap.

Practice

Insert the same tag twice and read the duplicate-key error.
Add ON CONFLICT (name) DO NOTHING and confirm INSERT 0 0.
Use DO UPDATE SET uses = tags.uses + 1 and run it three times. Check the count.
Add RETURNING and see the resulting row each time.
Try ON CONFLICT on a column with no unique constraint. Read the error.
Write an upsert that overwrites the title but adds to the copies.
Add a WHERE so the update only happens when the price changed. Run it twice with the same price and check the row count.
Replace IS DISTINCT FROM with <> and find a row where it behaves differently.
Bulk upsert five tags where two already exist.
Put the same name twice in one VALUES list and read the error.
Write the same upsert as MERGE and compare the two.
Implement the read-then-insert version in application code, race it from two processes, and see the failure. Then replace it with ON CONFLICT.
Write the idempotent webhook handler: ON CONFLICT (event_id) DO NOTHING, then deliver the same event twice.

Official documentation

PostgreSQL — INSERT ... ON CONFLICT — The full clause, EXCLUDED, and the note about affecting a row twice.
PostgreSQL — MERGE — Including the explicit warning about concurrency compared with ON CONFLICT.
PostgreSQL — Comparison operators — IS DISTINCT FROM and why it differs from <>.

You can now change data safely: insert efficiently, update and delete without regret, group work into transactions, choose an isolation level knowingly, recognise and fix a lost update, read a deadlock, and upsert without a race.

Next module: making it fast.