Subject: Upsert error "column reference is ambiguous"

From: Tim Starling <[email-masked]>
Date: Mon, 28 Apr 2025 14:55:58 +1000

Regarding upsert syntax.

psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1), server 14.13 (Ubuntu 
14.13-0ubuntu0.22.04.1))
=> CREATE TABLE t (k INTEGER, v INTEGER);
=> CREATE UNIQUE INDEX t_k ON t (k);
=> INSERT INTO t VALUES (1,1);
INSERT 0 1
=> 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.

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.

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.

I'm a maintainer of MediaWiki. Some kind person contributed PostgreSQL 
support many years ago so now I am required to maintain it in 
perpetuity. The work seems out of proportion to the benefit, but 
that's the industry I guess. A handful of users benefit, such as 
wiki.postgresql.org.

Our application has an upsert method which takes the assignment 
"v=v+1" as a string. It is feasible to split it on the equals sign 
into the destination field and expression components, but it is not 
feasible to parse the expression or to require callers to supply an 
AST tree for the expressions they give us. It is not feasible to 
require callers to prefix all field names with the table name.

We currently emulate upsert on PostgreSQL using several awkward and 
inefficient queries. It would be nice to be able to use PostgreSQL's 
native upsert feature. But unless someone here has an idea for a 
workaround, I think this field name resolution policy is a total 
blocker. We can implement upsert on MySQL and SQLite but on PostgreSQL 
it will remain emulated.

-- Tim Starling

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 27 Apr 2025 23:15:23 -0700

On Sunday, April 27, 2025, Tim Starling <[email-masked]> wrote:

> thus allowing it to DWIM.

We intentionally choose (or, in any case have established) a SWYM approach
here.

Personally I’d be fine with the reduced helpfulness in trying to prevent
buggy queries in the interest of being more conforming with the broader
world.  I am curious as to whether we are in strict adherence to the SQL
Standard on this point though.  Makes deviation a bit tougher to justify.

It does seem that project policies would prevent back-patching such a
change.

David J.

---

From: Tom Lane <[email-masked]>
Date: Mon, 28 Apr 2025 06:54:54 -0400

> Please convince me that this is not a bug.

It's not a bug.

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

> 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

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 28 Apr 2025 09:30:02 -0400

> require callers to prefix all field names with the table name.

You can use an alias for the target table name. Is it feasible to
require callers to prefix all field names with a generic table name
alias?

-- 
Peter Geoghegan

---

From: Tim Starling <[email-masked]>
Date: Tue, 29 Apr 2025 08:48:03 +1000

> require callers to prefix all field names with a generic table name

No, primarily because MySQL does not support such an alias.

-- Tim Starling