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

Tim Starling <tstarling@wikimedia.org> writes:
> On 28/4/25 20:54, Tom Lane wrote:
>> Even if I were on board with arbitrarily adopting one of the two
>> possible interpretations, it's far from obvious to me that most people
>> would agree that "v" should mean the value from the existing row,
>> rather than the new value.  Better to make them say which they want.

> OK sure, no way to tell, but if every other DBMS does it the same way 
> then that might be a hint.

AFAIK, "ON CONFLICT" is a Postgres-ism.  Exactly which constructs
in exactly which other databases are you citing as precedent?

> In the single-row case, there's no need for EXCLUDED at all, because 
> the client knows everything about the excluded row.

Laurenz already provided the counter-example of an INSERT/SELECT,
but there's also the possibility of the INSERT supplying a computed
default value for a column, e.g., CURRENT_TIMESTAMP.  So you won't
get far with that argument.

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.)

BTW, I did wonder how hard it would be to make such a change.
On first glance it seems to be a one-liner:

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 1f4d6adda52..f11727adbaa 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1306,7 +1306,7 @@ transformOnConflictClause(ParseState *pstate,
          * Add the EXCLUDED pseudo relation to the query namespace, making it
          * available in the UPDATE subexpressions.
          */
-        addNSItemToQuery(pstate, exclNSItem, false, true, true);
+        addNSItemToQuery(pstate, exclNSItem, false, true, false);
 
         /*
          * Now transform the UPDATE subexpressions.

So this isn't about implementation difficulty but about whether
we think it's a good idea.

            regards, tom lane