Re: Upsert error "column reference is ambiguous"

> On Apr 28, 2025, at 15:36, Tim Starling <tstarling(at)wikimedia(dot)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.