PostgreSQL 11.16中VARCHAR含连续双空格的等值查询失效问题

核心原因 #

PostgreSQL 11.13至11.16的补丁调整了部分字符排序规则（collation）对连续空格的比较逻辑：部分Unicode排序规则（如en_US.utf8）会将连续空白字符视为单一空格进行等值比较，而LIKE/ILIKE是逐字符精确匹配，IS NOT DISTINCT FROM会严格按字节校验，因此不受该规则影响。单空格查询正常是因为字段值与查询条件的空格数量一致，不会触发多空格合并逻辑。

排查步骤 #

1. 查看目标列的排序规则：

SELECT column_name, collation_name 
FROM information_schema.columns 
WHERE table_name = 'table' AND column_name = 'name';

2. 验证排序规则的空格处理行为：

-- 将<查询到的排序规则>替换为实际值
SELECT 'WORD1  WORD2' COLLATE '<查询到的排序规则>' = 'WORD1 WORD2' COLLATE '<查询到的排序规则>';

如果结果为true，说明该排序规则确实会合并连续空格。

解决方案 #

方案1：查询时显式指定无空格合并的排序规则 #

在查询中强制使用C或POSIX排序规则（这类规则会严格逐字符比较）：

SELECT * FROM table WHERE name COLLATE "C" = 'WORD1  WORD2' COLLATE "C";

方案2：修改列的排序规则（永久生效） #

如果业务需要严格的字符匹配，可将列的排序规则改为C（操作前请备份数据，避免影响现有业务）：

ALTER TABLE table ALTER COLUMN name TYPE VARCHAR COLLATE "C";

方案3：使用二进制比较 #

通过BINARY修饰符强制逐字节精确匹配：

SELECT * FROM table WHERE name = BINARY 'WORD1  WORD2';

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