Fixing PostgreSQL Upsert That Overwrites Unchanged Rows and Breaks updated_at

You bulk-load records with INSERT ... ON CONFLICT DO UPDATE, check your table afterward, and every single row has a fresh updated_at timestamp — even the ones where nothing actually changed. Your audit trail is now meaningless, and any downstream process that polls for recent changes is going to reprocess the entire dataset.

This is one of the most common PostgreSQL gotchas, and the fix is simpler than most people expect once you understand what the engine is actually doing under the hood.

What you'll learn

• Why ON CONFLICT DO UPDATE fires even when the incoming row is identical to the stored one
• How to add a WHERE clause to the DO UPDATE so only genuinely changed rows are touched
• How to scale that guard to tables with many columns without writing a wall of SQL
• How to back the guard with a trigger so the rule is enforced at the database level
• The subtle pitfalls that can still bite you after you think you've fixed it

Prerequisites

• PostgreSQL 9.5 or later (that's when ON CONFLICT was introduced)
• Basic familiarity with INSERT, UPDATE, and table constraints
• A table that has an updated_at TIMESTAMPTZ column you want to preserve correctly

The Problem in Plain Terms

Let's anchor this with a concrete table. Suppose you have a products table that a nightly ETL job refreshes from an upstream source:

CREATE TABLE products (
  id          BIGINT PRIMARY KEY,
  sku         TEXT NOT NULL,
  price       NUMERIC(10, 2) NOT NULL,
  stock       INT NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Your ETL issues an upsert that looks roughly like this:

INSERT INTO products (id, sku, price, stock, updated_at)
VALUES
  (1, 'WIDGET-A', 9.99, 100, now()),
  (2, 'WIDGET-B', 14.99, 50, now())
ON CONFLICT (id) DO UPDATE
  SET sku        = EXCLUDED.sku,
      price      = EXCLUDED.price,
      stock      = EXCLUDED.stock,
      updated_at = EXCLUDED.updated_at;

Even if WIDGET-A still costs $9.99 and still has 100 units in stock, it gets a new updated_at. PostgreSQL doesn't compare old and new values for you — it just executes the SET clause unconditionally.

How INSERT ... ON CONFLICT DO UPDATE Actually Works

PostgreSQL evaluates the conflict target (here, the id primary key) and, on a collision, switches from an insert to an update. The EXCLUDED pseudo-table holds the row that would have been inserted. PostgreSQL then runs the SET clause as a normal UPDATE against the existing row.

Crucially, PostgreSQL does not check whether the new values differ from the old ones before running that update. It just runs it. This means the row gets a new MVCC version written to disk, any triggers fire, and anything watching updated_at sees the change — even when the payload was identical.

This behavior is by design and is documented. The engine optimizes for predictability over implicit diffing. The responsibility for filtering no-op updates falls on you.

Why Every Row Gets a New updated_at

There are two distinct ways this breaks updated_at:

1. You set it explicitly in the SET clause to now() or EXCLUDED.updated_at, so it always gets the current time regardless of whether any other column changed.
2. You have a trigger that sets updated_at = now() on every UPDATE, and the trigger fires because the upsert runs the update path unconditionally.

Both require a fix, but in different places. We'll cover the explicit SET case first, then revisit the trigger case.

The Fix: A WHERE Clause on the DO UPDATE

PostgreSQL lets you attach a WHERE clause to ON CONFLICT DO UPDATE. If the condition evaluates to false, the update is skipped entirely — the row is left exactly as it is. This is the standard approach for preventing no-op writes.

INSERT INTO products (id, sku, price, stock, updated_at)
VALUES
  (1, 'WIDGET-A', 9.99, 100, now()),
  (2, 'WIDGET-B', 14.99, 50, now())
ON CONFLICT (id) DO UPDATE
  SET sku        = EXCLUDED.sku,
      price      = EXCLUDED.price,
      stock      = EXCLUDED.stock,
      updated_at = now()
WHERE
  products.sku   IS DISTINCT FROM EXCLUDED.sku
  OR products.price IS DISTINCT FROM EXCLUDED.price
  OR products.stock IS DISTINCT FROM EXCLUDED.stock;

Notice two things. First, updated_at is no longer set from EXCLUDED.updated_at — you set it to now() only when the WHERE condition is true, meaning a real change occurred. Second, the comparisons use IS DISTINCT FROM instead of <>. This matters because NULL <> NULL evaluates to NULL (not TRUE), so nullable columns would silently skip the comparison. IS DISTINCT FROM treats NULL as a comparable value.

When WIDGET-A arrives with unchanged data, the WHERE condition is false, the update is skipped, and updated_at stays at whatever it was last set to. Only rows with actual changes get a new timestamp.

Handling Multiple Columns Without Going Insane

The column-by-column comparison gets verbose fast on wide tables. A cleaner approach is to cast both the incoming and stored rows to a composite type and compare them in one shot using ROW():

INSERT INTO products (id, sku, price, stock, updated_at)
VALUES
  (1, 'WIDGET-A', 9.99, 100, now())
ON CONFLICT (id) DO UPDATE
  SET sku        = EXCLUDED.sku,
      price      = EXCLUDED.price,
      stock      = EXCLUDED.stock,
      updated_at = now()
WHERE
  ROW(products.sku, products.price, products.stock)
  IS DISTINCT FROM
  ROW(EXCLUDED.sku, EXCLUDED.price, EXCLUDED.stock);

The ROW() constructor compares element by element and respects NULL semantics correctly. Deliberately exclude updated_at itself from both sides of the comparison — you don't want the timestamp to affect whether the timestamp gets updated, which would be circular.

This approach scales well to 20-column tables without turning into a maintenance nightmare. If you add a new business-data column, add it to both the SET clause and both sides of the ROW() comparison. Keep updated_at, created_at, and any surrogate keys out of that comparison.

If you work with complex queries like this alongside other tricky SQL patterns, the article on fixing PostgreSQL window functions that return the wrong rank on tied partitions covers a similar mindset of understanding what the engine does versus what you intend.

Using a Trigger Instead of an Application-Level Guard

If multiple services write to this table, or if you can't guarantee every upsert query includes the WHERE guard, push the logic into the database with a trigger.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Only update the timestamp if a non-metadata column actually changed
  IF ROW(NEW.sku, NEW.price, NEW.stock)
     IS DISTINCT FROM
     ROW(OLD.sku, OLD.price, OLD.stock)
  THEN
    NEW.updated_at := now();
  ELSE
    NEW.updated_at := OLD.updated_at;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

With this trigger, even a naive upsert that sets updated_at = now() unconditionally will be corrected: the trigger runs before the row is written and resets updated_at to OLD.updated_at if nothing meaningful changed.

The tradeoff is that the update still writes a new row version to disk — it just has the same updated_at. If your primary concern is write amplification and table bloat, the WHERE clause approach on the upsert statement is better because it prevents the write entirely. If your primary concern is timestamp accuracy across many writers, the trigger is more robust.

Combining Both Approaches

You can use both: a WHERE clause on the upsert to skip disk writes for unchanged rows, plus a trigger as a safety net for any application path that bypasses the guard. The trigger only fires when an update actually reaches the row, so there's no double-penalty.

Common Pitfalls

Forgetting IS DISTINCT FROM on Nullable Columns

If any column in your comparison is nullable and you use = or <>, a NULL value will silently fail the comparison. You'll either always update or never update those rows correctly. Always use IS DISTINCT FROM for any column that could be NULL. It costs nothing extra in practice and is always safe to use even on NOT NULL columns.

Including updated_at in the Comparison

If you accidentally include updated_at in the ROW() comparison, the condition will almost always be true (because EXCLUDED.updated_at is now() and products.updated_at is some past time), defeating the entire guard. Keep metadata columns out of the business-data comparison.

ON CONFLICT DO NOTHING Is Not the Answer

A common instinct when you want to avoid touching unchanged rows is to switch to ON CONFLICT DO NOTHING. That skips the update entirely — which means legitimately changed rows also won't be updated. It's correct only for pure deduplication, not for keeping data current.

The Upsert WHERE Clause Does Not Filter Which Rows Conflict

The WHERE clause on DO UPDATE is not a filter on which incoming rows attempt the upsert. It's a condition on whether to execute the update after a conflict is detected. The conflict detection itself (the ON CONFLICT (id) part) always runs. If the WHERE evaluates to false, PostgreSQL effectively treats the statement as DO NOTHING for that row.

Partial Indexes as Conflict Targets

If your conflict target is a partial index rather than a primary key or unique constraint, you must reference the index's predicate in the ON CONFLICT clause. PostgreSQL won't infer it. Forgetting this causes the statement to fail or fall back to an unintended constraint, which can produce phantom conflicts or missed upserts.

If this situation comes up and you're also debugging row-level anomalies in Python pipelines, the article on fixing Pandas merge producing duplicate rows on non-unique keys shows a parallel pattern of data-identity problems that arise from unexpected key collisions.

Transaction Isolation and Race Conditions

Under high-concurrency write loads, two sessions can both read the same row, both decide it needs updating, and both proceed to write. This is a standard upsert race condition, not specific to the WHERE guard. If you need strict idempotency guarantees in concurrent workloads, consider advisory locks or a serializable isolation level for the upsert transaction.

Wrapping Up

PostgreSQL's ON CONFLICT DO UPDATE is intentionally dumb about diffing old and new values — that's your job. The core fix is a three-line addition to your upsert:

1. Add a WHERE clause to DO UPDATE that compares the business-data columns using IS DISTINCT FROM, or a ROW() composite comparison for wide tables.
2. Set updated_at = now() inside the SET clause, not from EXCLUDED.updated_at, so it only gets stamped when the WHERE guard passes.
3. Add a BEFORE UPDATE trigger if multiple application paths write to the table and you can't guarantee every writer includes the guard.
4. Test with a second identical upsert right after the first: updated_at should not change on the second run if no data changed.
5. Exclude metadata columns (updated_at, created_at, internal IDs) from the ROW() comparison — comparing them causes circular or always-true conditions.