PostgreSQL跨schema修改自定义类型列类型报错的解决方法

• PostgreSQL对自定义复合类型采用强校验规则，即使两个schema下的复合类型字段名、字段类型、字段顺序完全一致，数据库也不会默认二者兼容，无法自动完成隐式类型转换。
• 你第二次触发的报错核心原因有两个：一是原列实际类型为旧类型的数组myschema.test[]，但ALTER语句中写的目标类型是单个myschema1.test，类型维度不匹配；二是两个独立创建的自定义类型之间没有注册转换规则，直接强转必然失败。

方案一：直接移动自定义类型所属schema（最推荐，无转换风险） #

你不需要在新schema下创建同结构的重复类型，直接移动原有类型的schema即可，数据库会自动更新所有依赖该类型的对象引用，完全不会触发类型转换错误：

1. 先删除你提前在myschema1下创建的重复test类型（确认无其他业务依赖该新建类型再执行）：

DROP TYPE IF EXISTS myschema1.test;

2. 直接将原自定义类型移动到目标schema：

ALTER TYPE myschema.test SET SCHEMA myschema1;

执行完成后，所有使用myschema.test的表列、函数、存储过程都会自动将引用更新为myschema1.test，无需额外修改。

方案二：保留旧类型，手动转换列类型 #

如果因为业务依赖原因不能移动/删除原有myschema.test类型，必须保留两个schema下的同结构类型，按以下步骤操作：

1. 先确认原列的准确类型，执行以下SQL校验：

SELECT data_type, udt_schema, udt_name 
FROM information_schema.columns 
WHERE table_schema = 'myschema1' 
  AND table_name = 'table' 
  AND column_name = 'testcolumn';

如果查询结果中data_type为ARRAY、udt_name为_test，说明原列是数组类型，ALTER语句的目标类型需要写为myschema1.test[]，否则用单个类型myschema1.test即可。
2. 执行带正确USING规则的ALTER语句，两种写法二选一：

写法1：text中转（简单快捷，适合绝大多数场景） #

• 非数组列执行：

ALTER TABLE IF EXISTS myschema1.table 
ALTER COLUMN testcolumn TYPE myschema1.test 
USING testcolumn::text::myschema1.test;

• 数组列执行：

ALTER TABLE IF EXISTS myschema1.table 
ALTER COLUMN testcolumn TYPE myschema1.test[] 
USING testcolumn::text::myschema1.test[];

写法2：逐字段构造（生产环境推荐，无转义风险） #

• 非数组列执行：

ALTER TABLE IF EXISTS myschema1.table 
ALTER COLUMN testcolumn TYPE myschema1.test 
USING ROW(
  testcolumn.id,
  testcolumn.event,
  testcolumn.severity,
  testcolumn.status,
  testcolumn.value,
  testcolumn.text,
  testcolumn.type,
  testcolumn.update_time
)::myschema1.test;

• 数组列执行：

ALTER TABLE IF EXISTS myschema1.table 
ALTER COLUMN testcolumn TYPE myschema1.test[] 
USING ARRAY(
  SELECT ROW(
    ele.id,
    ele.event,
    ele.severity,
    ele.status,
    ele.value,
    ele.text,
    ele.type,
    ele.update_time
  )::myschema1.test
  FROM unnest(testcolumn) ele
);

• 大表执行ALTER TABLE操作会触发全表重写，会长时间持有ACCESS EXCLUSIVE排他锁，建议在业务低峰期执行，避免阻塞正常业务读写。
• 操作前务必备份目标表数据，避免转换逻辑异常导致数据丢失。

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