Re: [EXTERNAL]: Re: UPSERT in Postgres

[Alban Hertroys]

> On 7 Apr 2023, at 2:49, Louis Tian <louis.t...@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 in a 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 primary key). 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 your statement 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’re specifying 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.