Обсуждение: Re: Allow ON CONFLICT DO UPDATE to return EXCLUDED values

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

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

Rebased version attached, following 904f6a593a0.

Regards,
Dean