PostgreSQL修改自定义类型列类型转换失败的解决方法

报错原因 #

• 第一条语句执行失败：trim()（即报错里的pg_catalog.btrim）是字符串处理函数，仅支持文本类型入参，传入自定义复合类型invoice_item时不存在匹配的函数签名，自然报错。
• 第二条语句执行失败：PostgreSQL 不会自动为两个独立的自定义复合类型创建类型转换规则，未手动定义转换逻辑的前提下，无法直接通过::语法做强制类型转换。

可行操作 #

由于invoice_history是无任何存量数据的空表，不需要处理历史值的类型映射，任选以下一种方案即可：

方案1：删列后重建（推荐，操作最简单） #

空表不需要保留列内数据，直接删除旧列、添加同名列即可，不需要创建任何额外的转换逻辑：

ALTER TABLE invoice_history DROP COLUMN invoice_info;
ALTER TABLE invoice_history ADD COLUMN invoice_info invoice;

如果原列上配置了默认值、非空约束等属性，新增列时重新加上对应配置即可。

方案2：临时创建转换规则修改列类型 #

如果不想重建列（比如列上有较多依赖配置，重新加配置麻烦），可以临时创建两个类型的转换逻辑，改完列类型后清理临时资源即可：

-- 定义临时转换函数，空表场景下函数内部逻辑不会被实际执行
CREATE OR REPLACE FUNCTION _tmp_invoice_item_to_invoice(val invoice_item)
RETURNS invoice AS $$
BEGIN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 注册类型转换规则
CREATE CAST (invoice_item AS invoice)
WITH FUNCTION _tmp_invoice_item_to_invoice(invoice_item);

-- 执行列类型修改
ALTER TABLE invoice_history ALTER COLUMN invoice_info TYPE invoice;

-- 清理临时创建的转换规则和函数，避免干扰后续业务
DROP CAST IF EXISTS (invoice_item AS invoice);
DROP FUNCTION IF EXISTS _tmp_invoice_item_to_invoice(invoice_item);

补充：如果后续遇到有存量数据的表需要做同类修改，只需要把上面转换函数里的RETURN NULL替换成真实的字段映射逻辑即可，比如按照两个类型的字段对应关系赋值：

CREATE OR REPLACE FUNCTION _tmp_invoice_item_to_invoice(val invoice_item)
RETURNS invoice AS $$
BEGIN
    RETURN (val.col1, val.col2, val.col3)::invoice; -- 按实际字段顺序/映射关系填写
END;
$$ LANGUAGE plpgsql IMMUTABLE;

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