RE: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres

Hi Alban,

"I am not expecting an error here", by "here" I means when doing a TRUE UPSERT (an upsert current does not exist in Postgres).
I am NOT referring to an "Insert on conflict do update" (which despite its intention and wide acceptance is not fully equivalent to a true upsert).
I understand the error I am getting now is due to not null constraint given how "insert on conflict" works.

An UPSERT checks whether a row exists, if so, it does an update, if not it does an insert. This is the literal definition.
An UPSERT is NOT defined as try do an INSERT first, if violate uniqueness constraint, do update. This is what on conflict do update is doing.
We cannot define UPSERT with what "ON CONFLICT DO UPDATE" is doing. That is a logical fallacy.
UPSERT is a higher-level abstract concept. ON CONFLICT DO UPDATE is an implementation of UPSERT. not the other way around.

When doing a true UPSERT, if a row already exists, there is no need provide an (not null) column, since I am doing an update.

With `UPSERT person (id, is_active)` VALUES (0, true). Is it necessary to provide the not null "name" column here logically?
Not really, I already specified the row with the `id` column, then I specify the column I want to update `is_active`.
* the id does exist; the update can be executed without any issue or assumptions
* the id does not exit; then I am expecting a violate not null constraint.

On contrast, with `INSERT person (id, is_active) value (0, true) ON CONFLICT DO UPDATE set is_active=true`,
this statement will always fail regardless of whether the id exists or not.
So the behavior of ON CONFLICT DO UPDATE differs from a UPSERT with the presence of a not null column.
Hence why ON CONFLICT DO UPDATE is not complete equivalent to a TRUE upsert.

Cheers,
Louis Tian

-----Original Message-----
From: Alban Hertroys <haramrae(at)gmail(dot)com>
Sent: Sunday, April 9, 2023 7:26 PM
To: Louis Tian <louis(dot)tian(at)aquamonix(dot)com(dot)au>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>; pgsql-general(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres

CAUTION: This email originated from outside of Envirada. Do not click links or open attachments unless you recognize the sender and know the content is safe.

> 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.

--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.https://www.mailguard.com.au/mg
Click here to report this message as spam:
https://console.mailguard.com.au/ras/25ayougcIv/4FIia1zrtWT2nnuHlesEOS/1.8