public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Tim Starling <[email protected]>
Cc: [email protected]
Subject: Re: Upsert error "column reference is ambiguous"
Date: Mon, 28 Apr 2025 06:54:54 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Tim Starling <[email protected]> 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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Upsert error "column reference is ambiguous"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox