Re: BUG #14343: UPSERT (ON CONFLICT) doesn't check ON CONFLICT constraint first - Mailing list pgsql-bugs

reyes.r.ponce@gmail.com writes:
> ERROR:  null value in column "col1" violates not-null constraint
> DETAIL:  Failing row contains (1, null, 5, 2016-09-27 17:32:51.054896+00,
> pl_mstr_usr, 2016-09-27 17:32:51.054896+00, pl_mstr_usr).
> CONTEXT:  SQL statement "INSERT INTO public.MyTable(
>             MY_ID, COL1, COL2, CRETN_TS, CRETN_USER_ID, UPDT_TS,
> UPDT_USER_ID)
>     VALUES ($1, $2, $3, NOW(), current_user, NOW(), current_user)
>     ON CONFLICT(MY_ID)
>     DO UPDATE SET UPDT_TS = NOW(), UPDT_USER_ID = current_user, COL2 = $3"
> PL/pgSQL function upsert_mytable(integer,integer,integer) line 46 at
> EXECUTE

This test case seems rather overcomplicated, but AFAICS you are
complaining because the NOT NULL constraint is checked before uniqueness
is checked.  Sorry, that is not a bug, that is by design.

            regards, tom lane