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