Postgres中如何将integer[]类型列转换为integer类型？含多元素处理

针对你在PostgreSQL里遇到的integer[]类型列转换需求，尤其是数组包含多个元素的场景，这里提供两种对应你需求的可行方案：

回顾：单元素数组的转换 #

你已经知道，对于只有单个元素的数组（比如{99}），可以直接通过USING子句提取数组第一个元素并转换类型：

ALTER TABLE emails ALTER COLUMN user_email_id TYPE integer USING user_email_id[1]::INTEGER;

但这种方式在遇到多元素数组时，要么只会取第一个元素，要么直接报错，显然不符合多元素场景的需求，下面针对这类场景给出两种方案：

方案1：将数组转为字符串类型 #

如果你只是想把数组内容以字符串形式保留（比如把{98,110}转为"98,110"），可以使用PostgreSQL的array_to_string函数修改列类型：

ALTER TABLE emails ALTER COLUMN user_email_id TYPE text USING array_to_string(user_email_id, ',');

执行后查询结果会变成：

id | user_id | user_email_id
----+---------+---------------
65 | 1       | 98,110
66 | 1       | 99

如果之后需要转回数组，也可以用string_to_array函数反向转换。

方案2：将多元素数组拆分为多行记录 #

如果你的需求是把包含多个元素的数组拆分成独立行（比如把id=65的一条记录拆成两条，分别对应98和110），可以使用unnest函数展开数组，步骤如下：

1. 先验证拆分效果 #

执行查询确认拆分后的结果是否符合预期：

SELECT id, user_id, unnest(user_email_id) AS user_email_id
FROM emails;

查询结果会是：

id | user_id | user_email_id
----+---------+---------------
65 | 1       | 98
65 | 1       | 110
66 | 1       | 99

2. 保存拆分后的数据 #

确认效果后，可以创建新表存储拆分后的数据（建议先备份原表）：

-- 创建新表存储拆分后的数据
CREATE TABLE emails_new AS
SELECT id, user_id, unnest(user_email_id)::integer AS user_email_id
FROM emails;

3. 替换原表（可选） #

如果需要用新数据替换原表，执行以下操作（操作前务必备份原表）：

-- 重命名原表为备份表
ALTER TABLE emails RENAME TO emails_old;
-- 重命名新表为原表名
ALTER TABLE emails_new RENAME TO emails;
-- 给新表添加必要约束（比如联合主键，因为原id会重复）
ALTER TABLE emails ADD PRIMARY KEY (id, user_email_id);

注意事项 #

• 无论哪种方案，操作前一定要备份原表数据，避免意外数据丢失。
• 如果是生产环境，建议在业务低峰期执行DDL语句，避免锁表影响业务。

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