如何将PostgreSQL CREATE INDEX语句格式化为pg_indexes.indexdef规范格式？

我执行以下SQL创建索引：

CREATE INDEX test_1 ON partner (lower(name)) WHERE is_company is TRUE;

但查询pg_indexes表得到的索引定义格式和原始语句不一样：

SELECT indexdef from pg_indexes where indexname = 'test_1';

返回结果：

+----------------------------------------------------------------------------------------------------+
| indexdef                                                                                           |
|----------------------------------------------------------------------------------------------------|
| CREATE INDEX test_1 ON public.partner USING btree (lower((name)::text)) WHERE (is_company IS TRUE) |
+----------------------------------------------------------------------------------------------------+

这个结果里多了模式名、USING语句、类型转换和额外括号。我想知道怎么把原始语句转换成这种格式？我需要通过对比来判断是否要重建test_1索引。

这些格式差异都是PostgreSQL自动补全的默认值，完全不需要手动修改原始语句来匹配，具体差异的原因如下：

• public.模式名：创建索引时没指定模式的话，PostgreSQL会自动使用当前会话的默认模式（通常就是public），所以会在格式化时补全。
• USING btree：B-tree是PostgreSQL默认的索引类型，创建时省略该子句，系统会自动补上。
• (name)::text类型转换：如果name本身是text类型，这是系统格式化时自动添加的冗余转换，不影响功能；如果name是varchar这类字符类型，lower()函数会隐式把它转成text，系统只是把这个隐式操作显式写出来了。
• 额外括号：只是系统格式化SQL时的语法规范调整，和原始语句的语义完全一致。

如何判断是否需要重建索引 #

不用纠结格式化后的语句差异，直接检查索引的实际有效性和定义是否符合需求就行：

1. 确认索引表达式：不管有没有显式类型转换，lower(name)的实际功能是一致的
2. 确认过滤条件：is_company IS TRUE和原始语句的条件完全等价
3. 检查索引有效性：执行以下SQL，若返回t则索引有效：

SELECT indisvalid FROM pg_index WHERE indexrelid = 'test_1'::regclass;

只要以上几点都符合预期，就不需要重建索引。

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