UPDATE RETURNING OLD | NEW in YugabyteDB and PostgreSQL

It is a good idea to prefer UPDATE ... RETURNING instead of a two-statement transaction with an UPDATE and a SELECT. By default, the new value is returned. Oracle 23c has added the possibility to return the old values.

Here is how to do the same in YugabyteDB and PostgreSQL.

yugabyte=# create table demo (id bigint primary key, value text);
CREATE TABLE

yugabyte=# insert into demo 
           select generate_series(1,50), '...old...';
INSERT 0 50

yugabyte=# update demo set value='...new...' where id=42
           returning id, value;

 id |   value
----+-----------
 42 | ...new...
(1 row)

UPDATE 1

Here is how I run the same to return the old and new values:

yugabyte=# with old as (select * from demo where id=42)
           update demo new set value='...new...'
           from old where old.id=new.id
           returning new.id, old.value, new.value ;

 id | old_value | new_value
----+-----------+-----------
 42 | ...old... | ...new...

(1 row)

UPDATE 1

The WITH clause (also called CTE for Common Table Expression) is very helpful to do multiple reads and write operations within one statement. The advantage of declaring the logic in a single SQL statement makes it more efficient and scalable by reducing the roundtrips, increasing the possibilities of transparent retries (on clock skew for example) and avoiding anomalies even in read committed isolation level.

Comments

[Stephen Flavin]

No need for the CTE

update demo new
set value='...new...'
from (select id, value
      from demo
      where id=42) old
where old.id=new.id
returning new.id, old.value, new.value ;

Recent versions of Postgres will automatically inline it like this unless you use WITH ... AS MATERIALIZED.
Great to see Oracle 23c has implemented it without the need for the extra select, hopefully we'll see it in Postgres in future