Fixing PostgreSQL UPSERT That Silently Skips Updates on Conflict

You write an INSERT ... ON CONFLICT DO UPDATE, run it against a row that clearly exists, and nothing changes. No error, no warning — the row just sits there with its old values intact. This silent failure is one of the more frustrating PostgreSQL bugs to track down because the query succeeds from the database's point of view.

The problem almost always comes down to one of four things: a missing or mismatched conflict target, using DO NOTHING by accident, referencing columns incorrectly with the excluded pseudo-table, or a WHERE clause that excludes the rows you want to update. This guide walks through each cause with concrete examples and fixes.

What You'll Learn

• How PostgreSQL decides when a conflict has occurred and what it does next
• Why a wrong or missing conflict target causes silent skips
• How to correctly reference incoming values using the excluded pseudo-table
• How a WHERE clause on DO UPDATE can silently suppress all updates
• How to use RETURNING to verify that an update actually fired

Prerequisites

You need a running PostgreSQL instance (version 9.5 or later, which is when ON CONFLICT was introduced). The examples use psql but work identically in any client. A basic understanding of unique constraints and primary keys is assumed.

Understanding How PostgreSQL UPSERT Actually Works

PostgreSQL's UPSERT is not a separate statement type — it is an INSERT with an optional ON CONFLICT clause. When a conflict is detected on the specified constraint or column set, PostgreSQL either ignores the row (DO NOTHING) or updates it (DO UPDATE SET ...). If no conflict is detected, the insert proceeds normally.

The critical detail: PostgreSQL only considers a conflict if the incoming row would violate a specific constraint that you name in the conflict target. If your conflict target does not match the constraint that the row would actually violate, PostgreSQL either raises an error or, worse, lets the original constraint violation propagate — depending on whether another constraint catches it first.

Here is the canonical shape of a working UPSERT:

INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price;

Every part of that statement matters. Change any one piece incorrectly, and you get silence instead of an update.

The Most Common Cause: Missing or Wrong Conflict Target

The conflict target tells PostgreSQL which unique constraint or column list should trigger the ON CONFLICT branch. If you omit it or reference the wrong column, the engine either falls through to DO NOTHING on a different path or raises an ambiguity error depending on your version.

Consider this table:

CREATE TABLE products (
  id     SERIAL PRIMARY KEY,
  sku    TEXT NOT NULL UNIQUE,
  name   TEXT,
  price  NUMERIC
);

If you write:

-- BROKEN: conflict target is the primary key, but duplicates come in on sku
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 9.99)
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

The incoming row does not conflict on id (because id is a serial and is not supplied), so PostgreSQL sees no conflict and attempts a plain insert. That insert then fails on the sku unique constraint — and you get a real error rather than a silent skip. But if the conflict target names a column that exists and is unique but is never actually violated by your data, the DO UPDATE branch is simply never entered.

Fix: Make sure the conflict target names exactly the column or constraint that your incoming data will violate.

-- FIXED: match the constraint your data actually hits
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

You can also reference the constraint by name, which is more robust when columns are involved in composite constraints:

ON CONFLICT ON CONSTRAINT products_sku_key
DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

To find the exact constraint name, query pg_constraint:

SELECT conname, contype, conkey
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

Using DO NOTHING When You Meant DO UPDATE

This sounds obvious but it happens more than you'd expect, especially when copy-pasting from documentation or Stack Overflow snippets. DO NOTHING tells PostgreSQL to silently discard the incoming row on any conflict. The query completes with zero rows affected and zero errors.

-- BROKEN: silently discards the incoming row
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 14.99)
ON CONFLICT (sku) DO NOTHING;

-- Check: the price is still 9.99
SELECT price FROM products WHERE sku = 'ABC-001';

The fix is simply replacing DO NOTHING with a DO UPDATE SET block that lists the columns you want to refresh. Always double-check which variant you have before assuming the logic is correct.

The excluded Table: Getting Column References Right

Inside the DO UPDATE SET block, EXCLUDED is a special pseudo-table that holds the values from the row that was attempted to be inserted. This is how you get the incoming values rather than the existing row's values.

A subtle bug occurs when you reference the target table instead of EXCLUDED:

-- BROKEN: sets name and price to the EXISTING values (no-op)
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 14.99)
ON CONFLICT (sku)
DO UPDATE SET
  name  = products.name,
  price = products.price;

This compiles and runs without error, but the update sets each column to its own current value. Nothing changes. The fix is to use EXCLUDED on the right-hand side:

-- FIXED: use EXCLUDED to reference the incoming values
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 14.99)
ON CONFLICT (sku)
DO UPDATE SET
  name  = EXCLUDED.name,
  price = EXCLUDED.price;

You can mix EXCLUDED and the existing row's values when you want conditional logic. For example, only raise the price, never lower it:

ON CONFLICT (sku)
DO UPDATE SET
  price = GREATEST(products.price, EXCLUDED.price);

Here products.price is the stored value and EXCLUDED.price is the incoming value — a legitimate mixed usage.

Filtering Updates With a WHERE Clause Gone Wrong

The DO UPDATE block accepts an optional WHERE clause. When the condition is false, PostgreSQL skips the update entirely — silently — and the row is left as-is. This is by design, but it becomes a bug when your condition is unintentionally too restrictive.

-- Intended: only update if the incoming price is lower
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 14.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price
WHERE EXCLUDED.price < products.price;

