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