A basic example of a Postgres Upsert using “on conflict do update” with a where clause.
Postgres Upsert ON CONFLICT with WHERE clause
create table z_test(
f1 text PRIMARY KEY,
f2 text,
f3 text,
f4 text
);
insert into z_test
(f1,f2,f3,f4)
values('1','2','3','4');
commit;
select * from z_test;
--- INSERT NEW ROW
insert into z_test as z (f1, f2, f3, f4) values ('ww', 'x','y','z')
on conflict(f1) do update
set f2 = 'asd'
where z.f3 <> 'yy';
commit;
select * from z_test;
--- UPDATE ROW
insert into z_test as z (f1, f2, f3, f4) values ('ww', 'x','y','z')
on conflict(f1) do update
set f2 = 'asd'
where z.f3 <> 'yy';
commit;
select * from z_test;
--- DO NOTHING
insert into z_test as z (f1, f2, f3, f4) values ('ww', 'x','y','z')
on conflict(f1) do update
set f2 = 'qwert'
where z.f3 <> 'y';
commit;
select * from z_test;