Postgres 18临时表文本列排序规则问题及迁移困惑

一、临时表文本列排序规则显示null的原因与解决 #

临时表的文本列若未显式指定排序规则，会继承当前会话的默认排序规则，但information_schema.columns的collation_name字段仅会显示显式指定的排序规则，继承的规则会返回null。实际运行时，列的排序规则是会话默认值（即你设置的case_insensitive）。

验证与修复方法： #

1. 查看临时表列实际生效的排序规则：

SELECT attname, pg_get_expr(attcollation, attrelid) AS actual_collation
FROM pg_attribute
WHERE attrelid = 'pg_temp_288.tmp_test'::regclass
AND attnum > 0;

2. 创建临时表时显式指定排序规则，避免依赖会话默认：

CREATE TEMPORARY TABLE tmp_test(test text COLLATE "case_insensitive");

此时再查询information_schema.columns就会返回正确的case_insensitive。

二、递归查询/正则需指定C排序的原因 #

Postgres对非确定性排序规则有以下限制：

• 递归CTE：递归过程需要稳定的比较逻辑，非确定性排序（如大小写不敏感）可能导致递归结果不稳定，因此必须使用确定性的C排序规则来保证递归的正确性。
• 正则表达式（~操作符）：正则匹配是基于字节的精确匹配，非确定性排序会干扰字节级的匹配逻辑，所以必须显式指定C排序（基于ASCII字节顺序）才能让[0-9]、[a-z]这类模式正常工作。

示例修复： #

• 递归查询中指定排序规则：

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name COLLATE "C" AS name FROM table
    UNION ALL
    SELECT t.id, t.parent_id, t.name COLLATE "C" FROM table t
    JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte;

• 正则匹配时指定排序规则：

SELECT * FROM users WHERE username COLLATE "C" ~ '[a-z0-9]+';

三、非确定性排序替代CIText的潜在风险 #

虽然Postgres 18支持非确定性排序的LIKE，但替代CIText仍需注意以下问题：

• 唯一索引限制：非确定性排序的列无法直接创建唯一索引（大小写不同但逻辑相等的值，字节存储不同，会导致唯一约束冲突）。若需实现类似SQL Server的大小写不敏感唯一约束，需用表达式索引：

CREATE UNIQUE INDEX idx_users_username ON users (username COLLATE "case_insensitive");

• 性能开销：非确定性排序的比较、排序、分组操作比确定性排序（如C）稍慢，针对大数据量的查询需做性能测试。
• 函数兼容性：部分字符串函数（如position、substring）在非确定性排序下的行为可能与SQL Server不一致，需逐一验证。例如position('A' IN 'apple')在case_insensitive排序下是否返回1（符合预期）。
• 排序规则细节差异：SQL Server的Latin1_General_CI_AS是大小写不敏感、重音敏感，而Postgres默认的case_insensitive排序规则需确认重音处理是否一致。若不一致，需创建自定义排序规则：

CREATE COLLATION case_insensitive_ci_as (
    PROVIDER = icu,
    LOCALE = 'en-US-u-ks-level2-kc-true',
    DETERMINISTIC = false
);

• 跨会话一致性：若会话默认排序规则被修改，未显式指定排序的列会继承新规则，导致查询结果不一致。因此所有涉及大小写不敏感的列（含临时表）必须显式指定排序规则。

总结建议 #

1. 所有文本列（含临时表）显式指定case_insensitive排序规则，避免依赖会话默认；
2. 递归查询和正则匹配场景，针对性指定C排序规则；
3. 替换CIText时，重新设计唯一约束为表达式索引；
4. 全面测试核心查询的排序、分组、连接逻辑，确保与SQL Server行为一致。

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