PostgreSQL 18 预览: 在非确定性排序规则下使用 LIKE

你是否在查询一些特殊字符串的时候，出现过不匹配的情况？现在，PostgreSQL 可以在非确定性排序规则下使用 LIKE 了。

特性提交日志

支持在非确定性排序规则下使用 LIKE。

在合入本次提交后，在不区分大小写的排序规则下，可以使用 LIKE 了。此前，PostgreSQL 内部没有实现这一功能，所以使用时会遇到不支持的错误。该特性添加了内部实现，并消除了这个错误。特性的实现遵循 SQL 标准中的相关规范。

与确定性排序规则不同，在非确定性排序规则下，LIKE 的匹配不能逐个字符进行，而必须按子串进行。例如，当我们使用 LIKE ‘foo%bar’ 进行匹配时，不能先找字符 ‘f’，再找 ‘o’，而必须找到与 ‘foo’ 匹配的子串。这是因为排序规则可能会认为不同长度的子串是相等的。这些都是在like_match.c文件的MatchText()函数内部实现的。

like.c文件中GenericMatchText()函数的改动，只是将区域设置信息传递给MatchText()函数，这在之前是不需要的。这与下面的Generic_Text_IC_like()函数完全匹配。

ILIKE 的行为没有变化。（目前还不清楚在非确定性排序规则下的 ILIKE 是否有意义。）

该补丁还更新了like_support.c文件中的match_pattern_prefix()函数，以支持对非确定性排序规则下的精确模式进行优化。

讨论：https://www.postgresql.org/message-id/flat/700d2e86-bf75-4607-9cf2-f5b7802f6e88@eisentraut.org

示例

首先，什么是非确定性排序规则？尽管该排序规则叫非确定性，但这并不意味着它们是随机的。确定性排序规则是指，只有在处理字节完全相同的字符串时，才认为它们相等。

例如，在确定性排序规则下，“żółw”（U&’\017c\00f3\0142w’）和 “żółw”（U&‘z\0307o\0301\0142w’）并不相同，因为它们的字节表示不同。

虽然这听起来可能有些晦涩难懂，但想想不区分大小写的排序规则。如果你希望 ‘bz’ 等于 ‘BZ’，就需要非确定性的排序规则。比如这样：

CREATE COLLATION case_insensitive (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

CREATE TABLE testit (x text collate case_insensitive );

INSERT INTO testit SELECT unnest('{az,AZ,bz,BZ,za,ZA}'::text[]);

SELECT * FROM testit WHERE x = 'bz';
 x
----
 bz
 BZ
(2 rows)

然而问题在于，在 PostgreSQL 18 之前，不能在这样的列上使用 LIKE：

SELECT * FROM testit WHERE x like '%a%';
ERROR:  nondeterministic collations are not supported for LIKE

但现在，在 PostgreSQL 18 中，就可以使用了：

SELECT * FROM testit WHERE x like '%a%';
 x
----
 az
 AZ
 za
 ZA
(4 rows)

这在需要使用非确定性排序规则时，确实很有用。感谢社区的所有相关人员。

参考

提交日志：https://git.postgresql.org/pg/commitdiff/85b7efa1cdd63c2fe2b70b725b8285743ee5787f