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.kla...@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-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.kla...@aklaver.com > > > >