PostgreSQL 17中如何将text类型元组数组转回record[]类型？

为什么SELECT ARRAY[(1,2)] AS a INTO temp_table;报错 #

PostgreSQL的record[]是伪类型，这类类型仅用于临时查询结果的传递，无法作为表列的持久化存储类型，因此直接将其存入临时表会触发42P16错误。

将text类型列转回复合类型数组的步骤 #

要完成转换，需先定义与原元组结构匹配的自定义复合类型，再通过类型转换实现：

1. 创建匹配的复合类型 #

假设原元组是两个整数组成的结构，创建对应复合类型：

CREATE TYPE int_pair AS (x integer, y integer);

2. 执行类型转换 #

根据text列的存储格式选择对应方式：

• 若text列存储标准数组字符串（如{"(1,2)","(3,4)"}），直接强制转换：

SELECT b::int_pair[] AS a FROM temp_table;

• 若text列存储带方括号的格式（如[(1,2)]），先清理字符串再转换：

SELECT string_to_array(trim(b, '[]'), ',')::int_pair[] AS a FROM temp_table;

避免报错的最佳实践 #

如果需要持久化存储这类元组数组，建议从一开始就使用自定义复合类型，而非伪类型：

-- 先创建复合类型
CREATE TYPE int_pair AS (x integer, y integer);
-- 直接插入临时表
SELECT ARRAY[(1,2)::int_pair] AS a INTO temp_table;

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