If the existing price is already lower than the incoming value, the WHERE condition is false and the row is not updated. That is the intended behavior. But if you accidentally flip the comparison operator, or compare the wrong columns, the condition is always false and no row is ever updated.

A particularly tricky case: comparing a column that contains NULL. In SQL, any comparison with NULL evaluates to NULL (not true or false), so the WHERE clause silently filters out those rows.

-- BROKEN: if products.price is NULL, this is always NULL (falsy)
WHERE EXCLUDED.price <> products.price

-- FIXED: handle NULL explicitly
WHERE products.price IS DISTINCT FROM EXCLUDED.price

IS DISTINCT FROM treats NULL as a comparable value, so it correctly returns true when one side is NULL and the other is not. This is the safer default for any update condition in an UPSERT.

If you are debugging a similar class of silent-failure issues in SQLite via Python, the article on fixing sqlite3 inserts that never persist to disk covers a related set of gotchas worth knowing.

Partial Indexes as Conflict Targets

PostgreSQL allows unique partial indexes — indexes that only cover rows matching a WHERE predicate. If your uniqueness constraint is backed by a partial index, you must mirror that predicate in your conflict target, otherwise PostgreSQL cannot match the right index and will error or skip.

-- Partial index: unique sku only for active products
CREATE UNIQUE INDEX products_active_sku_idx
  ON products (sku)
  WHERE active = true;

-- BROKEN: conflict target has no WHERE predicate — won't match the partial index
INSERT INTO products (sku, name, price, active)
VALUES ('ABC-001', 'Widget', 9.99, true)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

-- FIXED: mirror the partial index predicate
INSERT INTO products (sku, name, price, active)
VALUES ('ABC-001', 'Widget', 9.99, true)
ON CONFLICT (sku) WHERE active = true
DO UPDATE SET price = EXCLUDED.price;

If the predicate on the partial index does not match the predicate in the ON CONFLICT clause exactly, PostgreSQL raises: there is no unique or exclusion constraint matching the ON CONFLICT specification. That error actually makes this category of bug easier to diagnose than the silent ones.

For deeper context on how PostgreSQL handles result sets in complex queries, see the guide on fixing window functions that return incorrect partition results — the same attention to constraint matching applies there.

Verifying Updates Actually Happened With RETURNING

The single fastest way to confirm that your UPSERT did what you expected is to append a RETURNING clause. It shows you the state of the row after the operation completes, including whether it was inserted or updated.

INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 14.99)
ON CONFLICT (sku)
DO UPDATE SET
  name  = EXCLUDED.name,
  price = EXCLUDED.price
RETURNING id, sku, price, xmax;

The xmax system column is particularly useful here. On a fresh insert, xmax is 0. On an update (even via UPSERT), xmax holds the transaction ID of the update. So:

• xmax = 0 → the row was newly inserted
• xmax > 0 → the row was updated by the conflict handler

This gives you a reliable way to distinguish inserts from updates without adding a separate action column or running a follow-up query.

If you have a batch UPSERT and need to know which rows were updated versus inserted, collect xmax in application code and filter accordingly. It is not a perfect mechanism for all concurrency patterns, but for single-session debugging it is highly reliable.

The guide on fixing DISTINCT ON returning wrong rows covers another area where PostgreSQL's ordering behavior can surprise you — worth reading if you are doing complex reads after your UPSERT.

Common Pitfalls to Watch For

Forgetting to include the conflict column in the INSERT column list

If you do not include the column used as the conflict target in your INSERT column list, PostgreSQL cannot evaluate the conflict. Always include the target column explicitly, even if it has a default that would normally handle it.

Using ON CONFLICT across multiple unique constraints

A single ON CONFLICT clause handles exactly one conflict target. If your table has two unique constraints and both could be violated by the same insert, you cannot handle both in one statement. In that case, restructure your schema or use a CTE to pre-check and route.

Updating the conflict column itself

Trying to update the very column named in the conflict target is allowed but rarely intentional and often breaks idempotency. If you must change the conflict column value, use a separate UPDATE statement instead.

Silent skips in bulk inserts

When you UPSERT thousands of rows at once with DO NOTHING or a restrictive WHERE, a large number of rows can silently not update. Always validate the affected row count against your expected update count, especially in ETL pipelines. The post on fixing Pandas merge creating duplicate rows from non-unique keys addresses a similar silent data problem that can feed into your PostgreSQL pipeline upstream.

Schema changes that invalidate your conflict target

If someone drops a unique constraint and recreates it under a different name, any ON CONFLICT ON CONSTRAINT constraint_name clauses in your code silently break. Prefer column-list conflict targets for application code and rely on constraint names only in migrations where you control the name.

Next Steps

You now have a systematic way to diagnose every silent-skip scenario in PostgreSQL UPSERTs. Here are concrete actions to take:

1. Add RETURNING xmax to any UPSERT you are actively debugging so you can immediately see whether the update branch fired.
2. Query pg_constraint to confirm the exact constraint name and column list before writing a conflict target — do not guess from memory.
3. Replace bare <> comparisons in DO UPDATE WHERE clauses with IS DISTINCT FROM to handle NULL correctly.
4. For bulk UPSERTs in pipelines, compare the row count returned by RETURNING against the input batch size to catch silent skips early.
5. Document your partial indexes by naming them explicitly and keeping the predicate mirrored in any application UPSERT that targets them.