Allow ON CONFLICT DO UPDATE to return EXCLUDED values

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Allow ON CONFLICT DO UPDATE to return EXCLUDED values
Date: 2025-06-24 18:49:23
Message-ID: CAEZATCXXu2ohYmn=4YrRQa9yNwD_fdEEOTBPgM_5jhQOFcaQ4g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patch text/x-patch 61.4 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Navrotskiy Artem 2025-06-24 19:32:07 Re: Add Option To Check All Addresses For Matching target_session_attr
Previous Message Christoph Berg 2025-06-24 18:24:22 Re: pgsql: Introduce pg_shmem_allocations_numa view