Re: UPSERT in Postgres

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
seemsto 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
constraintis 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.klaver@aklaver.com