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.
Tobias Bussmann
27.01.2025It's a very nice syntax addition indeed. In older versions of PostgreSQL, you can achieve the same result by doing a self-join on the table to be updated:
``` sql
CREATE TABLE change (i, name) AS
SELECT i, 'old ' || i FROM generate_series(1,3) i;
UPDATE change
SET name = 'new ' || change.i
FROM change AS change_old
WHERE change.i = change_old.i
AND change.i >= 2
RETURNING change.i, change_old.name AS old_name, change.name AS new_name;
```
Daniel Westermann
27.01.2025Thank you, Tobias