Allow ON CONFLICT DO UPDATE to return EXCLUDED values
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
Attachments:
v1-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchDownload+366-151
On Tue, 24 Jun 2025 at 19:49, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
The attached patch allows EXCLUDED values to appear in the RETURNING
list of INSERT ... ON CONFLICT DO UPDATE.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.
v2 attached, now with docs and more test cases.
Regards,
Dean
Attachments:
v2-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchDownload+717-235
Hi Dean
On 30.06.25 19:18, Dean Rasheed wrote:
v2 attached, now with docs and more test cases.
I'm wondering if the current behaviour with DEFAULT columns should be
mentioned in the docs.
CREATE TABLE t2 (a int PRIMARY KEY, b text DEFAULT 'default value');
INSERT INTO t2 VALUES (1, 'old value');
INSERT INTO t2 VALUES (1)
ON CONFLICT (a) DO UPDATE SET b = 'new value'
RETURNING a, old.b, new.b, excluded.b;
CREATE TABLE
INSERT 0 1
a | b | b | b
---+-----------+-----------+---------------
1 | old value | new value | default value
(1 row)
While it may seem intuitive, it's possible that it could cause confusion
for some users.
Best, Jim
On Mon, 7 Jul 2025 at 11:38, Jim Jones <jim.jones@uni-muenster.de> wrote:
I'm wondering if the current behaviour with DEFAULT columns should be
mentioned in the docs.
Hmm, perhaps. On the INSERT page, under "conflict_action", we currently say
Note that the effects of all per-row BEFORE INSERT triggers are
reflected in excluded values, since those effects may have
contributed to the row being excluded from insertion.
perhaps that should say
Note that any values that were supplied by defaults, as well as
the effects of all per-row BEFORE INSERT triggers are reflected
in excluded values, since that may have contributed to the row
being excluded from insertion.
Regards,
Dean
On 07.07.25 19:52, Dean Rasheed wrote:
perhaps that should say
Note that any values that were supplied by defaults, as well as
the effects of all per-row BEFORE INSERT triggers are reflected
in excluded values, since that may have contributed to the row
being excluded from insertion.
+1
IMO a short mention at insert.sgml also wouldn't hurt, e.g.
If the INSERT has an ON CONFLICT DO UPDATE clause, a column name or *
may be qualified using EXCLUDED to return the values that would have
been inserted. These values include any DEFAULT expressions or computed
expressions from the INSERT clause. If there is no conflict, then all
EXCLUDED values will be NULL.
Best, Jim
On Mon, 7 Jul 2025 at 19:09, Jim Jones <jim.jones@uni-muenster.de> wrote:
On 07.07.25 19:52, Dean Rasheed wrote:
perhaps that should say
Note that any values that were supplied by defaults, as well as
the effects of all per-row BEFORE INSERT triggers are reflected
in excluded values, since that may have contributed to the row
being excluded from insertion.+1
IMO a short mention at insert.sgml also wouldn't hurt, e.g.
If the INSERT has an ON CONFLICT DO UPDATE clause, a column name or *
may be qualified using EXCLUDED to return the values that would have
been inserted. These values include any DEFAULT expressions or computed
expressions from the INSERT clause. If there is no conflict, then all
EXCLUDED values will be NULL.
Thanks. I like some of this text better than what I originally wrote.
However, I don't like repeating different fragments of information in
different places on the same page. I think it's better to have a
single place on that page that describes all the properties of the
special excluded table, and then refer to that. In the attached v3,
I've done that in the form of a <note>, which makes it stand out more,
and is easier to refer to.
Regards,
Dean
Attachments:
v3-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchtext/x-patch; charset=US-ASCII; name=v3-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchDownload+740-246
Rebased version attached, following 904f6a593a0.
Regards,
Dean
Attachments:
v4-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchtext/x-patch; charset=US-ASCII; name=v4-0001-Allow-EXCLUDED-in-RETURNING-list-of-INSERT-ON-CON.patchDownload+741-246
(I realised I created a new thread by mistake, hopefully now I’ll get things back into the main one)
On 7 Oct 2025 at 23:52 +0200, Dean Rasheed <dean.a.rasheed@gmail.com>, wrote:
On Tue, 7 Oct 2025 at 14:56, Viktor Holmberg <v@viktorh.net> wrote:
I’ve looked through this patch. As far as I can tell, everything looks good, working and well commented.
The only nitpick I could find is a mispelling "EXLCUDED" → "EXCLUDED" in src/test/regress/expected/returning.out:464.
Thanks for looking. I'm also glad to see that you picked up the INSERT
... ON CONFLICT DO SELECT patch, because I think these 2 features
should work well together. I'll take another look at that one, but I'm
not going to have any time this week.
Agree. It’d be great if you could have a look, but no rush - I’m going on holiday now for a week anyway.
A maybe bigger question, is it nice that EXCLUDED is null when no conflict occurred? I can see the logic, but I think ergonomics wise it’d be nicer to have the proposed values in EXCLUDED, no matter what happened later. Then one can check EXCLUDED.value = NEW.value to see if one’s changes were added, for example.
Hmm, I'm not sure. I think it would be counter-intuitive to have
non-null EXCLUDED values for rows that weren't excluded, and I think
it's just as easy to check what values were added either way.
I see the point - I guess I think about EXCLUDED more as “PROPOSED”. I don’t have any examples at hand that would substantiate my point of view so it’s not a strong objection. In my opinion this patch adds value regardless, and you’re right that adapting the code to either case isn’t a big deal.