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

The following bug has been logged on the website:

Bug reference:      16706
Logged by:          zhongxuchen
Email address:      zhongxuchen@gmail.com
PostgreSQL version: 12.3
Operating system:   centos8
Description:

1、mysql/postgresql saveOrUpdate或saveOrUpdateAll操作时的on conflict () do update
当某个字段在数据库中设置为not null，SQL 错误 [23502]: ERROR: null value in column "name"
violates not-null constraint

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);

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).

so this seems to have been fixed already.

            regards, tom lane

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

Vik Fearing <vik@postgresfriends.org> writes:
> On 11/9/20 4:16 PM, Tom Lane wrote:
>> I get
>> ERROR:  null value in column "name" violates not-null constraint
>> DETAIL:  Failing row contains (4, null, 6).

> That's the complaint.

Oh ... it wasn't expressed very well then.  I thought the OP was
reporting that the not-null constraint wasn't being enforced,
which surely *would* be a bug.

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

            regards, tom lane

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