Message-ID: <ac2caa5c-9f18-49db-8f6a-91cd1131f36e@wikimedia.org>
Date: Tue, 29 Apr 2025 21:07:47 +1000
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Upsert error "column reference is ambiguous"
Content-Language: en-US
To: Laurenz Albe <laurenz.albe@cybertec.at>, 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>
 <5fe1d1c9-662e-405e-aaed-21d15bdbea06@wikimedia.org>
 <10ff0fbe7f4d711300951431ad28254aa5bf2dc4.camel@cybertec.at>
From: Tim Starling <tstarling@wikimedia.org>
In-Reply-To: <10ff0fbe7f4d711300951431ad28254aa5bf2dc4.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/ac2caa5c-9f18-49db-8f6a-91cd1131f36e%40wikimedia.org>
Precedence: bulk

On 29/4/25 16:36, Laurenz Albe wrote:
> On Tue, 2025-04-29 at 08:36 +1000, Tim Starling wrote:
>> 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.
> 
> Another litle "if" to cater for PostgreSQL's "EXCLUDED." would be
> such a big problem?

I don't understand what you mean. EXCLUDED is not needed. "$table." 
needs to be prefixed to every column reference in the string $set. How 
do you find the column references amongst the string literals, 
function calls, etc.? You would need to parse the expression.

This is a public interface and there may be callers in code that I 
don't have access to.

Part of the reason for wanting to replace the existing emulation with 
a native upsert is to simplify the code. Parsing the expression is 
definitely not a simplification.

-- Tim Starling