PostgreSQL中如何将逗号分隔整数字符串列转换为整数数组类型

你遇到的报错由两个核心问题导致：

• 同一条ALTER TABLE语句中同时执行删除默认值、修改列类型、设置新默认值三个操作时，部分PostgreSQL版本会出现类型推导顺序错误，误将原文本类型的列识别为数组类型，触发Array type text doesn't have a component type错误。
• _int4是PostgreSQL的内部系统类型名，不属于对外兼容的标准SQL类型；同时string_to_array()返回的是文本数组（text[]），直接整体强转为整数数组时，没有显式转换数组内元素的类型，很容易触发类型匹配错误。

分三步独立执行，不要合并到同一条ALTER语句中：

1. 先删除列原有的默认值

ALTER TABLE commission_data ALTER activities DROP DEFAULT;

2. 修改列类型为整数数组，转换逻辑中显式将拆分后的每个文本值转为整数，兼容可能存在的空格等格式问题

ALTER TABLE commission_data
ALTER activities TYPE integer[]
USING ARRAY(
  SELECT trim(unnest(string_to_array(activities, ',')))::integer
);

说明：如果你的列中存在空值、非数字格式的脏数据，可以在子查询中增加nullif、正则判断等容错逻辑，避免转换过程中断。

3. 为整数数组列设置匹配类型的新默认值

ALTER TABLE commission_data ALTER activities SET DEFAULT '{}'::integer[];

• 日常编写SQL时优先使用标准类型名integer[]（别名int[]/int4[]），不要使用_int4这类内部类型名，避免版本升级、驱动兼容带来的异常。
• 数组类型转换时，优先通过unnest逐个处理元素的类型转换，比直接强转整个数组的兼容性更强，能处理更多边缘格式场景。

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