| 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: | Whole Thread | Raw Message | 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
| 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? |