PostgreSQL 18 RETURNING OLD and NEW: what it returns, with tested examples

The short answer

PostgreSQL 18, released on 25 September 2025, adds OLD and NEW support to RETURNING in INSERT, UPDATE, DELETE and MERGE.¹ You qualify a column with old. or new. to get its value before or after the change:

Code A database command in SQL. The text around it says what it does.

UPDATE products SET price_cents = price_cents + 100
WHERE sku IN ('P001', 'P002', 'P003')
RETURNING sku, old.price_cents AS before, new.price_cents AS after;

Before 18, RETURNING gave only new values for INSERT and UPDATE and only old values for DELETE.¹ Getting the old value of an updated row meant a separate SELECT … FOR UPDATE first. Now one statement returns both, which makes it practical to record exactly what each write changed.

What changed in 18

An unqualified column still returns the new value, as before, so existing queries don’t change.²

UPDATE: before and after

All output in this post comes from one run on PostgreSQL 18.6 on a laptop, using the 100-product table from our benchmark’s scenario 12.⁴

Code What the screen or file shows, copied as is.

 sku  | before | after
------+--------+-------
 P001 |   1001 |  1101
 P002 |   1002 |  1102
 P003 |   1003 |  1103
(3 rows)

UPDATE 3

Now an update that changes nothing:

Code A database command in SQL. The text around it says what it does.

UPDATE products SET name = name
WHERE sku = 'P004'
RETURNING WITH (OLD AS o, NEW AS n) sku, o.name IS DISTINCT FROM n.name AS changed;

Code What the screen or file shows, copied as is.

 sku  | changed
------+---------
 P004 | f
(1 row)

UPDATE 1

The command tag says UPDATE 1, because Postgres counts matched rows, including rows whose values didn’t change.³ RETURNING with old and new lets you tell a real change from a no-op write, which the tag can’t.⁴

You can aggregate in the same statement. Wrapped in a transaction we rolled back, a broad update reported its own blast radius before anyone committed it:

Code A database command in SQL. The text around it says what it does.

BEGIN;
WITH u AS (
  UPDATE products SET price_cents = 0 WHERE price_cents > 1050
  RETURNING old.price_cents AS before
)
SELECT count(*) AS rows_changed, sum(before) AS value_before FROM u;
ROLLBACK;

Code What the screen or file shows, copied as is.

 rows_changed | value_before
--------------+--------------
           48 |        51591

INSERT and DELETE

For a plain INSERT, every old value is NULL. With ON CONFLICT DO UPDATE, old holds the previous values for rows that conflicted, and is NULL for rows that were inserted.² That makes old.<key> IS NULL a reliable way to count inserts and updates in an upsert; our ON CONFLICT post shows a 12-row upsert that split into 7 inserts and 5 overwrites.⁴

For DELETE, old is the deleted row and new is NULL:

Code A database command in SQL. The text around it says what it does.

DELETE FROM products WHERE sku = 'P010'
RETURNING old.sku, new.sku IS NULL AS new_is_null;

Code What the screen or file shows, copied as is.

 sku  | new_is_null
------+-------------
 P010 | t
(1 row)

DELETE 1

Aliases when old and new clash

If your table has a column called old or new, or you’re inside a trigger function where those names already mean something, rename them. The syntax is RETURNING WITH (OLD AS o, NEW AS n), after which the old and new rows must be referred to by the aliases.² The release notes mention this explicitly: the aliases can be renamed to avoid identifier conflicts.¹ The no-op example above uses it.

Replacing the read-then-write pattern

Before 18, code that needed the old value usually did this: SELECT … FOR UPDATE to lock and read the rows, then UPDATE them, then compare in the application. That’s two round trips, a lock held between them, and a window where the selected rows and the updated rows could differ if the WHERE clause matched differently the second time.

With RETURNING old, the read and the write are the same statement, so the old values describe exactly the rows that were written. There’s no second query to drift from the first. If you have tools or migrations built on the old pattern, they’re worth revisiting once you’re on 18: the code gets shorter, and the record gets more accurate.

Drivers need nothing special. RETURNING output comes back as an ordinary result set, the same way a SELECT does, so any client library that can read query results can read old and new values.

Three uses for agent writes

1. A record of what changed, not just how much. An agent’s tool can return old.* and new.* for every row it wrote. Store that with the action, and a reviewer later sees the actual before and after, not only “UPDATE 3”. For an agent action record, that is the difference between knowing something changed and knowing what.

2. A built-in undo. With the old values in hand, reversing a write is an UPDATE … FROM (VALUES …) that sets them back, for rows that nobody has changed since. Check that the current values still equal the new values first, or you’ll overwrite someone else’s later edit.

3. Honest summaries. Make the agent’s tool report rows actually changed (old differs from new), inserts versus updates, and the sum of what moved. An agent can only report what its tool tells it; give it the real numbers.

A checklist for adopting it

1. Confirm the server version. SHOW server_version; must say 18 or later; earlier versions don’t have the old and new qualifiers in RETURNING.
2. Return the key with every row, so old and new values can be matched to a record later.
3. Return only the columns you need. old.* on a wide table with large text columns makes every write’s result much bigger.
4. Store the result with the action, not only in application logs that get rotated.
5. Keep sensitive columns out, or hash them, if the record goes somewhere with wider access than the table.
6. Test the no-op case. Make sure your tool reports a write that changed nothing as exactly that.

What RETURNING doesn’t show

RETURNING covers the rows the statement itself wrote in the target table. It doesn’t include:

• Rows changed by triggers in other tables, or by ON DELETE CASCADE. Our 1 row vs 301 rows case is exactly that: the statement’s own rows looked fine while a trigger changed 300 more.
• Rows another session changes at the same time.
• Effects outside rows, such as a NOTIFY that sends email.

For those, you need a view from the database’s side: triggers that log, or the change log through logical decoding. Our comparison of pgaudit, triggers and logical decoding explains the trade-offs, and a read-back check covers the case where you can only read the result afterwards.

RETURNING old, new is still the cheapest improvement you can make to an agent’s database tool today: one clause, no extra round trip, and the agent’s report gets a lot closer to the truth. To try writes and checks on a real Postgres in your browser, open the playground; to grade what an agent can reach on your machine, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.