PostgreSQL：填充多外键ID时如何避免覆盖已存在值

你遇到的问题是第二次UPDATE语句会覆盖第一次填充的ID值，只需通过添加条件限制或合并更新逻辑即可解决，以下是两种可行方案：

方案1：限制第二次UPDATE仅处理未填充的记录 #

修改第二条UPDATE语句，增加WHERE条件，只更新mult_survey_id为空的行，这样就不会覆盖已从panama_survey填充的ID：

-- 先填充panama_survey关联的ID
update ves v 
set mult_survey_id = 
    (select ps.panama_survey_id
    from panama_survey ps
    where (ps.site, ps."date", ps.survey_time, ps.detection_type) = (v.site, v."date", v.survey_time, v.detection_type));
    
-- 仅填充未匹配到panama_survey的记录
update ves v 
set mult_survey_id =
    (select pes.penn_survey_id
    from penn_survey pes
    where (pes.site, pes."date", pes.survey_time, pes.detection_type) = (v.site, v."date", v.survey_time, v.detection_type))
where v.mult_survey_id IS NULL; -- 新增条件：只处理未填充的记录

方案2：合并为单条UPDATE语句（更高效） #

使用COALESCE函数优先匹配panama_survey的ID，若匹配不到再取penn_survey的ID，一次更新完成所有填充：

update ves v
set mult_survey_id = COALESCE(
    -- 优先取panama_survey的ID
    (select ps.panama_survey_id
     from panama_survey ps
     where (ps.site, ps."date", ps.survey_time, ps.detection_type) = (v.site, v."date", v.survey_time, v.detection_type)),
    -- 若panama_survey无匹配，取penn_survey的ID
    (select pes.penn_survey_id
     from penn_survey pes
     where (pes.site, pes."date", pes.survey_time, pes.detection_type) = (v.site, v."date", v.survey_time, v.detection_type))
);

两种方案都能确保已填充的panama_survey ID不会被覆盖，方案2减少了一次全表扫描，效率更高。

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