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(dot)klaver(at)aklaver(dot)com