PostgreSQL 基于CTE实现含拆分数组的主从两张表批量插入方法咨询

实现结论 #

完全可以通过CTE单条查询实现批量插入多条父记录及对应的嵌套子数组数据，无需逐条单条插入。

基础实现方案 #

如果你的父记录name字段没有重复值，可以直接使用以下语句：

WITH input_data AS (
    -- 待插入的源数据集，可任意扩展多条父记录
    SELECT * FROM (VALUES
        ('Parent1', ARRAY['Parent1-Child1', 'Parent1-Child2']),
        ('Parent2', ARRAY['Parent2-Child1'])
    ) AS t(parent_name, children_names)
),
insert_parent AS (
    -- 批量插入所有父记录，返回生成的主键和对应名称
    INSERT INTO parent (name)
    SELECT parent_name FROM input_data
    RETURNING id, name AS parent_name
)
-- 批量展开子数组插入子表
INSERT INTO child (name, parent_id)
SELECT unnest(input_data.children_names), insert_parent.id
FROM input_data
JOIN insert_parent ON input_data.parent_name = insert_parent.parent_name;

兼容重名的稳定优化方案 #

如果存在父名称重复的场景，通过行号匹配避免关联错误：

WITH input_data AS (
    SELECT 
        ROW_NUMBER() OVER () AS rn,
        parent_name,
        children_names
    FROM (VALUES
        ('Parent1', ARRAY['Parent1-Child1', 'Parent1-Child2']),
        ('Parent2', ARRAY['Parent2-Child1']),
        ('Parent1', ARRAY['Parent1-Child3', 'Parent1-Child4']) -- 支持同名父记录
    ) AS t(parent_name, children_names)
),
insert_parent AS (
    INSERT INTO parent (name)
    SELECT parent_name FROM input_data ORDER BY rn
    RETURNING id
)
INSERT INTO child (name, parent_id)
SELECT unnest(input_data.children_names), ip.id
FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM insert_parent
) ip
JOIN input_data ON ip.rn = input_data.rn;

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