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

Tom Lane:
>> 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.

I don't think that would work, because the not null constraint is
checked before the INSERT, not the UPDATE. But a before-insert trigger
would not be able replace NULL with the "old" value, because there is
none (yet).

The whole approach to this query seems a bit strange to me: When you
have the NOT NULL constraint and insert a NULL value like this, you must
expect the query to fail for a regular INSERT (when the on conflict does
not trigger) anyway. So the only way to succeed with this query is by
going through the ON CONFLICT. But if you know before, that you will
only be successful with an UPDATE, why not make the whole query an
UPDATE from the start?

Best

Wolfgang