Postgres导入异构schema时如何拆分逗号分隔分类到关联表

默认你已经将源数据完整导入Postgres的临时表stg_items，表结构和你给出的示例一致，目标表结构默认如下：

• categories：id 自增主键，name 加唯一索引保证分类名称不重复
• item_to_category：item_id 对应源表的条目id，category_id 对应categories表的id，两者设联合主键避免重复关联

步骤1：插入所有不存在的分类到分类表 #

用Postgres内置的string_to_array和unnest函数拆分分类字段，提取所有不重复的分类名称批量写入分类表：

INSERT INTO categories (name)
SELECT DISTINCT unnest(string_to_array(categories, ',')) AS cate_name
FROM stg_items
-- 避免插入已存在的分类
ON CONFLICT (name) DO NOTHING;

如果你的categories表name字段没有加唯一索引，把ON CONFLICT子句替换为WHERE NOT EXISTS (SELECT 1 FROM categories c WHERE c.name = cate_name)即可

步骤2：写入条目与分类的关联关系 #

将源表每条数据拆分分类后，和categories表关联拿到分类id，批量写入关联表：

INSERT INTO item_to_category (item_id, category_id)
SELECT 
    s.id AS item_id,
    c.id AS category_id
FROM stg_items s
-- 拆分每个条目的分类列表为多行
CROSS JOIN unnest(string_to_array(s.categories, ',')) AS cate_name
JOIN categories c ON c.name = cate_name
-- 避免重复插入已有的关联关系
ON CONFLICT (item_id, category_id) DO NOTHING;

步骤3：数据校验（可选） #

执行以下查询确认数据一致性：

SELECT 
    -- 统计源表所有分类总数
    (SELECT SUM(array_length(string_to_array(categories, ','), 1)) FROM stg_items) AS source_cate_count,
    -- 统计关联表写入的关联总数
    (SELECT COUNT(*) FROM item_to_category) AS target_cate_count;

SQL是该场景下非常合适的处理工具：

• Postgres内置的数组处理、字段拆分函数可以直接处理逗号分隔字段，不需要额外引入第三方工具
• 全程在数据库内执行，没有数据跨系统导出导入的额外开销，性能远高于用应用代码读取处理再写入
• 可以利用数据库的事务特性，操作要么全成功要么全回滚，避免产生脏数据

如果是一次性的小批量数据导入，上述SQL方案已经是最优选择。如果是周期性同步的大规模数据，可以考虑以下优化方向：

• 导入临时表时临时关闭非必要索引、WAL日志，导入完成后再重建索引，大幅提升写入速度
• 用COPY命令替代普通INSERT导入源数据到临时表，导入速度可提升5~10倍
• 如果数据源本身支持计算拆分，可以在抽取阶段就把分类字段拆成多行，减轻数据库侧的计算压力

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