DEV Community

UPDATE RETURNING OLD | NEW in YugabyteDB and PostgreSQL

Franck Pachot on June 04, 2023

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 r...
Read full post
Collapse
 
stephenflavin profile image
Stephen Flavin • • Edited

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 ;
Enter fullscreen mode Exit fullscreen mode

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