Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.1\))
Subject: Re: Upsert error "column reference is ambiguous"
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <5fe1d1c9-662e-405e-aaed-21d15bdbea06@wikimedia.org>
Date: Mon, 28 Apr 2025 15:58:42 -0700
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <D794C225-9F5D-4DF4-BE3F-39AD8C3430B1@thebuild.com>
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>
 <5fe1d1c9-662e-405e-aaed-21d15bdbea06@wikimedia.org>
To: Tim Starling <tstarling@wikimedia.org>
Archived-At: <https://www.postgresql.org/message-id/D794C225-9F5D-4DF4-BE3F-39AD8C3430B1%40thebuild.com>
Precedence: bulk


> On Apr 28, 2025, at 15:36, Tim Starling <tstarling@wikimedia.org> =
wrote:
> function upsert( $table, $names, $values, $key, $set ) {
>    if ( $this->type =3D=3D=3D 'mysql' ) {
>        $conflict =3D 'ON DUPLICATE KEY UPDATE';
>    } else {
>        $conflict =3D "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=3D$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.