如何将UUID[]列数据去重合并后迁移至另一张表

核心实现逻辑 #

按table1的table2_id分组，分别将同id下的column_one、column_two数组拆分、去重后重新聚合，写入目标表table2。

全版本兼容SQL（推荐） #

兼容性最强，不会出现多列拆分产生的笛卡尔积问题：

INSERT INTO table2 (id, column_one, column_two)
SELECT 
  t1_agg.table2_id AS id,
  -- 合并去重column_one
  ARRAY(SELECT DISTINCT unnest(t1_agg.col1_arr) ORDER BY 1) AS column_one,
  -- 合并去重column_two
  ARRAY(SELECT DISTINCT unnest(t1_agg.col2_arr) ORDER BY 1) AS column_two
FROM (
  -- 按table2_id分组聚合所有对应数组
  SELECT 
    table2_id,
    array_agg(column_one) AS col1_arr,
    array_agg(column_two) AS col2_arr
  FROM table1
  GROUP BY table2_id
) t1_agg;

PostgreSQL 14+ 简化写法 #

高版本支持array_agg直接加distinct参数：

INSERT INTO table2 (id, column_one, column_two)
SELECT 
  table2_id AS id,
  array_agg(DISTINCT e1 ORDER BY e1) AS column_one,
  array_agg(DISTINCT e2 ORDER BY e2) AS column_two
FROM table1
LEFT JOIN LATERAL unnest(column_one) e1 ON true
LEFT JOIN LATERAL unnest(column_two) e2 ON true
GROUP BY table2_id;

注意事项 #

• 若table2中已存在对应id的记录，将INSERT改为UPDATE关联更新即可
• 生产环境执行前请先备份table1原始数据，执行前可先单独运行SELECT部分校验聚合结果是否符合预期
• 不需要排序的话可以去掉语句中的ORDER BY部分，执行效率更高

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