PostgreSQL 9.6从tbl表生成map表多行数据的技术问询

需求说明 #

现有tbl表包含name、in1、in2（数组类型）字段，数据如下：

• name='a'：in1=1，in2为空
• name='b'：in1=2，in2='{3}'
• name='c'：in1=4，in2='{5,6}'
• name='d'：in1=7，in2='{8,9,10}'

需要生成map表（含name、out1、out2字段），要求：

1. 每个name对应in1生成一行：out1=in1，out2=in1
2. 每个name的in2数组中每个元素单独生成一行：out1=in1，out2=数组元素

解决方案 #

使用PostgreSQL内置的unnest()函数拆分数组，配合UNION ALL合并两种行数据，一次性完成插入操作，无需分两次执行插入和更新：

INSERT INTO map(name, out1, out2)
-- 生成in1对应的行
SELECT name, in1, in1
FROM tbl
UNION ALL
-- 拆分in2数组生成对应行
SELECT name, in1, unnest(in2)
FROM tbl
-- 过滤掉in2为空的情况，避免生成无效行
WHERE in2 IS NOT NULL AND array_length(in2, 1) > 0;

代码说明 #

• unnest(in2)：将数组in2的每个元素拆分为单独的行，每行对应一个数组元素
• UNION ALL：合并两部分查询结果，保留所有行（此处无重复场景）
• WHERE in2 IS NOT NULL AND array_length(in2, 1) > 0：避免in2为空或空数组时生成多余空行

执行以上SQL后，map表的最终数据如下：

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