PostgreSQL RETURNING: Read OLD and NEW Row Values

The PostgreSQL RETURNING clause can return values from rows changed by INSERT, UPDATE, DELETE, or MERGE. PostgreSQL 18 adds the old and new row aliases so you can inspect values before and after a change in the same statement.

In PostgreSQL 18, the usual RETURNING value depends on the command: INSERT returns the inserted row, UPDATE returns the updated row, and DELETE returns the deleted row. MERGE can return columns from its source and the target row affected by its action. The old and new aliases let you request both row states explicitly.

Compare values changed by an update

Create a temporary table and add a product to it:

CREATE TEMP TABLE products (
    product_id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL
);

INSERT INTO products (product_id, name, price)
VALUES (1, 'Keyboard', 100);

Update the price and return its value before and after the update:

UPDATE products
SET price = 110
WHERE product_id = 1
RETURNING
    name,
    old.price AS old_price,
    new.price AS new_price,
    new.price - old.price AS price_change;

   name   | old_price | new_price | price_change
----------+-----------+-----------+--------------
 Keyboard |       100 |       110 |           10
(1 row)

For UPDATE, old.column is the value before the change and new.column is the value after it. In this example, new.price is also available as the unqualified price; using new makes the before/after comparison explicit.

Use OLD and NEW with INSERT

For a row inserted without a conflict, old is typically NULL and new is the inserted row:

INSERT INTO products (product_id, name, price)
VALUES (2, 'Mouse', 25)
RETURNING old.product_id AS old_id, new.product_id AS new_id, new.name, new.price;

 old_id | new_id | name  | price
--------+--------+-------+-------
 <null> |      2 | Mouse |    25
(1 row)

The same distinction is useful with INSERT ... ON CONFLICT DO UPDATE. For a conflicting row, old contains the row before the update and new contains the updated row:

INSERT INTO products (product_id, name, price)
VALUES (1, 'Keyboard', 120)
ON CONFLICT (product_id) DO UPDATE
SET price = EXCLUDED.price
RETURNING old.price AS old_price, new.price AS new_price;

This returns old_price = 110 and new_price = 120 for the existing keyboard row.

Use OLD and NEW with DELETE

For a deleted row, old contains the deleted row and new is typically NULL:

DELETE FROM products
WHERE product_id = 1
RETURNING old.product_id, old.name, old.price, new.price AS new_price;

The returned row contains the product’s values before deletion and NULL for new_price.

MERGE also supports old and new in its RETURNING list. Which values are available depends on whether the action inserted, updated, or deleted the target row. See the PostgreSQL MERGE documentation for action-specific details.

Rename the row aliases

If old or new conflicts with a table name or column, PostgreSQL 18 lets you assign different aliases in the RETURNING clause:

UPDATE products
SET price = 130
WHERE product_id = 2
RETURNING WITH (OLD AS before_change, NEW AS after_change)
    before_change.price AS old_price,
    after_change.price AS new_price;

Version compatibility and notes

The old and new aliases in RETURNING are available in PostgreSQL 18 and later. Earlier versions do not support this syntax. If triggers modify a row, RETURNING reports the row as modified by those triggers.

For the basic INSERT, UPDATE, and DELETE forms, see their individual tutorials. PostgreSQL’s official documentation for RETURNING covers all four commands.