Re: PG12 change to DO UPDATE SET column references

On Sat, Jan 20, 2024 at 11:12 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> James Coleman <jtc331(at)gmail(dot)com> writes:
> > Suppose I have this table:
> > create table foo (id int primary key);
>
> > On PG11 this works:
> > postgres=# insert into foo (id) values (1) on conflict (id) do update
> > set foo.id = 1;
> > INSERT 0 1
>
> Hmm, are you sure about that? I get
>
> ERROR: column "foo" of relation "foo" does not exist
> LINE 2: on conflict (id) do update set foo.id = 1;
> ^
>
> in every branch back to 9.5 where ON CONFLICT was introduced.
>
> I'm checking branch tip in each case, so conceivably this is
> something that was changed post-11.0, but I kinda doubt we
> would have back-patched it.

Hmm, I just tested it on the official 11.15 docker image and couldn't
reproduce it. That leads me to believe that the difference isn't in
PG11 vs. 12, but rather in 2ndQuadrant Postgres (which we are running
for PG11, but are not using for > 11). Egg on my face twice in this
thread.

I do wonder if it's plausible (and sufficiently easy) to improve the
error message here. "column 'foo' of relation 'foo'" makes one thing
that you've written foo.foo, (in my real-world case the error message
also cut off the sql past "foo.", and so I couldn't even tell if the
sql was just malformed). At the very least it'd be nice to have a HINT
here (perhaps just when the relation and column name match).

Before I look at where it is, Is such an improvement something we'd be
interested in?

Regards,
James Coleman