skip to content

Upserts, MERGE & RETURNING

PostgreSQL upserts with INSERT ... ON CONFLICT on an arbiter index, adds MERGE with merge_action(), and lets RETURNING read OLD and NEW since 18. Interviewers probe races and missing rows.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

In PostgreSQL, how does INSERT ... ON CONFLICT DO NOTHING differ from ON CONFLICT DO UPDATE, and what does EXCLUDED refer to?

level: juniorimportance: must knowfreq 80%

answer

  1. skip versus overwrite
  2. the proposed row has a name
  3. one form needs a target
  4. a WHERE on the update

basics

~10 s

ON CONFLICT DO NOTHING skips any proposed row that collides with an arbiter unique index or constraint; DO UPDATE instead updates the existing row, and EXCLUDED names the row that was proposed for insertion.

solid answer

~40 s

Both forms replace a unique-violation error with an alternative action, decided per proposed row against an **arbiter** unique index or constraint. `DO NOTHING` drops the colliding row and may omit the conflict target, in which case every usable unique index and constraint arbitrates. `DO UPDATE` must name a target such as `ON CONFLICT (supplier_id, supplier_sku)`; its `SET` and `WHERE` see the stored row through the table name or alias and the proposed row through the special `excluded` table, so `SET price = EXCLUDED.price` copies the feed's value in. An optional `WHERE` on `DO UPDATE` skips the update for rows that fail it. `RETURNING` reports only rows actually inserted or updated, so a skipped row returns nothing.

code

sql · 12 lines
sql
-- New SKUs only; existing products are left untouched
INSERT INTO catalog_product (supplier_id, supplier_sku, title, price, stock)
VALUES (42, 'ACME-1001', 'Cordless drill', 89.90, 12)
ON CONFLICT DO NOTHING;

-- Insert or refresh price and stock from the feed
INSERT INTO catalog_product AS p (supplier_id, supplier_sku, title, price, stock)
VALUES (42, 'ACME-1001', 'Cordless drill', 84.50, 30)
ON CONFLICT (supplier_id, supplier_sku) DO UPDATE
   SET price = EXCLUDED.price,
       stock = EXCLUDED.stock
RETURNING p.product_id, p.price;

go deeper

for a junior

Recall the two actions, that DO UPDATE needs a target such as (supplier_id, supplier_sku), and that EXCLUDED is the row you tried to insert.

for a middle

Explain what RETURNING shows for skipped and filtered rows, why duplicate keys in one batch raise 21000 under DO UPDATE, and how a WHERE avoids no-op writes.

for a senior

Show you have run upserts at volume: dedupe the input, filter unchanged rows to limit churn, and know that triggers and identity values behave differently on the conflict path.

for a principal

Frame when the sync should overwrite supplier data versus only add new SKUs, and how that choice shows up as DO NOTHING versus a filtered DO UPDATE.

