Merge Command

#postgres #merge #deltas

Have you come across a situation where you have a very huge table Eg: 100 million rows and you need to update the deltas ?

Y'all probably re-create the whole table to cater such a situation, well now you do not have to fret about it, we have merge command released in Postgres 15.

Example:

create table demo (id serial primary key, val int);

inset into demo (val) select x * 10 from generate_series(1, 10) as x;

create table demo1 as (select id, id * 100 as val
 from 
generate_series(1, 10, 2) as id) ;

select * from demo order by id;
select * from demo1;

merge
into demo as d
       using demo1 as d1
on
   d.id = d1.id
   when matched then
update
set
   val = d1.val
   when not matched then
insert 
   (val)
values (d1.val) ;

select * from demo order by id;

Viola ! Easy peasy now !