pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedUPSERT in Postgres
4+ messages / 4 participants
[nested] [flat]
* UPSERT in Postgres
@ 2023-04-06 06:21 Louis Tian <louis.tian@aquamonix.com.au>
0 siblings, 2 replies; 4+ messages in thread
From: Louis Tian @ 2023-04-06 06:21 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
This is a question/feature request.
Virtually all references on the internet today suggests one can "upsert" in PostgreSQL using the "insert ... on conflict do update ..." statement.
But this is not complete true.
The PostgreSQL's own wiki page (https://wiki.postgresql.org/wiki/UPSERT#.22UPSERT.22_definition) defines UPSERT as
"UPSERT" is a DBMS feature that allows a DML statement's author to atomically either insert a row, or on the basis of the row already existing, UPDATE that existing row instead,
I believe this definition is correct and consistent with defintion elsewhere (https://en.wiktionary.org/wiki/upsert).
An implicit assumption behind this definition is that table must have a primary key for the upsert operation to make sense since it's the primary key that uniquely identifies a row.
let's say we have a very simple table
```
create table person (
id int primary key,
name text not null,
is_active boolean
)
```
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
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPSERT in Postgres
@ 2023-04-06 20:58 Peter Geoghegan <pg@bowt.ie>
parent: Louis Tian <louis.tian@aquamonix.com.au>
1 sibling, 0 replies; 4+ messages in thread
From: Peter Geoghegan @ 2023-04-06 20:58 UTC (permalink / raw)
To: Louis Tian <louis.tian@aquamonix.com.au>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Thu, Apr 6, 2023 at 1:21 PM Louis Tian <louis.tian@aquamonix.com.au> wrote:
> An implicit assumption behind this definition is that table must have a primary key for the upsert operation to make sense since it's the primary key that uniquely identifies a row.
It could just be a unique index or a unique constraint. So you can
upsert on any individual unique constraint/index, or the primary key.
Of course there might be several on a given table, but you can only
use one as the "conflict arbiter" per statement.
> 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
>
> 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.
It sort of has to work that way, though. In general your example might
*not* fail, due to a row-level before trigger in the insert path.
Why doesn't your proposed upsert syntax have the same problem? I mean,
how could it not? I guess it doesn't if you assume that it'll never
take the insert path with your not NULL constraint example? But if you
know that for sure, why not just use a regular update statement? On
the other hand, if you're not sure if the insert path can be taken,
then why is it actually helpful to not just throw an error at the
earliest opportunity?
Surely upsert means "update or insert", so why wouldn't the user expect
to see an error like this, independent of the specifics of the row in question?
Isn't the user tacitly saying "I don't specifically know if the update or insert
path will be taken in respect of any given row" by using ON CONFLICT
DO UPDATE in the first place?
> 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.
I don't know what you mean by that. "Valid"?
> 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.
The MERGE command has various race conditions that are particularly
relevant to UPSERT type use cases. See the wiki page you referenced
for a huge amount of information on this.
> *Feature Request*
> Given that UPSERT is an *idempotent* operator it is extremely useful.
In general UPSERT (or any definition of it that I can think of) does
not imply idempotency.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPSERT in Postgres
@ 2023-04-06 21:00 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Louis Tian <louis.tian@aquamonix.com.au>
1 sibling, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2023-04-06 21:00 UTC (permalink / raw)
To: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: UPSERT in Postgres
@ 2023-04-06 21:42 Benedict Holland <benedict.m.holland@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 4+ messages in thread
From: Benedict Holland @ 2023-04-06 21:42 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general <pgsql-general@lists.postgresql.org>
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.klaver@aklaver.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-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
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-04-06 21:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-06 06:21 UPSERT in Postgres Louis Tian <louis.tian@aquamonix.com.au>
2023-04-06 20:58 ` Peter Geoghegan <pg@bowt.ie>
2023-04-06 21:00 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-06 21:42 ` Benedict Holland <benedict.m.holland@gmail.com>
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