sql - PostgreSQL 是否支持 "accent insensitive"排序规则？

Question

在 Microsoft SQL Server 中，可以指定“不区分重音”的排序规则(对于数据库、表或列)，这意味着可以进行类似的查询

SELECT * FROM users WHERE name LIKE 'João'

查找带有 Joao 的行名称。

我知道可以使用 unaccent_string 从 PostgreSQL 中的字符串中去除重音符号。 contrib 函数，但我想知道 PostgreSQL 是否支持这些“不区分重音”的排序规则，所以 SELECT上面会起作用。

Answer 1

使用 unaccent module为此 - 这与您要链接的内容完全不同。

unaccent is a text search dictionary that removes accents (diacritic signs) from lexemes.

每个数据库安装一次:

CREATE EXTENSION unaccent;

如果您收到如下错误:

ERROR: could not open extension control file
"/usr/share/postgresql/<version>/extension/unaccent.control": No such file or directory

按照此相关答案中的指示，在您的数据库服务器上安装 contrib 包:

Error when creating unaccent extension on PostgreSQL

除此之外，它提供了功能 unaccent()您可以使用您的示例(其中 LIKE 似乎不需要)。

SELECT *
FROM   users
WHERE  unaccent(name) = unaccent('João');

指数

要为此类查询使用索引，请创建 index on the expression . 然而 , Postgres 只接受 IMMUTABLE索引函数。如果函数可以为相同的输入返回不同的结果，则索引可能会悄悄中断。
unaccent()只有 STABLE不是 IMMUTABLE
不幸的是， unaccent()只是 STABLE ，不是 IMMUTABLE .根据 this thread on pgsql-bugs ，这是由于三个原因:

这取决于字典的行为。

这本词典没有硬连线连接。

因此，它还取决于当前 search_path ，这很容易改变。

Some tutorials在网络上指示将函数波动性更改为 IMMUTABLE .这种蛮力方法在某些条件下可能会失效。

其他人建议一个 simple IMMUTABLE wrapper function (就像我过去所做的那样)。

是否制作 variant with two parameters 一直存在争论 IMMUTABLE它显式声明了使用的字典。阅读 here或 here .

另一种选择是这个带有 IMMUTABLE unaccent() function by Musicbrainz 的模块。 ，在 Github 上提供。自己没测试过。我想我想出了一个 更好的主意 :

目前最好

这种方法是 比其他解决方案更高效，更安全 .
创建 IMMUTABLE SQL 包装函数执行带有硬接线模式限定函数和字典的两参数形式。

由于嵌套非不可变函数会禁用函数内联，因此它基于 C 函数的副本，(假的)声明 IMMUTABLE以及。它的唯一目的是在 SQL 函数包装器中使用。不打算单独使用。

由于无法在 C 函数的声明中硬连接字典，因此需要复杂性。 (需要修改 C 代码本身。)SQL 包装函数可以做到这一点，并允许函数内联和表达式索引。

CREATE OR REPLACE FUNCTION public.immutable_unaccent(regdictionary, text)
  RETURNS text LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.immutable_unaccent(regdictionary 'public.unaccent', $1)
$func$;

掉落 PARALLEL SAFE来自 Postgres 9.5 或更早版本的两个函数。
public是您安装扩展的架构( public 是默认值)。

显式类型声明 ( regdictionary ) 防御恶意用户使用函数的重载变体进行的假设攻击。

之前，我提倡基于 STABLE 的包装函数功能 unaccent()随 unaccent 模块一起提供。那个残疾人 function inlining .此版本执行 快十倍比我之前在这里使用的简单包装函数。
这已经是添加 SET search_path = public, pg_temp 的第一个版本的两倍。到函数 - 直到我发现字典也可以是模式限定的。 Still (Postgres 12) not too obvious from documentation.

如 您缺乏创建 C 函数所需的权限，您将回到第二个最佳实现:An IMMUTABLE STABLE 周围的函数包装器 unaccent()模块提供的功能:

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT;

最后， 表达索引快速查询:

CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));

记得要 重新创建索引 在对函数或字典进行任何更改后涉及此函数，例如不会重新创建索引的就地主要版本升级。最近的主要版本都有 unaccent 的更新模块。

调整查询以匹配索引(因此查询计划器将使用它):

SELECT * FROM users
WHERE  f_unaccent(name) = f_unaccent('João');

您不需要正确表达式中的函数。在那里你还可以提供像 'Joao' 这样的无重音字符串。直接地。

使用 的更快的函数不会转换为更快的查询。 expression index .这对预先计算的值进行操作并且已经非常快了。但是索引维护和查询不使用索引的好处。

Postgres 10.3/9.6.8 等加强了客户端程序的安全性。在任何索引中使用时，您需要对函数和字典名称进行模式限定。见:

'text search dictionary “unaccent” does not exist' entries in postgres log, supposedly during automatic analyze

连字

在 Postgres 9.5 或更高版本 像 'Œ' 或 'ß' 这样的连字必须手动扩展(如果你需要)，因为 unaccent()总是替换一个字母:

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
E A e a S

你会爱上 this update to unaccent在 Postgres 9.6 :

Extend contrib/unaccent's standard unaccent.rules file to handle all diacritics known to Unicode, and expand ligatures correctly (Thomas Munro, Léonard Benedetti)

大胆强调我的。现在我们得到:

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
OE AE oe ae ss

模式匹配

对于 LIKE 或 ILIKE 对于任意模式，将其与模块 pg_trgm 结合使用在 PostgreSQL 9.1 或更高版本中。创建三元组 GIN(通常更可取)或 GIST 表达式索引。 GIN 示例:

CREATE INDEX users_unaccent_name_trgm_idx ON users
USING gin (f_unaccent(name) gin_trgm_ops);

可用于查询，如:

SELECT * FROM users
WHERE  f_unaccent(name) LIKE ('%' || f_unaccent('João') || '%');

GIN 和 GIST 索引的维护成本比普通 btree 更昂贵:

Difference between GiST and GIN index

对于左 anchor 定模式有更简单的解决方案。有关模式匹配和性能的更多信息:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

pg_trgm还提供了有用的 operators for "similarity" ( % ) and "distance" ( <-> ) .

Trigram 索引还支持简单的正则表达式 ~等。和 不区分大小写 模式匹配 ILIKE :

PostgreSQL accent + case insensitive search