Re: Upsert error "column reference is ambiguous" - Mailing list pgsql-general

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