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:

<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