如何将PostgreSQL文本数组列转换为整数数组并保留数据？

错误原因 #

原迁移代码直接对text[]类型字段使用translate和replace字符串函数，而PostgreSQL无法将数组类型直接传入这类函数，导致语法错误。

正确迁移代码 #

class ChangeSelectedExtrasToInteger < ActiveRecord::Migration[5.2]
  def up
    # 遍历数组元素，去除引号后转为整数，重组为integer[]
    change_column :campaigns, :selectedextras, :integer, array: true, default: [], using: "array(select trim(unnest(selectedextras), '\"')::integer)"
  end

  def down
    # 反向操作，给整数元素添加引号后转为文本，重组为text[]
    change_column :campaigns, :selectedextras, :text, array: true, default: [], using: "array(select '\"' || unnest(selectedextras) || '\"'::text)"
  end
end

关键逻辑说明 #

• unnest(selectedextras)：将数组拆分为单个元素的数据集
• trim(elem, '"')：移除每个文本元素前后的双引号（匹配原text[]的存储格式）
• ::integer：将处理后的纯数字字符串转为整数类型
• array(select ...)：将单个元素重新组合为数组
• down方法反向执行，给每个整数元素添加双引号，确保转回text[]后格式与原数据一致

前置检查建议 #

迁移前请确保text[]中的所有元素都是可转为整数的数字，否则迁移会失败。可以用以下SQL排查不符合要求的记录：

SELECT * FROM campaigns 
WHERE NOT (selectedextras = '{}'::text[]) 
AND EXISTS (
  SELECT 1 FROM unnest(selectedextras) elem 
  WHERE elem !~ '^"\d+"$'
);

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