From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tim Starling <tstarling@wikimedia.org>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Upsert error "column reference is ambiguous"
In-reply-to: <a31a76b6-b176-47fe-8778-9dfece231341@wikimedia.org>
References: <a31a76b6-b176-47fe-8778-9dfece231341@wikimedia.org>
Comments: In-reply-to Tim Starling <tstarling@wikimedia.org>
	message dated "Mon, 28 Apr 2025 14:55:58 +1000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <697016.1745837694.1@sss.pgh.pa.us>
Date: Mon, 28 Apr 2025 06:54:54 -0400
Message-ID: <697017.1745837694@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/697017.1745837694%40sss.pgh.pa.us>
Precedence: bulk

Tim Starling <tstarling@wikimedia.org> writes:
> Regarding upsert syntax.
> => INSERT INTO t VALUES (1,1) ON CONFLICT (k) DO UPDATE SET v=v+1;
> ERROR:  column reference "v" is ambiguous

> Please convince me that this is not a bug.

It's not a bug.

> If I understand correctly, in the expression "v+1", both EXCLUDED.v 
> and t.v are present as the unqualified name "v". This is always the 
> case and it is never possible to reference an unqualified field name 
> in the expression of a conflict action.

Correct: it's not clear whether you mean to use "v" from the new
desired-to-be-inserted row or "v" from the existing row.

> Thus, any query with an unqualified name is statically known to be 
> invalid. It is not a b/c break to remove EXCLUDED.v from the list of 
> unqualified fields in a new major release of PG, thus allowing it to DWIM.

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.

			regards, tom lane