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@xxxxxxxxxxx
