PostgreSQL用UNION合并新旧表优先取新表数据的实现方法

原有UNION/UNION ALL写法失效原因 #

UNION ALL的逻辑是直接拼接两个结果集的所有行，不会做任何按主键去重或优先级判断，最终会出现同一ID对应新旧两条记录的问题；UNION仅会对所有字段值完全一致的行做去重，只要新旧记录任意字段存在差异（比如示例中id=002的quantity旧值为23、新值为70），就会同时保留两条同ID数据，无法满足取最新记录的需求。

可用SQL实现 #

方案1：UNION ALL+排除匹配ID（兼容性最佳，支持所有主流数据库） #

逻辑是先全量返回新表的所有最新记录，再补充返回旧表中ID未在新表出现的历史记录，完全匹配需求：

-- 取新表全量最新数据
SELECT id, customer, product, quantity FROM table01_new
UNION ALL
-- 取旧表中未被新表覆盖的历史数据
SELECT id, customer, product, quantity FROM table01_old
WHERE id NOT IN (SELECT DISTINCT id FROM table01_new);

基于你给出的测试数据，执行该语句的返回结果为：

• id=001：客户Cust001、Soap、200件（旧表历史记录）
• id=002：客户Cust002、Shampoo、70件（新表最新记录）
• id=003：客户Cust003、Ketchup、50件（新表最新记录）

该写法同时兼容新表存在旧表未覆盖的新增ID的场景，不需要额外调整逻辑。

优化提示：如果业务中ID字段可能存在NULL值，NOT IN写法可能出现匹配异常，可替换为NOT EXISTS写法，逻辑完全等价且对NULL值兼容性更好：

SELECT o.id, o.customer, o.product, o.quantity
FROM table01_old o
WHERE NOT EXISTS (SELECT 1 FROM table01_new n WHERE n.id = o.id)

方案2：窗口函数实现（易扩展，适合多版本表合并场景） #

如果后续存在多批次更新表需要合并，可使用窗口函数按优先级排序取数，扩展成本更低：

WITH all_merge_data AS (
    SELECT *, 1 AS data_priority FROM table01_new -- 新表优先级最高，标记为1
    UNION ALL
    SELECT *, 2 AS data_priority FROM table01_old -- 旧表优先级更低，标记为2
)
SELECT id, customer, product, quantity
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY data_priority ASC) AS rn
    FROM all_merge_data
) t
WHERE rn = 1;

该写法的返回结果和方案1完全一致，后续新增更新表时，只需要在all_merge_data CTE块中添加对应表的查询语句、设置好优先级即可，不需要修改外层筛选逻辑。

持久化视图创建 #

如果需要长期使用合并后的结果，可直接基于上述SQL创建视图，后续查询视图即可直接获取符合规则的合并数据：

CREATE VIEW v_table01_merged AS
-- 替换为你选择的任意一种合并SQL即可
SELECT id, customer, product, quantity FROM table01_new
UNION ALL
SELECT id, customer, product, quantity FROM table01_old
WHERE NOT EXISTS (SELECT 1 FROM table01_new n WHERE n.id = table01_old.id);

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