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
