Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint

Hi,

On 2020-11-09 15:15:01 -0500, Tom Lane wrote:
> Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> > I think the issue is the not null constraint is evaluated before the ON
> > CONFLICT and so there is no chance to "heal" the row by coalescing the
> > old and new values.
>
> Ah. Well, ON CONFLICT is for resolving duplicate-key errors;
> it's not a get-out-of-jail-free card for every sort of error.
>
> I think the right way to handle this example would be with a
> before-update trigger, which IIRC can modify the row before
> we apply any table constraint checks.

Sounds like the to-be-upserted row could never be inserted, but only
updated. In which case an UPDATE sounds more appropriate than an INSERT
... ON CONFLICT SET ... - am I missing something?

Greetings,

Andres Freund