Postgres 15+ merge (with delete) example

pg-merge-with-deletes.sql

create schema if not exists sample;
set search_path = sample;

create table if not exists sample_src
(
    pk  int primary key,
    txt varchar(255)
);

create table if not exists sample_dest
(
    pk  int primary key,
    txt varchar(255)
);

-- FIRST BATCH
truncate sample_src;
insert into sample_src (pk, txt)
values (1, '1.1'),
       (2, '2.1'),
       (3, '3.1');

-- RUN MERGE / UPSERT TEST HERE

-- SECOND BATCH, delete the 2nd row, change the 3rd row only
-- (though the 1st row likely will be updated but not changed)
truncate sample_src;
insert into sample_src (pk, txt)
values (1, '1.1'),
       (3, '3.2');

-- RUN MERGE / UPSERT TEST HERE

--- =================== MERGE (simple)
merge into sample_dest d
using sample_src s
on s.pk = d.pk
when matched then
    update
    set txt = s.txt
when not matched then
    insert (pk, txt)
    values (s.pk, s.txt);

select * from sample_dest;

-- FIRST BATCH ...
-- +--+---+
-- |pk|txt|
-- +--+---+
-- |1 |1.1|
-- |2 |2.1|
-- |3 |3.1|
-- +--+---+

-- SECOND BATCH ... this isn't yet deleting, but we can see the UPDATE part working
-- +--+---+
-- |pk|txt|
-- +--+---+
-- |2 |2.1|
-- |1 |1.1|
-- |3 |3.2|
-- +--+---+


--- =================== MERGE (complex) =================== --
-- reference: https://stackoverflow.com/a/77111376/403414
-- Per the referenced example, the key is to ensure we compare the src/dest to find rows that should be deleted...
-- then we can handle those deletes in the merge by adding a 3rd `when` block using that generated column.
merge into sample_dest d
using (select coalesce(src.pk, dst.pk) as pk,
              (src.pk is null)         as do_delete,
              src.txt                  as txt
       from sample_dest as dst
                full outer join sample_src as src
                                on src.pk = dst.pk) as s
on s.pk = d.pk
when matched and s.do_delete then
    delete
when matched then
    update
    set txt = s.txt
when not matched then
    insert (pk, txt)
    values (s.pk, s.txt);


select * from sample_dest;

-- FIRST BATCH ...
-- +--+---+
-- |pk|txt|
-- +--+---+
-- |1 |1.1|
-- |2 |2.1|
-- |3 |3.1|
-- +--+---+

-- SECOND BATCH ...
-- +--+---+
-- |pk|txt|
-- +--+---+
-- |1 |1.1|
-- |3 |3.2|
-- +--+---+


-- working example of the sub-select, this is important to understand in
-- isolation as it's performance and logic are key to the MERGE flow
select coalesce(src.pk, dst.pk) as pk,
       (src.pk is null)         as do_delete,
       src.txt
from sample_dest as dst
         full outer join sample_src as src
                         on src.pk = dst.pk;