Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT - Mailing list pgsql-hackers

From Robert Haas
Subject Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT
Date
Msg-id CA+TgmoYnY++qFbGw_2HY7zA5Mzcec2D5vm2RYRHYmXfvGke91w@mail.gmail.com
Whole thread Raw
In response to Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT  (Peter Geoghegan <pg@bowt.ie>)
Responses Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT
Re: doc: Clarify what "excluded" represents for INSERT ON CONFLICT
List pgsql-hackers
On Thu, Jun 30, 2022 at 6:40 PM Peter Geoghegan <pg@bowt.ie> wrote:
> My impression from reading this transcript is that the user was
> confused as to why they needed to qualify the target table name in the
> ON CONFLICT DO UPDATE's WHERE clause -- they didn't have to qualify it
> in the targetlist that appears in "SET ... ", so why the need to do it
> in the WHERE clause? This isn't something that upsert statements need
> to do all that often, just because adding additional conditions to the
> WHERE clause isn't usually necessary. That much makes sense to me -- I
> *can* imagine how that could cause confusion.

+1.

I think that the issue here is simply that because both the updated
table and the "excluded" pseudo-table are visible here, and have the
same columns, an unqualified name is ambiguous. I don't really think
that it's worth documenting. The error message you get if you fail to
do it is actually pretty good:

rhaas=# insert into foo values (1, 'frob') on conflict (a) do update
set b = (select b || 'nitz');
ERROR:  column reference "b" is ambiguous
LINE 1: ...'frob') on conflict (a) do update set b = (select b || 'nitz...
                                                             ^

Now you could read that and not understand that the ambiguity is
between the target table and the "excluded" pseudo-table, for sure.
But, would you think to check the documentation at that point? I'm not
sure that's what people would really do. And if they did, I think that
David's proposed patch would be unlikely to make them less confused.
What would probably help more is adding something like this to the
error message:

HINT: column "b" could refer to any of these relations: "foo", "excluded"

That could also help people who encounter this error in other
situations. I'm not 100% sure this is a good idea, but I feel like it
would have a much better chance of helping someone in this situation
than the proposed doc patch.

-- 
Robert Haas
EDB: http://www.enterprisedb.com



pgsql-hackers by date:

Previous
From: Joe Conway
Date:
Subject: Re: Hardening PostgreSQL via (optional) ban on local file system access
Next
From: Robert Haas
Date:
Subject: Re: replacing role-level NOINHERIT with a grant-level option