Re: Upsert error "column reference is ambiguous"
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. Also, I'm just saying, the upsert feature is fully useless to me with this name resolution policy. In the single-row case, there's no need for EXCLUDED at all, because the client knows everything about the excluded row. Recall my example: INSERT INTO t VALUES (1,1) ON CONFLICT (k) DO UPDATE SET v=v+1; If I meant SET v=EXCLUDED.v+1 I would have just written v=2. The default policy (in other DBMSes) follows by analogy from the single-row case. -- Tim Starling
Upsert error "column reference is ambiguous"
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
Re: Upsert error "column reference is ambiguous"
On 28/4/25 23:54, Tom Lane wrote:
AFAIK, "ON CONFLICT" is a Postgres-ism. Exactly which constructs
in exactly which other databases are you citing as precedent?
There's a list here:
<https://wiki.postgresql.org/wiki/UPSERT#UPSERT_as_implemented_in_practice>
Since that page was written in 2014, SQLite added upsert support,
consciously following PG's syntax, except that unqualified names
resolve to target rows.
My code would be like
function upsert( $table, $names, $values, $key, $set ) {
if ( $this->type === 'mysql' ) {
$conflict = 'ON DUPLICATE KEY UPDATE';
} else {
$conflict = "ON CONFLICT ($key) DO UPDATE SET";
}
return $this->query( "INSERT INTO $table ($names) " .
"VALUES ($values) $conflict $set" );
}
The parameters are a little bit more structured than that, but that
gives you the idea.
MediaWiki has supported MySQL's ON DUPLICATE KEY UPDATE since 2013,
and we've always had the conflict target parameter $key since then as
a helper for emulation. So it's trivial to produce either MySQL and
SQLite syntax.
-- Tim Starling
Re: Upsert error "column reference is ambiguous"
On 28/4/25 23:30, Peter Geoghegan wrote: 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? No, primarily because MySQL does not support such an alias. -- Tim Starling
Re: Upsert error "column reference is ambiguous"
On 29/4/25 16:36, Laurenz Albe wrote:
On Tue, 2025-04-29 at 08:36 +1000, Tim Starling wrote:
My code would be like
function upsert( $table, $names, $values, $key, $set ) {
if ( $this->type === 'mysql' ) {
$conflict = 'ON DUPLICATE KEY UPDATE';
} else {
$conflict = "ON CONFLICT ($key) DO UPDATE SET";
}
return $this->query( "INSERT INTO $table ($names) " .
"VALUES ($values) $conflict $set" );
}
The parameters are a little bit more structured than that, but that
gives you the idea.
Another litle "if" to cater for PostgreSQL's "EXCLUDED." would be
such a big problem?
I don't understand what you mean. EXCLUDED is not needed. "$table."
needs to be prefixed to every column reference in the string $set. How
do you find the column references amongst the string literals,
function calls, etc.? You would need to parse the expression.
This is a public interface and there may be callers in code that I
don't have access to.
Part of the reason for wanting to replace the existing emulation with
a native upsert is to simplify the code. Parsing the expression is
definitely not a simplification.
-- Tim Starling
