While doing some research on OpenStack (see here for the introduction post), I thought blogging about a new feature for the next version of PostgreSQL might free my head a bit from all those OpenStack services, so here we go 🙂

What you can do in PostgreSQL since a very long time, is to return the values of an insert/update or delete statement like this:

postgres=# create table t ( a int, b text );
CREATE TABLE
postgres=# insert into t values (1,'aaa') returning *;
 a |  b  
---+-----
 1 | aaa
(1 row)

INSERT 0 1
postgres=# update t set b = 'bbb' returning *;
 a |  b  
---+-----
 1 | bbb
(1 row)

UPDATE 1

postgres=# delete from t where b = 'bbb' returning *;
 a |  b  
---+-----
 1 | bbb
(1 row)

DELETE 1

This is especially useful for generated or default values which might not be known from the beginning:

postgres=# drop table t;
DROP TABLE
postgres=# create table t ( a int primary key generated always as identity
                          , b text
                          , c timestamptz default now() );
CREATE TABLE
postgres=# insert into t (b) values ('aaa') returning a,c;
 a |               c               
---+-------------------------------
 1 | 2025-01-22 10:22:13.738709+01
(1 row)

INSERT 0 1

What you can’t do as of today is to reference (or return) the old and new values for the affected rows a the same time. With this patch this changes, and you can return both: old and new:

postgres=# update t set b = 'xxx' returning old.b as old, new.b as new;
 old | new 
-----+-----
 aaa | xxx
(1 row)

UPDATE 1

For an insert statement old values will be null, for a delete statement the new values will be null:

postgres=# insert into t (b) values ('yyy') returning old.b as old, new.b as new;
old | new
----+-----
    | yyy
(1 row)

INSERT 0 1
postgres=# delete from t where b = 'yyy' returning old.b as old, new.b as new;
 old | new 
-----+-----
 yyy | 
(1 row)

DELETE 1

This works (and did before) for multiple rows as well:

postgres=# insert into t (b) select md5(i::text) from generate_series(1,10) i;
INSERT 0 10
postgres=# update t set b = 'qqq' returning old.b as old, new.b as new;
               old                | new 
----------------------------------+-----
 xxx                              | qqq
 c4ca4238a0b923820dcc509a6f75849b | qqq
 c81e728d9d4c2f636f067f89cc14862c | qqq
 eccbc87e4b5ce2fe28308fd9f2a7baf3 | qqq
 a87ff679a2f3e71d9181a67b7542122c | qqq
 e4da3b7fbbce2345d7772b0674a318d5 | qqq
 1679091c5a880faf6fb5e6087eb1b2dc | qqq
 8f14e45fceea167a5a36dedd4bea2543 | qqq
 c9f0f895fb98ab9159f51fd0297e236d | qqq
 45c48cce2e2d7fbdea1afc51c7c6ad26 | qqq
 d3d9446802a44259755d38e6d163e820 | qqq
(11 rows)

UPDATE 11

Nice, thanks to everybody who worked on this.