Re: UPSERT in Postgres

That is the answer. Postgresql can upsert easily via triggers and on
conflict.

Thanks,
Ben

On Thu, Apr 6, 2023, 5:01 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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-constraint
> >
> https://www.postgresql.org/message-id/flat/1996438.1604952901%40sss.pgh.pa.us#ace8adc1354265aca5672028faea0c0f
> >
> > 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(dot)klaver(at)aklaver(dot)com
>
>
>
>