Re: [EXTERNAL]: Re: UPSERT in Postgres

On 4/6/23 18:27, Louis Tian wrote:
> Hi Adrian,
> 
> Thank you. I think this is a better approach than trigger-based 
> solution, at least for my taste.
> That being said, it does require some logic to push to the client side 
> (figuring out which required column value is missing and set it value to 
> the existing one via reference of the table name).
> Still wish there would be UPSERT statement that can handle this and make 
> dev experience better.

It does what is advertised on the tin:

https://www.postgresql.org/docs/current/sql-insert.html

The optional ON CONFLICT clause specifies an alternative action to 
raising a unique violation or exclusion constraint violation error

[...]

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; 
provided there is no independent error, one of those two outcomes is 
guaranteed, even under high concurrency. This is also known as UPSERT — 
“UPDATE or INSERT”.

You got caught by the '...independent error...' part. The same thing 
would have happened if you had just done:

insert into person (id, is_active) values(0, true);
ERROR:  null value in column "name" of relation "person" violates 
not-null constraint


The insert has to be valid on its own before you get to the 'alternative 
action to raising a unique violation or exclusion constraint violation 
error' part. Otherwise you are asking Postgres to override this 'insert 
into person (id, is_active)' and guess you really wanted something like:

insert into person (id, name, is_active) values(0, <existing value>, true)

I'm would not like the server making those guesses on my behalf.

> ,
> Cheers,
> Louis Tian
> ------------------------------------------------------------------------

-- 
Adrian Klaver
adrian.klaver@aklaver.com