Message-ID: <5fe1d1c9-662e-405e-aaed-21d15bdbea06@wikimedia.org>
Date: Tue, 29 Apr 2025 08:36:18 +1000
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Upsert error "column reference is ambiguous"
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
References: <a31a76b6-b176-47fe-8778-9dfece231341@wikimedia.org>
 <697017.1745837694@sss.pgh.pa.us>
 <80f95342-cc09-4236-a2d7-68538fbfc41b@wikimedia.org>
 <774865.1745848449@sss.pgh.pa.us>
From: Tim Starling <tstarling@wikimedia.org>
In-Reply-To: <774865.1745848449@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/5fe1d1c9-662e-405e-aaed-21d15bdbea06%40wikimedia.org>
Precedence: bulk

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