Upsert & Conflict Handling

What is it?

Sometimes you don't know in advance whether a row already exists — like syncing a record from an external system, or incrementing a page-view counter that may or may not have started yet. Doing a SELECT to check, then an INSERT or UPDATE depending on the result, isn't safe: two requests running at the same time could both see "it doesn't exist yet" and both try to insert, causing a duplicate-key error or one update silently overwriting the other.

An upsert (INSERT ... ON CONFLICT in Postgres, MERGE in standard SQL and SQL Server) does the check-and-write as a single atomic database operation, removing that race condition entirely.

Explain like I'm 10

It's like a hotel front desk that either creates a new reservation or updates the existing one for that guest in one motion — rather than an agent looking the guest up, stepping away, and someone else double-booking the same room in the gap before the agent comes back to act on what they saw.

Examples

Insert, or update the existing row on conflict

INSERT INTO page_views (page_id, views)
VALUES ('home', 1)
ON CONFLICT (page_id)
DO UPDATE SET views = page_views.views + 1;

If no row exists for 'home' yet, it's inserted with 1 view; if one already exists, its views column is incremented instead — atomically, with no gap for a race condition.

Ignore instead of update

INSERT INTO users (email, name)
VALUES ('a@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

If a user with this email already exists, the statement simply does nothing instead of erroring or overwriting the existing row — useful for safe, repeatable deduplication.

How it works

The database attempts the insert as normal. If it would violate a unique constraint or primary key named in ON CONFLICT, instead of raising an error, it runs the specified fallback (DO UPDATE or DO NOTHING) against the conflicting existing row — all as one atomic operation. Because it's atomic, no other transaction can slip a conflicting write in between "checking" and "writing," which is exactly what a separate SELECT-then-INSERT in application code can't guarantee.

Why does it exist?

It exists to remove the race condition inherent in "check, then act" logic written in application code, and to avoid the extra round trip of a separate SELECT before deciding whether to INSERT or UPDATE.

When to use it

Reach for an upsert when syncing external data that may or may not already exist, maintaining counters, building "create or update settings" endpoints, or deduplicating on a unique column like an email address.

When not to use it

When insert and update should trigger genuinely different application behavior — like sending a "welcome" email only on true creation — an upsert makes it harder to tell which branch actually happened unless you inspect what the statement returned; explicit, separate insert and update logic can be clearer there.

Common mistakes

• Naming a column in ON CONFLICT that isn't backed by an actual unique constraint or primary key — Postgres requires a real constraint to detect the conflict against.

• Forgetting that DO UPDATE must reference the table name (like page_views.views) to mean 'the existing row's value,' not the newly attempted one.

• Continuing to use separate SELECT-then-INSERT/UPDATE application logic in a case with real concurrency, and hitting rare but genuine race conditions an upsert would have avoided.

Practice exercises

1. Easy:

   Write an upsert that inserts a product by sku, or increments its stock count if that sku already exists.

2. Medium:

   Write an upsert using ON CONFLICT DO NOTHING to safely deduplicate email signups.

3. Hard:

   Walk through, step by step, how two simultaneous requests using separate SELECT-then-INSERT logic (without an upsert) could both succeed and create two rows despite an intended uniqueness rule.

Interview questions

What problem does an upsert solve that a SELECT followed by INSERT or UPDATE doesn't?

It removes the race condition between checking whether a row exists and writing to it, by making the whole check-and-write a single atomic database operation.

What must the column(s) named in ON CONFLICT correspond to?

An actual unique constraint or unique index on the target table — not just any column, or any combination that merely happens to be unique in practice.

What's the difference between ON CONFLICT DO NOTHING and DO UPDATE?

DO NOTHING silently skips the write when a conflict occurs; DO UPDATE instead overwrites fields on the existing conflicting row.

Walk through how two concurrent SELECT-then-INSERT sequences can both 'see' no existing row and both attempt to insert.

Both transactions run their SELECT at nearly the same moment, before either has inserted anything, so both correctly see zero matching rows and both proceed to INSERT — the check was accurate at the instant it ran, but stale by the time the write actually happens.

Why doesn't wrapping the SELECT-then-INSERT in a transaction at the default READ COMMITTED isolation level fix that race on its own?

READ COMMITTED only guarantees each individual statement sees committed data as of when it runs — it doesn't stop a second transaction's SELECT from also running (and also seeing nothing) before the first transaction's INSERT has committed, so both can still proceed to insert.

Why does making the check-and-write a single atomic upsert statement close the race regardless of isolation level?

There's no longer a gap in time between 'check' and 'write' for another transaction to slip into — the database evaluates the conflict and applies the fallback as one indivisible operation, so a second concurrent attempt simply finds the row already there and takes the DO UPDATE/DO NOTHING path instead of also inserting.

What is the `EXCLUDED` pseudo-table in Postgres's INSERT ... ON CONFLICT DO UPDATE, and what does it refer to?

It represents the row that was proposed for insertion — the new values from the VALUES clause that hit the conflict — letting the DO UPDATE clause reference the attempted new data separately from the existing row already in the table.

In `DO UPDATE SET views = page_views.views + 1`, why reference the table name (`page_views.views`) rather than `EXCLUDED.views`?

page_views.views means the value already stored in the existing row; EXCLUDED.views would instead mean the value from the attempted new insert (here, the literal 1) — using EXCLUDED.views + 1 would ignore the row's actual current count entirely.

What does the standard SQL / SQL Server MERGE statement do, and how does it compare to Postgres's INSERT ... ON CONFLICT?

MERGE matches a source set of rows against a target table and lets you specify different actions for matched versus unmatched rows in one statement (typically update-if-matched, insert-if-not); ON CONFLICT is a narrower, insert-first version of the same idea, specifically for a single row hitting a specific unique constraint.

What's one thing MERGE can express that a single ON CONFLICT clause typically can't?

MERGE can also act on rows present in the target but missing from the source (e.g. deleting them), and can process many source rows against the target in one statement — ON CONFLICT only ever reacts to a conflict from the row(s) you're actively inserting.

How would you make an ON CONFLICT DO UPDATE conditional, and how does that differ from DO NOTHING?

Add a WHERE clause after the SET list — DO UPDATE SET ... WHERE <condition> — so the update only applies when the condition holds; unlike DO NOTHING, which never touches the row at all, a false WHERE here still recognizes the conflict, it just declines to change anything for it.

Why can ON CONFLICT target a specific named constraint instead of listing columns directly, and when would you need that?

ON CONFLICT ON CONSTRAINT constraint_name is needed when the uniqueness you care about isn't a simple column list — e.g. a uniqueness rule defined with an expression, or when you want to be explicit about exactly which of several unique constraints you mean.

Can ON CONFLICT target a partial unique index (one defined with its own WHERE clause)? What has to match?

Yes, but the conflict target has to match both the indexed columns/expression and that index's own WHERE condition exactly — a row that wouldn't be covered by the partial index's condition can't trigger a conflict against it.

How would you find out, after running an upsert, whether it actually inserted a new row or updated an existing one?

Add a RETURNING clause with a column whose value differs between the two paths (like xmax = 0 in Postgres, which is true only for a freshly inserted row), or track it via the value change itself if that's distinguishable.

Why is an upsert alone not enough when insert and update are supposed to trigger genuinely different application behavior, like sending a welcome email only on true creation?

The single statement doesn't inherently tell the calling code which path it took — you'd need to inspect what it returned (e.g. via RETURNING) to branch application logic correctly, rather than assuming the upsert always means 'created'.

What's a gotcha with auto-incrementing primary keys and an upsert that hits a conflict and ends up doing nothing?

The sequence value generated for the attempted insert's id is still consumed even though that row was never actually created, leaving a permanent gap in the sequence — harmless, but often surprising the first time it's noticed.

Why does that sequence-gap behavior happen — why doesn't the database 'give the number back' when the conflicting insert doesn't go through?

Sequences are designed to hand out values without ever blocking or coordinating with other concurrent transactions, so taking a value back based on whether a later step succeeded would require exactly the kind of locking sequences are built to avoid — gaps are an accepted tradeoff for that speed.

What does 'idempotent write' mean, and how does DO NOTHING support writing idempotent sync code?

An idempotent write can be safely applied more than once without changing the outcome beyond the first time; DO NOTHING makes re-inserting the same row (say, retrying a sync job after a partial failure) a no-op instead of an error, so the same operation can be safely repeated.

How would you write a single statement that upserts many rows at once from a bulk import, rather than one row at a time?

Use a multi-row VALUES list (or INSERT ... SELECT ... from a staging table) with a single ON CONFLICT clause — the conflict handling applies per row, but it's all one statement instead of one round trip per row.

Why is INSERT ... ON CONFLICT generally faster than the equivalent SELECT-then-INSERT/UPDATE application logic, even ignoring the race condition it fixes?

It's a single round trip to the database instead of two or more — a separate SELECT, and then a conditional INSERT or UPDATE, each pay their own network and query-planning overhead that one statement avoids.

Scenario: importing 10,000 rows with ON CONFLICT (email) DO NOTHING, the table's row count only grows by 9,000. What happened to the other 1,000, and is it a bug?

Not a bug — 1,000 of those emails already existed in the table, and DO NOTHING silently skipped writing them, exactly as designed for safe, repeatable deduplication.

Debugging: an upsert meant to catch duplicate emails inserted a new row for what looks like the same email, just different casing, instead of updating the existing one. What's the likely explanation?

The unique constraint is on the raw email column, and the two values differ in case, so they're not equal as far as that constraint is concerned; catching that would need a case-insensitive unique index (e.g. UNIQUE (LOWER(email))) with a matching ON CONFLICT target.

Why must the column(s)/expression named in ON CONFLICT match an existing unique constraint or index precisely?

Postgres needs to know exactly which constraint's violation to intercept and redirect into the fallback clause — it can't guess from 'any columns that happen to be unique together' without an actual declared constraint or index backing that combination.

What error does Postgres raise if you write ON CONFLICT (col) but no unique constraint or index exists on that exact column?

It raises an error at query time (something like 'there is no unique or exclusion constraint matching the ON CONFLICT specification') rather than silently falling back to a plain insert.

How does MySQL's INSERT IGNORE differ in behavior from Postgres's ON CONFLICT DO NOTHING?

INSERT IGNORE suppresses a broader class of errors — not just duplicate-key conflicts, but also things like certain data-truncation or not-null violations, converting them to warnings — while ON CONFLICT DO NOTHING specifically targets a conflict against the declared or inferred constraint and nothing else.

Can ON CONFLICT DO NOTHING be written without naming a specific column or constraint at all — what does it apply to then?

Yes — without an explicit target, it applies to any constraint violation that would otherwise have made the insert fail; DO UPDATE, by contrast, requires an explicit target because it needs to know exactly which existing row's columns to update.

What isolation-related guarantee is a SELECT ... FOR UPDATE followed by INSERT/UPDATE inside a transaction also trying to achieve, and why is an upsert usually simpler?

It's trying to lock the potentially-conflicting row so no other transaction can act on it in between the check and the write — the same race-free guarantee an upsert gives for free, without needing to reason about explicit locking, lock ordering, or holding a transaction open across the whole check-and-write sequence.

Why would separate, explicit INSERT and UPDATE logic still be preferable over an upsert in some cases, despite the upsert being safer against races?

When insert and update genuinely need to trigger different application behavior — different validation, different side effects like a welcome email — writing them as two explicit paths keeps that branching obvious in the code, instead of burying it inside a single statement whose outcome has to be inferred from what it returned.