Re: [EXTERNAL]: Re: UPSERT in Postgres

> On 7 Apr 2023, at 2:49, Louis Tian <louis.tian@aquamonix.com.au> wrote:

(…)

> I am not expecting an error here. The problem is with no conflict it always go down the insert path first and results
ina not null constraint error. 
> While I am expecting the insert is never executed in the first place when that row already exist (as identified by it
primarykey). So the update execute without error. 
> I hope the pesudo code above is enough to clarify the difference?

Your assumption on what the problem is, is not correct. The problem is not with the conflict resolution, it is with
yourstatement violating a not null constraint. 

It doesn’t matter whether you insert first or update first, either operation is going to violate that constraint.
You’respecifying a NULL value for a column that doesn’t accept that because it has a NOT NULL constraint. That is your
problem.

Alban Hertroys
--
There is always an exception to always.