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

On 11/9/20 4:16 PM, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> example:
>> CREATE TABLE TEST1 (
>>     id varchar(100) NOT NULL,
>>     name varchar(100) NOT NULL,
>>     status varchar(100) NOT NULL,
>>     CONSTRAINT test1_pkey PRIMARY KEY (id)
>> )
> 
>> insert into TEST1 (ID, NAME,STATUS ) values('4','test','6') ;
>> insert into TEST1 as t1 (ID, NAME,STATUS ) values('4',null,'6') on conflict
>> (id) do update set NAME=COALESCE(excluded.NAME,t1.NAME),
>> STATUS=COALESCE(excluded.STATUS,t1.STATUS);
> 
> I get
> 
> ERROR:  null value in column "name" violates not-null constraint
> DETAIL:  Failing row contains (4, null, 6).

That's the complaint.

> so this seems to have been fixed already.

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.
-- 
Vik Fearing