Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres

On 4/9/23 19:16, Rob Sargent wrote:
> On 4/9/23 19:55, Louis Tian wrote:
>> Hi Alban,
>>
>> "I am not expecting an error here", by "here" I means when doing a 
>> TRUE UPSERT (an upsert current does not exist in Postgres).
>> I am NOT referring to an "Insert on conflict do update" (which despite 
>> its intention and wide acceptance is not fully equivalent to a true 
>> upsert).
>> I understand the error I am getting now is due to not null constraint 
>> given how "insert on conflict" works.
>>
>> An UPSERT checks whether a row exists, if so, it does an update, if 
>> not it does an insert. This is the literal definition.
> 
> This the part that's always eluded me: How does the client, the 
> UPSERTer, come to hold an id and not know whether or not it's already in 
> the database.

My use case is for bulk loading data into a table I know has data that 
will create a PK/Unique violation with the inserted data. It's a quick 
and dirty way to avoid queries that look for potential violations ahead 
of time, basically 'Ask forgiveness' vs 'Get permission'.

-- 
Adrian Klaver
adrian.klaver@aklaver.com