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

From zhongxuchen@gmail.com
Subject Re: Re: BUG #16706: insert into on conflict(pk) do update error violates not-null constraint
Date
Msg-id 2020111018432320226511@gmail.com
Whole thread Raw
List pgsql-bugs
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
 
Date: 2020-11-10 12:25
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

pgsql-bugs by date:

Previous
From: Kurt Roeckx
Date:
Subject: Re: BUG #16707: Memory leak
Next
From: Kurt Roeckx
Date:
Subject: Re: BUG #16707: Memory leak