PostgreSQL: "UPDATE … RETURNING" made even better

PostgreSQL 18 ships several notable features, including asynchronous I/O and various indexing improvements, but one enhancement deserves closer attention: the extended RETURNING clause. It is easy to overlook, yet it changes how much information a single DML statement can hand back to the caller.

What RETURNING already did

The classic behavior of RETURNING is straightforward: it exposes the rows a DML statement has changed. A small test table with a single row is enough to demonstrate it.

test=# CREATE TABLE t_test AS 
	SELECT 1 AS id, 'Hans' AS name;
SELECT 1

Running an UPDATE that changes the row's ID from 1 to 2 and appending a RETURNING clause yields the new version of the row:

test=# UPDATE t_test 
	SET   id = 2 
	WHERE id = 1 
	RETURNING *;
 id | name 
----+------
  2 | Hans
(1 row)

UPDATE 1

The clause accepts a star for the whole tuple, individual columns, or even expressions, depending on what the application needs. This mechanism has been part of PostgreSQL for many years and is widely used in production code.

NEW and OLD in the RETURNING clause

PostgreSQL 18 adds support for the NEW and OLD keywords, which many users already know from trigger functions. NEW refers to the row as the UPDATE sends it to the table, while OLD refers to the row as it existed before the modification.

The new syntax is applied as follows:

test=# UPDATE t_test 
	SET   id = 3 
	WHERE id = 2 
	RETURNING NEW.*;
 id | name 
----+------
  3 | Hans
(1 row)

UPDATE 1

Functionally this matches the earlier example, modifying one row and returning the change, except that the columns are now referenced through the new row.

More interesting is what OLD makes possible:

test=# UPDATE t_test 
	SET   id = 4 
	WHERE id = 3 
	RETURNING OLD.*;
 id | name 
----+------
  3 | Hans
(1 row)

UPDATE 1

Here PostgreSQL returns the pre-modification data rather than the freshly written version. That opens up a useful trick: comparing the old row against the new one to determine precisely which columns have changed.

test=# UPDATE t_test 
	SET   id = 5 
	WHERE id = 4 
	RETURNING OLD.*, NEW.*;
 id | name | id | name 
----+------+----+------
  4 | Hans |  5 | Hans
(1 row)

UPDATE 1

Composite types and change detection

Because PostgreSQL supports composite data types, a row behaves much like an object or struct in a general-purpose programming language. Two identically shaped composite types can be compared directly, and the database will evaluate every field and return a boolean.

test=# UPDATE t_test 
	SET   id = 6 
	WHERE id = 5 
	RETURNING OLD = NEW;
 ?column? 
----------
 f
(1 row)

UPDATE 1

In other words, the statement asks whether the new row and the old row are identical — did anything actually change? In the listing above, data was modified, so the answer reflects that. When no change occurs, the comparison returns true:

test=# UPDATE t_test 
	SET   id = 6 
	WHERE id = 6 
	RETURNING OLD = NEW;
 ?column? 
----------
 t
(1 row)

UPDATE 1

That behavior makes it simple to add sanity checks in an application: confirm that a row really was updated, or at least verify that the expected number of rows were affected.

Takeaway

Asynchronous I/O is one of the headline items in PostgreSQL 18, and so is the improved RETURNING clause. Between the two, the RETURNING work is the quieter change, but it gives developers a compact way to inspect both sides of a modification without a second query.