PostgreSQL：用INSERT ON CONFLICT处理源表已不存在的数据

你当前的CASE语句确实无法触发TRUE的情况——因为DO UPDATE分支只有当源表中存在与目标表匹配的hostname时才会执行，此时excluded.hostname是来自源表的有效值，不可能为null，所以case永远返回false。更关键的是，这条语句根本没处理目标表中存在但源表已删除的记录，这才是你需要设置demise_status=TRUE的核心场景。

解决这个需求需要分两步操作：

1. 同步源表存在的记录（新增/更新，标记为未消亡） #

先通过INSERT...ON CONFLICT同步源表的现有数据，同时将这些记录的demise_status设为false：

insert into target (hostname, shared_owners, shared_owners_emails, demise_status)
select distinct hostname, shared_owners, shared_owners_emails, false
from source
where shared_owners is not null and shared_owners_emails is not null
on conflict (hostname)
do update set
    shared_owners = excluded.shared_owners,
    shared_owners_emails = excluded.shared_owners_emails,
    demise_status = false;

2. 标记源表已删除的记录为消亡 #

再通过UPDATE语句，把目标表中不存在于源表的记录的demise_status设为true：

update target
set demise_status = true
where not exists (
    select 1 
    from source
    where source.hostname = target.hostname
);

如果需要保证操作的原子性（避免出现中间状态），可以把这两个语句放在一个事务里：

begin;
-- 执行第一步INSERT语句
insert into target (hostname, shared_owners, shared_owners_emails, demise_status)
select distinct hostname, shared_owners, shared_owners_emails, false
from source
where shared_owners is not null and shared_owners_emails is not null
on conflict (hostname)
do update set
    shared_owners = excluded.shared_owners,
    shared_owners_emails = excluded.shared_owners_emails,
    demise_status = false;

-- 执行第二步UPDATE语句
update target
set demise_status = true
where not exists (
    select 1 
    from source
    where source.hostname = target.hostname
);
commit;

内容的提问来源于stack exchange，提问作者moth