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(dot)klaver(at)aklaver(dot)com