Re: [EXTERNAL]: Re: UPSERT in Postgres

On 4/6/23 18:27, Louis Tian wrote:
> 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.

Another way to make the experience easier:

alter table person alter COLUMN name set default 'cat';

  \d person
                   Table "public.person"
   Column   |  Type   | Collation | Nullable |   Default
-----------+---------+-----------+----------+-------------
  id        | integer |           | not null |
  name      | text    |           | not null | 'cat'::text
  is_active | boolean |           |          |

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


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

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


> 
> Cheers,
> Louis Tian

-- 
Adrian Klaver
adrian.klaver@aklaver.com