PostgreSQL: "UPDATE … RETURNING" made even better

PostgreSQL 18 offers a couple of new features such as asynchronous I/O (aio), improvements around indexing, and a lot more. However, there is one feature that has really caught my attention. While it might go unnoticed by many users, it is really powerful and, to me, somehow represents everything I like about PostgreSQL.

The improvement I want to focus on today is related to the way the "RETURNING-clause" works in PostgreSQL 18.

Using RETURNING-clauses

Before we dive into the new functionality, we have to create a simple test table containing a single row of data:

The way RETURNING works is that it allows us to see what a DML statement has changed. 

Here is an example:

The statement finds our row and modifies the ID by changing it from 1 to 2. The RETURNING-clause at the end can be used to fetch whatever columns we want from the new rows that have been written to the table. By placing a star, we will get the entire tuple, but we can of course simply list single columns or even expressions (depending on our needs).

This feature has existed for many years now and has been adopted by a fair number of applications.

RETURNING in PostgreSQL 18 and beyond

What is new in PostgreSQL is support for the NEW and OLD keywords, which some users might already know from PostgreSQL trigger functions. What is the purpose of those two keywords? Well, NEW represents the new row as it is sent to the table by our UPDATE statement and OLD represents the row as it has been before the modification. 

The following listing shows how the new syntax can be applied:

Essentially, the example does the same as the previous one - we modify one row and return the change. In this case we refer to all columns in the new row.

However, it is more interesting to see what the new OLD keyword can do for us:

This is new behavior. Instead of looking at the new data we have just added, PostgreSQL returns the data as it was before. Obviously, this allows us to make use of a really interesting trick: We can actually compare the old to the new row and determine exactly what has changed.

Here is how it works:

By simply listing all columns, we can get exactly what we need.

RETURNING and composite data types

PostgreSQL features composite data types. Basically, a row is like an object or a structure one would use in various programming languages. The beauty of PostgreSQL is that if you have two identical composite types, you can simply compare them out of the box. The database will compare all fields and return a boolean value.

Here is how it works:

What we do here is: We can ask PostgreSQL if the new and the old row are indeed identical. So, did a change actually happen? In the previous listing, data has been modified - in the next listing, you can see that in case no change has happened, the return value is in fact true:

This opens the possibility to add a couple of sanity checks to your application easily to ensure that data has indeed been updated or that at least n number of rows have been subject to change.

Finally ...

PostgreSQL 18 comes with a couple of really powerful features, with RETURNING and asynchronous I/O being among my favorite ones. If you want to learn more about asynchronous I/O, consider checking out my post about it as well.