Re: Upsert error "column reference is ambiguous" - Mailing list pgsql-general

On Tue, 29 Apr 2025 at 01:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I do actually have some sympathy for your proposal after thinking
> about it a bit more, but the argument I would use is "the behavior
> of the ON CONFLICT UPDATE SET list should be as much as possible like
> the behavior of an ordinary UPDATE's SET list".  Since "v = v+1" would
> refer to the existing row's "v" in regular UPDATE, it's sensible to
> let that happen here too.  Of course the counter-argument is that this
> should be compared not to a trivial UPDATE, but an "UPDATE ... FROM
> othertable" where the othertable supplies some conflicting column
> name(s).  In that situation we're going to make you resolve the
> conflict by qualifying the column names.  The only thing that makes
> that not a precise parallel is that EXCLUDED is not something the user
> wrote into the query explicitly, so there's no opportunity to
> substitute different column aliases, as a FROM clause would allow.
> Perhaps that justifies demoting it to second-class citizenship whereby
> EXCLUDED has to be qualified but the target table doesn't.  (I don't
> find this argument hugely compelling, but it's an argument.)

Not arguing for or against, but... I think there are some cases where
it would be more dangerous to relax this.  Here's one case where not
qualifying the column can be dangerous:

create table a1 (a int);
insert into a1 values(1),(2);
create table a2 (a int);
insert into a2 values(1);
select * from a1 where a in(select a from a2); -- as expected.

-- application changes, a2.a isn't needed anymore. column gets dropped
but someone forgets to update a query in the app...
alter table a2 drop column a;
select * from a1 where a in(select a from a2); -- silently returns
unexpected results.

If the original author of that query had been thoughtful enough to
qualify the column in the subquery then someone would probably have
gotten an error and fixed it. The moral of that story is that
sometimes forcing the query author to qualify the column is a good
idea. (not that there's much we can do about that one...)

Now the question is, do any similar hazards exist with ON CONFLICT DO
UPDATE? I don't think so as any columns being dropped will disappear
from the insert target table and the EXCLUDED work table at the same
time.

Another thought is that you can have an UPDATE with a RETURNING
clause. An unqualified column defaults to NEW even though you could
argue it's ambiguous due to OLD (as of 80feb727c). Likely we were
forced into making it work that way through not wanting to force
everyone to rewrite their RETURNING statements when upgrading to v18.
The moral of that story is, UPDATE isn't exactly consistent already
about when it requires column qualifications. Maybe it's weird to
insist that users qualify columns with their ON CONFLICT UPDATE SET
when RETURNING is happy to assume you must have meant NEW.

David