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