Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

Q: Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

**Target Audience:** Developers who already use `INSERT ... ON CONFLICT` and now hit the follow-ups: stale overwrites, "was it created?", and soft-deleted rows. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 (`xmax`, locking behaviour) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples). PostgreSQL 17 `MERGE ... RETURNING` is cited, not run (the local server is 16). > **How to read this doc:** Plain language first, then a `📌 Interview term:` callout with the precise vocabulary. All outputs, error messages and lock waits below come from real runs; the **Verified** section quotes every example output in full. ## 1. Why This Even Matters — A Story First A shop window shows today's price on a chalkboard. Head office sends price updates by courier, and couriers get stuck in traffic. At 10:05 a courier arrives with "Monitor arm: 49" written at 08:00, but the board already says 45, written from a note dated 09:30. A careful shopkeeper checks the date on the note before w

The problem

Question presented to candidate: "Your price feed sends events out of order, and an older price sometimes overwrites a newer one. Separately, product wants a 'welcome' email only when a customer row is created, not when it is updated, and customers are soft-deleted with a deleted_at column, so a person who deleted their account must be able to sign up again with the same email. How do you handle all three with upserts?"

What a strong answer should cover:

Conditional upsert: ON CONFLICT ... DO UPDATE SET ... WHERE excluded.source_ts > prices.source_ts (newest wins) or WHERE t.col IS DISTINCT FROM excluded.col (skip no-op writes); rows whose WHERE is false are not updated and not returned by RETURNING, but they are still locked
Insert-or-update detection in PostgreSQL: RETURNING (xmax = 0) AS inserted; what xmax is, why it is 0 for a new row and non-zero after the DO UPDATE path, and that this is an implementation detail rather than a documented API
Portable alternatives: a version (or created_at = updated_at) column, or MERGE ... RETURNING merge_action() on PostgreSQL 17+
Soft delete: a partial unique index UNIQUE (email) WHERE deleted_at IS NULL, and an upsert whose conflict target repeats the predicate: ON CONFLICT (email) WHERE deleted_at IS NULL
What breaks: a conflict target without the predicate does not match the partial index and is rejected; a plain UNIQUE (email) constraint would block re-signup

Clarifying questions expected:

Does every event carry a reliable timestamp or version to compare against?
Does "created" need to be known only in the same statement, or later too?
Should a re-signup revive the old soft-deleted row or create a new one?

Code / implementation expected: Yes — a conditional DO UPDATE ... WHERE, a RETURNING that tells inserts from updates, and a partial unique index used as a conflict target.

Schema used by the examples:

upsert
idempotency
constraints

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who already use INSERT ... ON CONFLICT and now hit the follow-ups: stale overwrites, "was it created?", and soft-deleted rows. Difficulty: Medium Engines: PostgreSQL 16.15 (xmax, locking behaviour) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples). PostgreSQL 17 MERGE ... RETURNING is cited, not run (the local server is 16).

How to read this doc: Plain language first, then a 📌 Interview term: callout with the precise vocabulary. All outputs, error messages and lock waits below come from real runs; the Verified section quotes every example output in full.

1. Why This Even Matters — A Story First

A shop window shows today's price on a chalkboard. Head office sends price updates by courier, and couriers get stuck in traffic. At 10:05 a courier arrives with "Monitor arm: 49" written at 08:00, but the board already

The solution is hidden so you can try first

16 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLNewest wins: DO UPDATE ... WHERE excluded.source_ts > prices.source_ts

SQLSoft delete: partial unique index as the conflict target lets Ben sign up again

SQLPortable insert-or-update flag: a version column read back with RETURNING

PostgreSQL: RETURNING (xmax = 0) AS inserted, with a conditional update

WITH up AS (
  INSERT INTO prices (sku, price_cents, source_ts)
  VALUES ('P-1', 1799, '2024-06-02 08:00'),
         ('P-2', 4900, '2024-05-30 23:00'),
         ('P-3', 750,  '2024-06-04 12:00'),
         ('P-4', 300,  '2024-06-02 08:00')
  ON CONFLICT (sku) DO UPDATE
  SET price_cents = excluded.price_cents,
      source_ts   = excluded.source_ts,
      version     = prices.version + 1
  WHERE excluded.source_ts > prices.source_ts
  RETURNING sku, price_cents, version, (xmax = 0) AS inserted
)
SELECT sku, price_cents, version,
       CASE WHEN inserted THEN 'inserted' ELSE 'updated' END AS action
FROM up
ORDER BY sku;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.