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
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? |