In PostgreSQL, how does INSERT ... ON CONFLICT DO NOTHING differ from ON CONFLICT DO UPDATE, and what does EXCLUDED refer to?
answer
- skip versus overwrite
- the proposed row has a name
- one form needs a target
- a WHERE on the update
basics
~10 sON 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 sBoth 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-- 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
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.
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.
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.
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.