Home > mailing lists

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

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

From

"zhongxuchen@gmail.com"

Date:

10 November 2020, 13:43:24

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