Re: INSERT ... ON CONFLICT DO SELECT [FOR ...] take 2

> On 3/4/25 10:24 AM, Andreas Karlsson wrote:
> Rebased the patch to add support for OLD.* and NEW.*.

create table t (key int primary key, val text);
insert into t values (1, 'old');

insert into t values (1, 'new') on conflict (key) do select for update
returning old.*, new.*;

key | val | key | val
-----+-----+-----+-----
1 | old | |
(1 row)

IMO this should cause new.* be the same as old.*. This is kind-of like
a "do update" that changes nothing, with the end result being that old
and new are the same, because nothing was changed. Currently, the only
command that can cause new to be NULL is a DELETE, because the new
state of the table is that the row no longer exists, which isn't the
case here.

On Mon, 10 Mar 2025 at 13:11, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> On Mon, 10 Mar 2025 at 18:05, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
> >
> > 1) Should we answer INSERT 0 10 here?
>
> Sorry, i mean: INSERT 0 0

Hmm, I would say that the patch is correct -- the count should be the
number of rows inserted, updated or selected for return (and the
"Outputs" section of the doc page for INSERT should be updated to say
that). That way, the count always matches the number of rows returned
when there's a RETURNING clause, which I think is true of all other
DML commands.

Regards,
Dean