PostgreSQL中针对ID分布在当前/快照同构表的更新实现方法

核心问题诊断 #

你写的SQL从语法根上就无法正常执行，存在两个关键错误：

• UNION根本不能用来拼接UPDATE语句。它是专门为SELECT设计的集合操作符，作用是合并多个查询的返回结果集，PostgreSQL在语法解析阶段看到UNION后面跟UPDATE时会直接抛出语法错误，连实际执行的机会都没有，自然得不到预期的更新效果。
• 你写的SET column1=value里的value不是合法的常量值，就算修复了UNION的语法问题，实际执行时也会因为找不到名为value的字段，报“关系不存在”的错误。

适配业务场景的正确写法 #

你的核心需求是：不管目标ID存储在test表还是test_snapshot表，只要在某张表匹配到对应column4的ID值，就更新对应行的三个固定字段。这种场景根本不需要复杂拼接，直接写两段独立的UPDATE即可——PostgreSQL的UPDATE如果扫描完全表发现没有匹配行，会直接返回UPDATE 0，不会产生多余的性能开销。

最推荐的轻量实现

-- 更新当前业务表的匹配记录
UPDATE test
SET column1 = '你要写入的固定值',
    column2 = '你要写入的固定值',
    column3 = '你要写入的固定值'
WHERE column4 IN (1,2,3);

-- 更新历史快照表的匹配记录
UPDATE test_snapshot
SET column1 = '你要写入的固定值',
    column2 = '你要写入的固定值',
    column3 = '你要写入的固定值'
WHERE column4 IN (1,2,3);

需要保证更新原子性的实现

如果担心出现只更新了一张表的中间状态，可以把两段UPDATE放到同一个事务块里执行，要么两张表的修改全部生效，要么全部回滚：

BEGIN;
UPDATE test
SET column1 = '你要写入的固定值',
    column2 = '你要写入的固定值',
    column3 = '你要写入的固定值'
WHERE column4 IN (1,2,3);

UPDATE test_snapshot
SET column1 = '你要写入的固定值',
    column2 = '你要写入的固定值',
    column3 = '你要写入的固定值'
WHERE column4 IN (1,2,3);
-- 确认执行结果符合预期再提交，出现问题直接执行ROLLBACK回滚
COMMIT;

补充提示：如果你之前误以为用UNION拼接可以减少客户端和数据库的交互次数，上面的事务块写法完全可以达到同样的效果，且不存在任何语法兼容问题。

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