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