如何通过子查询在单次更新中批量更新重复行（并行数组）

表结构 #

主表 final_table #

CREATE TEMP TABLE final_table (
    idx INTEGER,
    pids INTEGER[],
    stats INTEGER[]
);
INSERT INTO final_table
VALUES
    (2733111, '{43255890, 8653548}'::int[], '{4, 5}'::int[]),   
    (2733112, '{54387564}'::int[]         , '{6}'::int[]   ),   
    (2733113, '{}'::int[]                 , '{}'::int[]    );

临时表 aggreg #

CREATE TEMP TABLE aggreg (
    idx INTEGER,
    pid INTEGER,
    count INTEGER
);
INSERT INTO aggreg
VALUES
    (2733111, 21854997,  2),    
    (2733111, 21854923, 10),    
    (2733112, 12345689,  3),    
    (2733113, 98765348, 11),    
    (2733111, 43255890,  4),    
    (2733112, 54387564,  6);

需求 #

用临时表aggreg的数据更新主表final_table，仅新增主表中不存在的pid及其对应的count值到pids和stats数组中，避免同一行被多次更新导致仅保留最后一次结果。

无效查询尝试 #

UPDATE final_table AS ft
    SET
        stats = ag2.stats || (ag2.stats - ft.stats),
        pids = ag2.pids || (ag2.pids - ft.pids)
FROM (
    SELECT
        ag.idx,
        array_agg(ag.pid) AS pids,
        array_agg(ag."count") AS stats
    FROM
            aggreg AS ag
    INNER JOIN
            final_table AS ft
    ON
            ag.idx = ft.idx
    GROUP BY
            ag.idx
) AS ag2
WHERE
    ag2.idx = ft.idx;

已启用intarray扩展实现数组减法操作

预期结果 #

解决方案 #

核心思路是先筛选出aggreg中每个idx下未在final_table中存在的pid和count，聚合为数组后再和原数组拼接，避免重复数据和多次更新问题：

-- 确保已启用intarray扩展（若未启用）
-- CREATE EXTENSION IF NOT EXISTS intarray;

UPDATE final_table ft
SET
    pids = ft.pids || upd.new_pids,
    stats = ft.stats || upd.new_stats
FROM (
    SELECT
        ag.idx,
        array_agg(ag.pid) AS new_pids,
        array_agg(ag.count) AS new_stats
    FROM aggreg ag
    JOIN final_table ft2 ON ag.idx = ft2.idx
    -- 过滤掉已存在于主表pids中的pid
    WHERE NOT ag.pid = ANY(ft2.pids)
    GROUP BY ag.idx
) upd
WHERE ft.idx = upd.idx;

说明 #

1. 子查询中通过NOT ag.pid = ANY(ft2.pids)筛选出当前idx下需要新增的pid及对应count；
2. 对筛选后的数据按idx分组，用array_agg聚合为新增数组；
3. 更新时直接将原数组与新增数组拼接，实现仅添加非重复数据的效果。

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