Postgres 18

Postgres 18: OLD and NEW Rows in the RETURNING Clause

Brandur Leach

2 min readMore by this author

Postgres 18 was released today. Well down page from headline features like async I/O and UUIDv7 support, we get this nice little improvement:

This release adds the capability to access both the previous (OLD) and current (NEW) values in the RETURNING clause for INSERT, UPDATE, DELETE and MERGE commands.

It's not a showstopper the way async I/O is, but it is one of those small features that's invaluable in the right situation.

A simple demonstration with UPDATE to get all old and new values:

UPDATE fruit
SET quantity = 300
WHERE item = 'Apples'
RETURNING OLD.*, NEW.*;

 id |  item  | quantity | id |  item  | quantity
----+--------+----------+----+--------+----------
  5 | Apples |      200 |  5 | Apples |      300
(1 row)

Detecting new rows with OLD on upsert

Say we're doing an upsert and want to differentiate between whether a row sent back by RETURNING was one that was newly inserted or an existing row that was updated. This was possible before, but relied on an unintuitive check on xmax = 0 (see the very last line below):

INSERT INTO webhook (
    id,
    data
) VALUES (
    @id,
    @data
)
ON CONFLICT (id)
    DO UPDATE SET id = webhook.id -- force upsert to return a row
RETURNING webhook.*,
    (xmax = 0) AS is_new;

The statement relies on xmax being set to zero for a fresh insert as an artifact of Postgres' locking implementation (see a full explanation for why this happens). It works, but isn't a guaranteed part of the API, and could conceivably change at any time.

In Postgres 18, we can reimplement the above so it's more legible and doesn't rely on implementation details. It's easy too -- just check whether OLD is null in the returning clause:

INSERT INTO webhook (
    id,
    data
) VALUES (
    @id,
    @data
)
ON CONFLICT (id)
    DO UPDATE SET id = webhook.id -- force upsert to return a row
RETURNING webhook.*,
    (OLD IS NULL)::boolean AS is_new;

Access to OLD and NEW will undoubtedly have many other useful cases, but this is one example that lets us improve pre-18 code right away.

Related Articles