PostgreSQL将JSONB列修改为JSON类型报错的解决方法咨询

报错原因 #

1. 报错Operator class "jsonb_path_ops" does not accept data type json的核心原因：你要修改的jsonb列上存在依赖jsonb_path_ops运算符类的GIN索引，该运算符类仅支持jsonb类型，与json类型不兼容，类型转换时索引无法适配导致报错。
2. 报错Type "to_json" does not exist的原因：语法错误，to_json是函数不是数据类型，::是PostgreSQL的强制类型转换语法，后面必须跟数据类型，不能直接跟函数名。

可行操作步骤 #

• 第一步：查询目标列上的相关jsonb_path_ops索引，执行以下SQL，替换其中的表名和列名即可：

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table_name'
  AND indexdef LIKE '%jsonb_path_ops%'
  AND indexdef LIKE '%your_jsonb_column_name%';

• 第二步：确认查询到的索引无必要保留后，删除该索引：

DROP INDEX your_index_name;

• 第三步：重新执行列类型转换操作：

ALTER TABLE your_table_name
ALTER COLUMN your_jsonb_column_name TYPE json
USING your_jsonb_column_name::json;

注意事项 #

• 操作前请务必备份表数据，避免误操作导致数据丢失
• 若表数据量较大，ALTER TABLE操作会持有表锁，建议在业务低峰期执行
• json类型不支持jsonb的专属查询运算符（如包含判断@>、键存在判断?等），如果后续业务仍需要使用这类能力，请提前评估兼容性
• 若后续需要对json类型列做检索，可根据业务需求创建适配json类型的索引，性能会低于jsonb同类索引

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