pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Louis Tian <louis.tian@aquamonix.com.au>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: [EXTERNAL]: Re: UPSERT in Postgres
Date: Fri, 7 Apr 2023 01:27:59 +0000
Message-ID: <SY4P282MB1052E561074B2D7CE7F6D286A6969@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <66a94b55-04ee-8185-73a9-681b968fc7b8@aklaver.com>
References: <SY4P282MB1052A434D91AC0656589B6AEA6919@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
	<66a94b55-04ee-8185-73a9-681b968fc7b8@aklaver.com>

Hi Adrian,

Thank you. I think this is a better approach than trigger-based solution, at least for my taste.
That being said, it does require some logic to push to the client side (figuring out which required column value is missing and set it value to the existing one via reference of the table name).
Still wish there would be UPSERT statement that can handle this and make dev experience better.

Cheers,
Louis Tian
________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, April 7, 2023 7:00 AM
To: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: [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 4/5/23 23:21, Louis Tian wrote:
> This is a question/feature request.
>

> Given the definition of upsert, I'd expect an upsert command to do the following.
> - `upsert into person (id, name) values (0, 'foo')` to insert a new row
> - `upsert into person (id, is_active) values (0, true)` updates the is_active column for the row inserted above
>
> Naturally, since there isn't a real upsert command in PostgreSQL this won't work today.
> But can we achieve the same effect with "INSERT ... ON CONFLICT DO UPDATE" like a lot of references on the internet seems to suggest.
>
> insert into person (id, name) values (0, 'foo') on conflict ("id") do update set id=excluded.id, name=excluded.name
> insert into person (id, is_active) values (0, true) on conflict ("id") do update set id=excluded.id, is_active=excluded.is_active

insert into person (id, name, is_active) values (0, '', true) on
conflict ("id") do update set id=excluded.id, name=person.name,
is_active=excluded.is_active ;
INSERT 0 1

select * from person;
  id | name | is_active
----+------+-----------
   0 | foo  | t

>
> Unfortunately. the second statement will fail due to violation of the not null constraint on the "name" column.
> PostgreSQL will always try to insert the row into the table first. and only fallback to update when the uniqueness constraint is violated.
> Is this behavior wrong? maybe not, I think it is doing what it reads quite literally.
> That being said, I have never had a need for the ON CONFLICT DO UPDATE statement other than where I need upsert.
> But using it as "upsert" is only valid when the table is absent of any NOT NULL constraint on it's non primary key columns.
> So, if my experience/use case is typical (meaning the main purpose / use case for ON CONFLICT DO UPDATE is to support upsert) then it can be argue the current behavior is incorrect?
>
> This has been a source confusion to say at least.
> https://stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-postgres-with-a-not-null-cons...
> https://www.postgresql.org/message-id/flat/1996438.1604952901%40sss.pgh.pa.us#ace8adc1354265aca56720...
>
> The MERGE command introduced in PG15 in theory can be used to do UPSERT properly that is void of the aforementioned limitation.
> The downside is it is rather verbose.
>
> *Question*
> This there a way to do an upsert proper prior to PG15?
>
> *Feature Request*
> Given that UPSERT is an *idempotent* operator it is extremely useful.
> Would love to see an UPSERT command in PostgreSQL so one can 'upsert' properly and easily.
>
>
> Regards,
> Louis Tian
>
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
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/259w94L3yw/5Vb4QHWbRwfP3KeOMAvzU8/-2.1

view thread (9+ messages)  latest in thread

Message-ID: <SY4P282MB1052E561074B2D7CE7F6D286A6969@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
Permalink:  ../SY4P282MB1052E561074B2D7CE7F6D286A6969@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM/
Also on:    postgresql.org/message-id/SY4P282MB1052E561074B2D7CE7F6D286A6969@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: louis.tian@aquamonix.com.au, adrian.klaver@aklaver.com
  Subject: Re: [EXTERNAL]: Re: UPSERT in Postgres
  In-Reply-To: <SY4P282MB1052E561074B2D7CE7F6D286A6969@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
  References: <SY4P282MB1052A434D91AC0656589B6AEA6919@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM> <66a94b55-04ee-8185-73a9-681b968fc7b8@aklaver.com> <SY4P282MB1052E561074B2D7CE7F6D286A6969@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox