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 |
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 |