Allow ON CONFLICT DO UPDATE to return EXCLUDED values

Dean Rasheed <dean.a.rasheed@gmail.com>

From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2025-06-24T18:49:23Z
Lists: pgsql-hackers

Attachments

The attached patch allows EXCLUDED values to appear in the RETURNING
list of INSERT ... ON CONFLICT DO UPDATE. For example:

CREATE TABLE t (a int PRIMARY KEY, b text);
INSERT INTO t VALUES (1, 'old value');

INSERT INTO t VALUES (1, 'excluded value')
  ON CONFLICT (a) DO UPDATE SET b = 'new value'
  RETURNING a, old.b, new.b, excluded.b;

 a |     b     |     b     |       b
---+-----------+-----------+----------------
 1 | old value | new value | excluded value
(1 row)

If there is no conflict, then OLD and EXCLUDED values are NULL.


For the most part, this is just an extension of the code to support
returning OLD and NEW. Originally, I had intended to not use
varreturningtype, since EXCLUDED is a different RTE than the result
relation, so the executor just uses the Var's varno (set to INNER_VAR
in setrefs.c). However, the rewriter code needed to support updatable
views and virtual generated columns turns out to be simpler if these
Vars have a separate varreturningtype.

I still have a lot more testing to do, and docs to update, but so far
the results look promising. I'll add this to the next CF.

Regards,
Dean