能否将数组作为行插入PostgreSQL表？现有操作结果不符

问题描述 #

创建的表结构：

create table mytable(id text, name text, phone text)

拥有的数组（每个数组对应一行数据）：

ARRAY['1','Billy','123'] ARRAY['2','Ann','321'] ARRAY['3','Ivor','213']

期望插入后的结果：

id|name |phone 
---+-----+------
 1 |Billy|123
 2 |Ann  |321

尝试执行的插入语句：

insert into (id, name, phone)
select * from 
unnest('{1,Billy,123}'::TEXT[],'{2,Ann,321}'::TEXT[],'{3,Ivor,213}'::TEXT[]) 

得到的错误结果：

id    |name   |phone
-------+-------+------
 1     |2      |3
 Billy |Ann    |Ivor
 123   |321    |213

疑问：是否可以将数组作为行插入表中，还是只能将数组作为列插入？

解决方案 #

完全可以将数组作为行插入，你之前的错误是因为unnest处理多数组的逻辑不对——当传递多个同长度数组给unnest时，它会按列展开元素，而非按行组合。

以下两种方法可以实现需求：

方法1：将行级数组嵌套在外层数组中处理 #

把每个对应一行数据的数组放在一个外层数组里，直接提取内层数组的元素作为字段值：

INSERT INTO mytable(id, name, phone)
SELECT arr[1], arr[2], arr[3]
FROM unnest(ARRAY[
    ARRAY['1','Billy','123'],
    ARRAY['2','Ann','321'],
    ARRAY['3','Ivor','213']
]) AS arr
-- 若只需要插入前两行，添加LIMIT限制
LIMIT 2;

方法2：用序号对齐数组元素 #

给每个字段数组的元素加上序号，通过序号关联同一行的元素：

INSERT INTO mytable(id, name, phone)
SELECT id.val, name.val, phone.val
FROM unnest('{1,Billy,123}'::TEXT[]) WITH ORDINALITY AS id(val, idx)
JOIN unnest('{2,Ann,321}'::TEXT[]) WITH ORDINALITY AS name(val, idx) USING(idx)
JOIN unnest('{3,Ivor,213}'::TEXT[]) WITH ORDINALITY AS phone(val, idx) USING(idx)
-- 只取前两行则添加以下条件
WHERE idx <= 2;

总结 #

PostgreSQL支持将数组作为行插入，核心是要正确控制数组的展开逻辑：要么用外层数组包裹行级数组直接提取元素，要么通过序号对齐单个字段数组的元素来组合成行。

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