Re: Upsert error "column reference is ambiguous"

On Tuesday, April 29, 2025, Tim Starling wrote: > > > This is a public interface and there may be callers in code that I don't > have access to. > You might help your cause by sharing examples of how client code uses your driver to perform upsert that runs into this limitation. David J.

Re: Upsert error "column reference is ambiguous"

David Rowley writes: > Another thought is that you can have an UPDATE with a RETURNING > clause. An unqualified column defaults to NEW even though you could > argue it's ambiguous due to OLD (as of 80feb727c). Likely we were > forced into making it work that way through not wanting to force > ever

Re: Upsert error "column reference is ambiguous"

On Tue, 29 Apr 2025 at 01:54, Tom Lane wrote: > I do actually have some sympathy for your proposal after thinking > about it a bit more, but the argument I would use is "the behavior > of the ON CONFLICT UPDATE SET list should be as much as possible like > the behavior of an ordinary UPDATE's SET

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

Re: Upsert error "column reference is ambiguous"

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

Re: Upsert error "column reference is ambiguous"

> On Apr 28, 2025, at 15:58, Christophe Pettus wrote: > It does require knowing which of the VALUES is the key value being inserted > (pseudocode syntax above) [...] The instant after I hit send, I realized that information is available to the function by lining up the $names and $values arr

Re: Upsert error "column reference is ambiguous"

> On Apr 28, 2025, at 15:36, Tim Starling wrote: > 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->quer

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 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: Since that page was written in 201

Re: Upsert error "column reference is ambiguous"

On Monday, April 28, 2025, Tom Lane wrote: > > AFAIK, "ON CONFLICT" is a Postgres-ism. Exactly which constructs > in exactly which other databases are you citing as precedent? > I confirmed the SQLite reference from the original email. “The upsert above inserts the new vocabulary word "jovial"

Re: Upsert error "column reference is ambiguous"

Tim Starling writes: > 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.

Re: Upsert error "column reference is ambiguous"

On Mon, Apr 28, 2025 at 12:56 AM Tim Starling 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

Re: Upsert error "column reference is ambiguous"

On Mon, 2025-04-28 at 21:22 +1000, Tim Starling wrote: > 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

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 the

Re: Upsert error "column reference is ambiguous"

Tim Starling 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 >

Re: Upsert error "column reference is ambiguous"

On Sunday, April 27, 2025, Tim Starling 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 b