skip to content

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.