public inbox for [email protected]help / color / mirror / Atom feed
Upsert error "column reference is ambiguous" 5+ messages / 4 participants [nested] [flat]
* Upsert error "column reference is ambiguous" @ 2025-04-28 04:55 Tim Starling <[email protected]> 0 siblings, 3 replies; 5+ messages in thread From: Tim Starling @ 2025-04-28 04:55 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Upsert error "column reference is ambiguous" @ 2025-04-28 06:15 David G. Johnston <[email protected]> parent: Tim Starling <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: David G. Johnston @ 2025-04-28 06:15 UTC (permalink / raw) To: Tim Starling <[email protected]>; +Cc: [email protected] <[email protected]> On Sunday, April 27, 2025, Tim Starling <[email protected]> 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Upsert error "column reference is ambiguous" @ 2025-04-28 10:54 Tom Lane <[email protected]> parent: Tim Starling <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-04-28 10:54 UTC (permalink / raw) To: Tim Starling <[email protected]>; +Cc: [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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Upsert error "column reference is ambiguous" @ 2025-04-28 13:30 Peter Geoghegan <[email protected]> parent: Tim Starling <[email protected]> 2 siblings, 1 reply; 5+ messages in thread From: Peter Geoghegan @ 2025-04-28 13:30 UTC (permalink / raw) To: Tim Starling <[email protected]>; +Cc: [email protected] On Mon, Apr 28, 2025 at 12:56 AM Tim Starling <[email protected]> wrote: > 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. 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Upsert error "column reference is ambiguous" @ 2025-04-28 22:48 Tim Starling <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tim Starling @ 2025-04-28 22:48 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-28 22:48 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-28 04:55 Upsert error "column reference is ambiguous" Tim Starling <[email protected]> 2025-04-28 06:15 ` David G. Johnston <[email protected]> 2025-04-28 10:54 ` Tom Lane <[email protected]> 2025-04-28 13:30 ` Peter Geoghegan <[email protected]> 2025-04-28 22:48 ` Tim Starling <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox