public inbox for [email protected]  

help / color / mirror / Atom feed

From: Christophe Pettus <[email protected]>
To: Tim Starling <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Upsert error "column reference is ambiguous"
Date: Mon, 28 Apr 2025 15:58:42 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>



> On Apr 28, 2025, at 15:36, Tim Starling <[email protected]> wrote:
> 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" );

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=$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.

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], [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