原文地址：CASE-INSENSITIVE PATTERN MATCHING IN POSTGRESQL
原文作者：Laurenz Albe不区分大小写的搜索是一个非常需要的功能，部分原因是为了保持与Microsoft SQL Server的兼容性。该问题有多种解决方案，其中之一是使用不区分大小写的 ICU 排序规则。这就像一个魅力，除非你想执行模式匹配。因此，让我们仔细看看这个问题和可能的解决方案。
不区分大小写搜索的替代方法
在 PostgreSQL 中存在三种不区分大小写搜索的已知解决方案：
lower()用or显式转换upper()
使用此方法的查询如下所示：
SELECT id, col FROM tab WHERE lower(col) = lower('search string');这可以通过在 上的 B-tree 索引来快速完成lower(col)，但有两个缺点：
该解决方案是在应用程序级别实现的，也就是说，您必须为不区分大小写的搜索定制查询
如果数据库列包含长值，则必须将整个值转换为小写，即使只需要比较几个字符，也会导致性能不佳使用citext扩展
该扩展citext提供了一种数据类型citext，代表“不区分大小写text”。该表定义为
CREATE TABLE tab ( col citext,查询很简单
SELECT id, col FROM tab WHERE col = 'search string';这既简单又方便，但也有一些缺点：
没有数据类型civarchar，因此您只能使用检查约束来实现它 较长值的性能也可能很差，因为在比较值之前citext内部调用lower(col COLLATE “default”) 正则表达式匹配不区分大小写，必须~*显式使用不区分大小写的运算符 使用不区分大小写的 ICU 排序规则
如果您使用的是 PostgreSQL v12 或更高版本，并且配置了 PostgreSQL --with-icu，您可以定义一个不区分大小写的排序规则，如下所示：
CREATE COLLATION english_ci ( PROVIDER = icu, -- 'en-US@colStrength=secondary' for old ICU versions LOCALE = 'en-US-u-ks-level2', DETERMINISTIC = FALSE该表将被定义为
CREATE TABLE tab ( col text COLLATE english_ci,并且查询再次很简单：
SELECT id, col FROM tab WHERE col = 'search string';有关 ICU 排序规则的更多详细信息，请阅读我关于该主题的文章
（地址：https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/）
模式匹配和不区分大小写的排序规则的问题
不区分大小写的排序规则是该问题最优雅的解决方案。这种方法有两个缺点：
尽管 PostgreSQL v15 允许您使用 ICU 排序规则作为默认数据库排序规则，但这不会扩展到不区分大小写的排序规则，因此您仍然必须为每个列定义显式指定排序规则 您不能将不区分大小写的排序规则与模式匹配一起使用 第一个问题只是一个烦恼，但第二个问题需要更多的思考。以下是展示该问题的两个示例：
SELECT id, long FROM perf_coll WHERE long LIKE 'abcd%'; ERROR: nondeterministic collations are not supported for LIKE SELECT id, long FROM perf_coll WHERE long ~ '^abcd'; ERROR: nondeterministic collations are not supported for regular expression为什么不支持？
德国足球的困境
ICU 文档详细说明了为什么正确的不区分大小写的模式匹配很困难。一个很好的例子是德语字母“ß”，它传统上没有大写等效项。因此，使用良好的德语排序规则（来自 GNU C 库的排序规则在这方面不好），您将得到如下结果：
SELECT upper('Fußball' COLLATE "de-DE-x-icu"); upper ══════════ FUSSBALL (1 row)现在，在不区分大小写的排序规则中，以下查询的正确结果是什么？
SELECT 'Fußball' LIKE 'FUS%';你可以争辩说它应该是TRUE，因为那是你会得到的upper(‘Fußball’) LIKE ‘FUS%’。另一方面，
SELECT lower('FUSSBALL' COLLATE "de-DE-x-icu"); lower ══════════ fussball (1 row)所以你也可以争辩结果应该是FALSE. 为简单起见，ICU 库与第二种解决方案一起使用。这两种解决方案都很难在 PostgreSQL 中实现，所以我们暂时放弃了这个问题。比较邮件列表中的此报价：
ICU 还提供正则表达式匹配，但不支持排序规则，因为
基于字符的模式不能很好地与排序规则的概念配合使用。
关于潜在的排序感知 LIKE，它看起来很难实现，
因为目前在 like_match.c 中使用的算法似乎纯粹
基于字符。AFAICS 无法将对 usearch_*
函数的调用插入其中，它需要从头开始单独重新设计。除了符号学
正确的德语没有普遍接受的权威。例如，讲德语的瑞士人在 20 世纪废除了 ß。另一方面，“Rat für deutsche Rechtschreibung”（德语拼写委员会）在 2017 年引入了大写字母 ẞ，但这种简化问题的尝试却被德语世界广泛忽视。简化德语，多么离谱的想法！
最终结果比以前更令人困惑：
SELECT upper('ß' COLLATE "de-DE-x-icu"), lower('ẞ' COLLATE "de-DE-x-icu"); upper │ lower ═══════╪═══════ SS │ ß (1 row)一种不区分大小写的模式匹配的解决方案
我们需要一个切实可行的解决方案。当然，我们可以使用lower()或强制转换为citext，但这会重新引入长字符串的性能问题。因此，我们通过显式使用不同的排序规则来规避问题。出于性能原因，并且为了避免德国足球的脑筋急转弯，我们强制转换为二进制排序规则，它会逐个字符地比较单词：
SELECT id, col FROM tab WHERE col COLLATE "C" ILIKE 'search%pattern'; SELECT id, col FROM tab WHERE col COLLATE "C" ~* '^search.*pattern';请注意，要使其按预期工作，我们必须使用不区分大小写的版本LIKE和正则表达式匹配 operator ~。
当然，这个解决方案并不像我们希望的那么简单：同样，应用程序必须编写明确指定不区分大小写处理的查询。另一个困难是性能：虽然 B-tree 索引可以支持区分大小写的模式匹配，但不区分大小写的模式匹配需要三元索引：
CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX tab_col_pattern_idx ON tab USING gin (col gin_trgm_ops);这样的索引可以加快上述两个语句的速度，但它会变大并且比普通的 B-tree 索引更新慢。
不区分大小写比较的性能测试
在这个测试中，我使用了一千万行的表和一个包含 320 个字符的随机字符串的列。列上没有索引。在表中perf_citext，列定义为citext。其中perf_lower，数据类型为text，比较使用lower()。表与上面定义的排序规则一起perf_coll使用。使用的数据库是带有美国英语排序规则的 PostgreSQL v15。所有表都缓存在共享缓冲区中。textenglish_ci
不区分大小写搜索技术的性能比较
|
|