Re: [EXTERNAL]: Re: UPSERT in Postgres

> On 7 Apr 2023, at 2:49, Louis Tian <louis(dot)tian(at)aquamonix(dot)com(dot)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.