On Thu, Jun 30, 2022 at 6:40 PM Peter Geoghegan <p...@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