In PostgreSQL 18, how can a single INSERT ... ON CONFLICT DO UPDATE report which rows were inserted versus updated, and each row's previous price?
answer
- RETURNING grew two aliases
- old side empty on insert
- rename to avoid clashes
- MERGE has its own function
basics
~20 sPostgreSQL 18 lets RETURNING read old and new: old values are NULL for inserted rows and hold the prior values for conflicting rows that were updated, so old.product_id IS NULL flags inserts and old.price gives the previous price.
solid answer
~40 sSince PostgreSQL 18, `RETURNING` in `INSERT`, `UPDATE`, `DELETE` and `MERGE` can qualify columns with the special aliases `old` and `new`. For an `INSERT ... ON CONFLICT DO UPDATE`, rows that were plainly inserted have every `old` value NULL, while rows that took the update path carry the pre-update values, so `RETURNING new.product_id, old.product_id IS NULL AS inserted, old.price AS old_price, new.price` answers both questions in one round trip; test a NOT NULL column such as the key. If `old` or `new` clashes with a real name, rename them with `RETURNING WITH (OLD AS o, NEW AS n)`. Rows skipped by `DO NOTHING` or a failing `DO UPDATE ... WHERE` are not returned at all. In `MERGE`, available since 17, `merge_action()` returns `'INSERT'`, `'UPDATE'` or `'DELETE'` per row.
code
sql · 14 linesWITH changed AS (
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 price = EXCLUDED.price, stock = EXCLUDED.stock
WHERE p.price IS DISTINCT FROM EXCLUDED.price
RETURNING new.product_id,
old.product_id IS NULL AS inserted,
old.price AS old_price, new.price AS new_price
)
INSERT INTO price_change_log (product_id, old_price, new_price, action)
SELECT product_id, old_price, new_price,
CASE WHEN inserted THEN 'new' ELSE 'repriced' END
FROM changed;go deeper
Remember that PostgreSQL 18's RETURNING can say old.price and new.price, and that old values are NULL for rows that were simply inserted.
Explain the outcome table: inserted, updated, filtered and skipped rows, which ones are returned, and why the insert flag must test a NOT NULL column.
Show how you would feed an audit or change log from one statement, choose between 18's old/new and 17's merge_action(), and retire xmax-based hacks.
Weigh building change capture into the write statement against triggers or logical decoding, and what each costs in coupling and upgrade timing.
## The reporting problem After each hourly supplier-feed sync, the shop wants an audit trail: which products are new, which had their price changed and from what. Before PostgreSQL 18, `RETURNING` on an `INSERT ... ON CONFLICT DO UPDATE` exposed only the **new** row, so the old price was gone by the time the client saw the result, and telling an insert from an update needed workarounds. ## OLD and NEW in RETURNING (PostgreSQL 18) PostgreSQL 18 added two special aliases to the `RETURNING` list of `INSERT`, `UPDATE`, `DELETE` and `MERGE`: - **`old.column`** / **`old.*`** — the row as it was before the command touched it; - **`new.column`** / **`new.*`** — the row as the command left it. An unqualified column, or one qualified by the table name or alias, still means the **new** value, so existing statements behave as before. For an upsert the two sides tell the story: | Outcome for a proposed row | `old` values | `new` values | Returned? | |---|---|---|---| | Inserted (no conflict) | all `NULL` | the inserted row | yes | | Conflict, `DO UPDATE` applied | the stored row before the update | the updated row | yes | | Conflict, `DO UPDATE ... WHERE` false | — | — | no | | Conflict under `DO NOTHING` | — | — | no | So `old.product_id IS NULL` is a reliable 'was inserted' flag, provided the column tested can never be NULL in a stored row — an identity primary key is ideal. Testing a nullable column such as `old.discontinued_at` would misclassify updated rows whose stored value happened to be NULL. When `old` or `new` collides with a real name — a table literally called `new`, or a trigger-function context — rename them: `RETURNING WITH (OLD AS o, NEW AS n) o.price, n.price`. Once renamed, the original names are hidden. ## MERGE and merge_action() `MERGE` gained `RETURNING` in **PostgreSQL 17**, together with the function **`merge_action()`**, which returns the text `'INSERT'`, `'UPDATE'` or `'DELETE'` for the action applied to each row. It is legal only in a `MERGE` statement's `RETURNING` list; anywhere else it is an error. In 18, `MERGE ... RETURNING` can also use `old` and `new`, so a sync that inserts, reprices and delists in one `MERGE` can report all three with prior values. ## What people did before 18 1. Read the rows first in a writable CTE or a separate query, then upsert — extra work, and the read can be stale under concurrency. 2. Return `xmax = 0` as an 'inserted' flag. This relied on an undocumented detail of the `xmax` system column and was never a supported interface; on 18 there is no reason to keep it. 3. Use `MERGE ... RETURNING merge_action()` on 17, accepting `MERGE`'s weaker concurrency guarantees. ## Practical points - `RETURNING` reflects the row **as modified by triggers**, so a `BEFORE UPDATE` trigger that stamps `updated_at` shows up in `new`. - `RETURNING` needs `SELECT` privilege on every column it mentions. - In a writable CTE, the `RETURNING` output is the only way the rest of the statement sees what the upsert did, so `old`/`new` make that hand-off far richer. - The plain-`INSERT` case is unchanged: with no `ON CONFLICT`, every `old` value is `NULL`. - For `UPDATE`, `old` makes 'what did this change?' answerable without a prior `SELECT ... FOR UPDATE`; for `DELETE`, `new` is normally all `NULL`. - Mixed fleets: a statement using `old`/`new` is rejected by 17 and earlier, so code shared with older servers needs the version checked before relying on it. ```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 price = EXCLUDED.price, stock = EXCLUDED.stock RETURNING new.product_id, old.product_id IS NULL AS inserted, old.price AS old_price, new.price AS new_price; ```
- Why are products whose price did not change missing from that audit output?The `DO UPDATE` carries `WHERE p.price IS DISTINCT FROM EXCLUDED.price`. Conflicting rows that fail it are locked but not updated, and `RETURNING` reports only rows actually inserted or updated, so they produce no output row. That is usually what an audit wants; drop the `WHERE` only if every touched SKU must be listed, at the cost of a new row version each hour.
- On PostgreSQL 17, how would you report insert versus update for the same sync?Rewrite it as `MERGE ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... RETURNING merge_action(), p.*`, which 17 supports. You still cannot read the previous price there, because `old` arrived in 18; and you inherit `MERGE`'s concurrency behaviour, where a racing insert of the same key raises `23505` instead of turning into an update.
- RETURNING old.* works on PostgreSQL 16 and 17 as well.
- merge_action() can also be called in the RETURNING list of INSERT ... ON CONFLICT.
- An unqualified column in RETURNING now returns the old value.
- Skipped DO NOTHING rows come back with every new value NULL.
- old.discontinued_at IS NULL is a safe insert flag on any column.
Assumes PostgreSQL 18, where RETURNING gained OLD and NEW; MERGE ... RETURNING and merge_action() date from 17.