## What ON CONFLICT replaces An hourly job reads a supplier's product feed and writes it into the shop's `catalog_product` table, which has a **unique constraint** on `(supplier_id, supplier_sku)`. A plain `INSERT` of a SKU that already exists fails with SQLSTATE `23505` (`unique_violation`) and rolls back the whole statement. PostgreSQL's `INSERT ... ON CONFLICT` clause names an **alternative action** for each proposed row that would violate an **arbiter** — a unique index, a unique or primary-key constraint, or (for `DO NOTHING` only) an exclusion constraint. Other errors, such as a `CHECK` or foreign-key violation, are not caught: `ON CONFLICT` is about uniqueness and exclusion only. ## DO NOTHING `ON CONFLICT DO NOTHING` simply does not insert the colliding row and carries on with the next one. - The **conflict target is optional**. Without one, conflicts against every usable unique index and constraint are absorbed; with one, only the named arbiter counts and a collision on any other unique index still raises `23505`. - Nothing is written for the skipped row, so `RETURNING` produces **no output row** for it — the classic source of 'missing rows' in get-or-create code. - Duplicate keys inside the same batch are fine: the second copy conflicts with the first and is skipped. ## DO UPDATE and the excluded row `ON CONFLICT (...) DO UPDATE SET ...` turns the collision into an `UPDATE` of the row already stored. 1. A **conflict target is mandatory** — a column list (inference) or `ON CONSTRAINT name`. 2. Inside `SET` and `WHERE`, the stored row is reached through the table name or its alias, and the row that *would have been inserted* through the special table **`excluded`**. `EXCLUDED` values already include the effect of any row-level `BEFORE INSERT` trigger. 3. An optional `WHERE` condition is evaluated last; rows failing it are **locked but not updated** and are left out of `RETURNING`. 4. The manual guarantees an **atomic insert-or-update outcome**: barring an unrelated error, each proposed row ends up either inserted or updated, even under heavy concurrency (read committed). ## Side by side | Aspect | `DO NOTHING` | `DO UPDATE` | |---|---|---| | Conflict target | optional | required | | Effect on the stored row | none | updated via `SET` | | Exclusion constraint as arbiter | allowed | not supported | | `RETURNING` for a conflicting row | nothing | the updated row (unless `WHERE` fails) | | Same key twice in one statement | second copy skipped | error `21000` | | Privileges | `INSERT` | `INSERT` and `UPDATE` | ## Details that catch people out - **Batch duplicates.** If the feed holds one SKU twice, `DO UPDATE` fails with `21000` (`cardinality_violation`): 'ON CONFLICT DO UPDATE command cannot affect row a second time'. Deduplicate the input first. - **Command tag.** `INSERT 0 n` counts rows inserted *or* updated; skipped rows are not counted. - **Identity values are spent.** Defaults, including `nextval` behind an identity column, are computed for the proposed row before the conflict is detected, so conflicting rows leave gaps. - **Triggers.** With `DO UPDATE`, statement-level `UPDATE` triggers fire even if no row took the update path; row-level `BEFORE INSERT` then `BEFORE UPDATE` can both fire for one row. - **Avoiding pointless writes.** `WHERE (p.price, p.stock) IS DISTINCT FROM (EXCLUDED.price, EXCLUDED.stock)` stops unchanged products from getting a new row version every hour. - **Privileges.** `DO UPDATE` needs `UPDATE` privilege on the columns it sets, in addition to `INSERT`, and every form needs `SELECT` on the columns it reads, including the arbiter's key columns and any `EXCLUDED` column it references. - **Other unique keys still bite.** If the catalogue also has a unique `gtin` barcode, a row that collides on `gtin` but not on the declared target raises `23505` as usual; only the arbiter's conflicts are absorbed. ## Putting it together ```sql INSERT INTO catalog_product AS p (supplier_id, supplier_sku, title, price, stock) SELECT supplier_id, supplier_sku, title, price, stock FROM feed_staging ON CONFLICT (supplier_id, supplier_sku) DO UPDATE SET title = EXCLUDED.title, price = EXCLUDED.price, stock = EXCLUDED.stock WHERE (p.title, p.price, p.stock) IS DISTINCT FROM (EXCLUDED.title, EXCLUDED.price, EXCLUDED.stock); ``` This inserts new SKUs, updates changed ones, and leaves identical rows alone. Swap the action for `DO NOTHING` when the job must only add products it has never seen and must never overwrite curated titles or prices.

  • What happens if one feed batch contains the same supplier SKU twice and the statement uses DO UPDATE?
    The statement fails with SQLSTATE `21000`, 'ON CONFLICT DO UPDATE command cannot affect row a second time', and rolls back entirely: one statement may not change the same existing row twice. Deduplicate first, for example `SELECT DISTINCT ON (supplier_id, supplier_sku) ... ORDER BY supplier_id, supplier_sku, feed_ts DESC` over the staging rows. With `DO NOTHING` the second copy is just skipped.
  • How do you stop the hourly sync from rewriting every unchanged product?
    Add a `WHERE` to `DO UPDATE`, such as `WHERE (p.price, p.stock) IS DISTINCT FROM (EXCLUDED.price, EXCLUDED.stock)`. Rows that fail it get no new row version, no WAL for an update and no row-level `UPDATE` trigger, though they are still locked and are omitted from `RETURNING`. `IS DISTINCT FROM` compares NULLs sensibly, which `<>` does not.

  • ON CONFLICT DO UPDATE works without a conflict target, just like DO NOTHING.
  • EXCLUDED holds the row that is already stored in the table.
  • RETURNING after ON CONFLICT DO NOTHING returns the existing row for skipped conflicts.
  • ON CONFLICT also swallows CHECK and foreign-key violations.
  • A row that hits a conflict never consumes an identity value.
open as a page

In PostgreSQL's INSERT ... ON CONFLICT, how is the arbiter index chosen, and when would you use ON CONSTRAINT instead of a column list?

level: middleimportance: must knowfreq 58%

basics

~20 s

PostgreSQL infers arbiters: every unique index whose key is exactly the listed columns or expressions, with a matching WHERE for partial ones. ON CONSTRAINT names a primary-key, unique or exclusion constraint directly and cannot name a bare unique index.

open as a page

In PostgreSQL 18, how can a single INSERT ... ON CONFLICT DO UPDATE report which rows were inserted versus updated, and each row's previous price?

level: middleimportance: should knowfreq 38%

basics

~20 s

PostgreSQL 18 lets RETURNING read old and new: old values are NULL for inserted rows and hold the prior values for conflicting rows that were updated, so old.product_id IS NULL flags inserts and old.price gives the previous price.

open as a page

A PostgreSQL get-or-create query puts INSERT ... ON CONFLICT DO NOTHING RETURNING brand_id in a WITH clause, UNION ALL a SELECT of the existing row, yet sometimes returns nothing; why?

level: seniorimportance: should knowfreq 30%

basics

~20 s

DO NOTHING returns no row for a conflict, and the fallback SELECT shares the statement's snapshot, so a brand committed by a concurrent transaction after that snapshot is invisible to it; the query returns zero rows although the brand exists.

open as a page

Two overlapping runs of a PostgreSQL product-feed sync both use MERGE ... WHEN NOT MATCHED THEN INSERT, and one fails with SQLSTATE 23505; why, and would INSERT ... ON CONFLICT avoid it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

MERGE picks MATCHED or NOT MATCHED by joining against its snapshot, so a row the other run inserts concurrently is invisible, the INSERT arm fires, and the unique index raises 23505. ON CONFLICT DO UPDATE would have updated that row instead.

open as a page