PG12 change to DO UPDATE SET column references

From: James Coleman <jtc331(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: PG12 change to DO UPDATE SET column references
Date: 2024-01-19 17:00:42
Message-ID: CAAaqYe8S2Qa060UV-YF5GoSd5PkEhLV94x-fEi3=TOtpaXCV+w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

I realize this is almost ancient history at this point, but I ran into
a surprising behavior change from PG11->12 with ON CONFLICT ... DO
UPDATE SET ...

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

But on PG12+ this is the result:
postgres=# insert into foo (id) values (1) on conflict (id) do update
set foo.id = 1;
ERROR: column "foo" of relation "foo" does not exist
LINE 1: ...oo (id) values (1) on conflict (id) do update set foo.id = 1...

Making this more confusing is the fact that if I want to do something
like "SET bar = foo.bar + 1" the table qualification cannot be present
on the setting column but is required on the reading column.

There isn't anything in the docs that I see about this, and I don't
see anything scanning the release notes for PG12 either (though I
could have missed a keyword to search for).

Was this intended? Or a side effect? And should we explicitly document
the expectations here

The error is also pretty confusing: when you miss the required
qualification on the read column the error is more understandable:
ERROR: column reference "bar" is ambiguous

It seems to me that it'd be desirable to either allow the unnecessary
qualification or give an error that's more easily understood.

Regards,
James Coleman

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amul Sul 2024-01-19 17:06:26 Re: Add system identifier to backup manifest
Previous Message Heikki Linnakangas 2024-01-19 16:54:12 Re: Change GUC hashtable to use simplehash?