PostgreSQL 18 Old & New

 Learning Structured Query Language can be frustrating when double-checking that what you wanted to have done is actually what was done. PostgreSQL 18 has 'OLD and NEW support for RETURNING clauses in INSERT, UPDATE, DELETE, and MERGE commands'. Now you can get instant feedback. 

The addition of the RETURNING clause in the previous version made MERGE much easier to use. Now it makes other commands easier.

To demonstrate, let's create a table with one column that is designated as a unique, primary key integer and insert a value.

create table foo (a int unique primary key);

insert into foo (a) values (1);

Now is the point where many of us would run a SELECT a FROM foo, just to double check that indeed there is a 1 in column a.

There is now an option in PG 18 to use RETURNING, and it provides the previous 'old' value of a column along with the 'new' value.

insert into foo (a) values (2) returning old.a, new.a;

a|a|
-+-+
 |2|

The 'old' value was a NULL, and the new value is 2.

I suggest adding an alias to make things more explicit.

insert into foo (a) values (3) returning old.a as "Old", new.a as "New";

Old|New|

---+---+

   |  3|

Upserts

As I mentioned earlier, sometimes doubt creeps into your mind when you enter or update data.

RETURNING also works with UPSERTS (Thanks to the brilliant Magnus Hagander, who pointed this out out in

his presentation at the PG NYC 2025 show and his slides are available here).

In the following example, we have a conflict because there is already a '2' in the table, so the

ON CONFLICT path is taken. Using RETURNING in this case takes away any doubt of what happened when

this query was executed.

insert into foo(a) values (2)

on conflict(a) do update

set a = 99

returning old.a as old, new.a as update

old|update|

---+------+

  2|    99|