public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tim Starling <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Upsert error "column reference is ambiguous"
Date: Tue, 29 Apr 2025 08:36:18 +1000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Upsert error "column reference is ambiguous"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox