pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint
7+ messages / 6 participants
[nested] [flat]

* BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint
@ 2020-11-09 09:13 PG Bug reporting form <noreply@postgresql.org>
  2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: PG Bug reporting form @ 2020-11-09 09:13 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: zhongxuchen@gmail.com

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
  2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
@ 2020-11-09 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-09 19:45   ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Vik Fearing <vik@postgresfriends.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2020-11-09 15:16 UTC (permalink / raw)
  To: zhongxuchen@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
  2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
  2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-11-09 19:45   ` Vik Fearing <vik@postgresfriends.org>
  2020-11-09 20:15     ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Vik Fearing @ 2020-11-09 19:45 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; zhongxuchen@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
  2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
  2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-09 19:45   ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Vik Fearing <vik@postgresfriends.org>
@ 2020-11-09 20:15     ` Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-09 20:29       ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Wolfgang Walther <walther@technowledgy.de>
  2020-11-10 04:25       ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Andres Freund <andres@anarazel.de>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2020-11-09 20:15 UTC (permalink / raw)
  To: Vik Fearing <vik@postgresfriends.org>; +Cc: zhongxuchen@gmail.com; pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
  2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
  2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-09 19:45   ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Vik Fearing <vik@postgresfriends.org>
  2020-11-09 20:15     ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-11-09 20:29       ` Wolfgang Walther <walther@technowledgy.de>
  1 sibling, 0 replies; 7+ messages in thread

From: Wolfgang Walther @ 2020-11-09 20:29 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
  2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
  2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-09 19:45   ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Vik Fearing <vik@postgresfriends.org>
  2020-11-09 20:15     ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-11-10 04:25       ` Andres Freund <andres@anarazel.de>
  2020-11-10 10:43         ` Re: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint zhongxuchen@gmail.com <zhongxuchen@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Andres Freund @ 2020-11-10 04:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Vik Fearing <vik@postgresfriends.org>; zhongxuchen@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On 2020-11-09 15:15:01 -0500, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
  2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
  2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-09 19:45   ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Vik Fearing <vik@postgresfriends.org>
  2020-11-09 20:15     ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
  2020-11-10 04:25       ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Andres Freund <andres@anarazel.de>
@ 2020-11-10 10:43         ` zhongxuchen@gmail.com <zhongxuchen@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: zhongxuchen@gmail.com @ 2020-11-10 10:43 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Vik Fearing <vik@postgresfriends.org>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>

HI!
     Will this problem provide support?
     Personally, if this problem is not solved, it means that "on conflict () do update" is meaningless!
     
     非常希望postgresql可以解决此问题,让postgresql更加完美!也让个人的开源orm项目更加完美(https://github.com/sagframe/sagacity-sqltoy)
     非常感谢各位的辛苦和付出!
     



zhongxuchen@gmail.com
 
From: Andres Freund
Date: 2020-11-10 12:25
To: Tom Lane
CC: Vik Fearing; zhongxuchen; pgsql-bugs
Subject: 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@postgresfriends.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


^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2020-11-10 10:43 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-09 09:13 BUG #16706: insert into on conflict(pk) do update  error violates not-null constraint PG Bug reporting form <noreply@postgresql.org>
2020-11-09 15:16 ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
2020-11-09 19:45   ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Vik Fearing <vik@postgresfriends.org>
2020-11-09 20:15     ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Tom Lane <tgl@sss.pgh.pa.us>
2020-11-09 20:29       ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Wolfgang Walther <walther@technowledgy.de>
2020-11-10 04:25       ` Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint Andres Freund <andres@anarazel.de>
2020-11-10 10:43         ` Re: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint zhongxuchen@gmail.com <zhongxuchen@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox