Re: Upsert error "column reference is ambiguous"

[Christophe Pettus]

> On Apr 28, 2025, at 15:36, Tim Starling <tstarl...@wikimedia.org> 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->query( "INSERT INTO $table ($names) " .
>        "VALUES ($values) $conflict $set" );

I'll mention that you can do this without ON CONFLICT in PostgreSQL in a way 
that, while not nearly as clean as ON CONFLICT, isn't a huge hack, either:

"DO $$ BEGIN INSERT INTO $table($names) VALUES($values); EXCEPTION WHEN 
integrity_constraint_violation THEN UPDATE $table SET $set WHERE 
$key=$values[0]; END; $$ LANGUAGE plpgsql;"

It does require knowing which of the VALUES is the key value being inserted 
(pseudocode syntax above), but if that is stylized to always be the first 
value, that does not seem insurmountable.