Postgres Upsert On Conflict Where Clause


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;

Pin It on Pinterest

Share This