Postgres升级至13+时timestamp_ops索引报错及操作符类排查

为什么PostgreSQL 13+会报错？ #

你猜的没错：PostgreSQL 12及以下版本对操作符类的类型校验较宽松，当你给TIMESTAMP WITH TIME ZONE（timestamptz）字段指定timestamp_ops时，会自动隐式替换为匹配的timestamptz_ops。但从PostgreSQL 13开始，系统收紧了类型匹配规则，要求操作符类必须与字段数据类型严格对应，因此直接抛出类型不兼容的错误。

查看索引实际使用的操作符类 #

你可以通过以下几种方式确认旧版本中索引实际使用的操作符类：

1. 使用psql的\d+命令（最直观） #

连接到数据库后，执行以下命令查看表或索引的详细信息：

• 查看指定索引：\d+ your_index_name
• 查看指定表的所有索引：\d+ your_table_name

输出结果中，索引的Index definition或Columns部分会明确显示每个字段对应的操作符类（比如hour timestamptz_ops）。

2. 查询系统表获取精确信息 #

执行以下SQL语句，直接从系统元数据中提取索引的操作符类：

SELECT
    idx.indexrelname AS index_name,
    col.attname AS column_name,
    opc.opcname AS operator_class
FROM
    pg_index idx
JOIN
    pg_class tbl ON idx.indrelid = tbl.oid
JOIN
    pg_attribute col ON col.attrelid = tbl.oid AND col.attnum = ANY(idx.indkey)
JOIN
    pg_opclass opc ON opc.oid = idx.indclass[array_position(idx.indkey, col.attnum)]
WHERE
    tbl.relname = 'your_table_name'  -- 替换为你的表名
    AND idx.indexrelname = 'your_index_name';  -- 可选：指定索引名过滤

3. 使用pg_get_indexdef函数获取索引定义 #

执行以下SQL，直接生成索引的创建语句，其中会包含实际使用的操作符类：

SELECT pg_get_indexdef('your_index_name'::regclass);

升级前的排查建议 #

1. 在PostgreSQL 10环境中，用上述方法检查所有涉及timestamptz字段的索引，确认是否存在指定timestamp_ops的情况。
2. 修改迁移脚本，将这类索引的操作符类明确改为timestamptz_ops，避免升级到14.4时触发报错。
3. 提前在测试环境中验证修改后的脚本，确保索引创建正常。

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