pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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