PostgreSQL是否支持“不敏感”归类？

Question

在Microsoft SQL Server中，可以指定“不区分重音”的排序规则（对于数据库，表或列），这意味着可以对诸如

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

查找具有Joao名称的行。

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

Answer 1

为此，请使用 unaccent模块
-这与您要链接的 模块 完全不同。

unaccent是一种文本搜索词典，用于删除词素中的重音符号。

每个数据库安装一次：

CREATE EXTENSION unaccent;

如果出现类似以下错误：

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

file or directory

除其他外，它提供了unaccent()可与示例一起使用的功能（LIKE似乎不需要）。

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

指数

要将索引用于此类查询，请在expression上创建一个索引。 但是
，Postgres仅接受IMMUTABLE索引功能。如果函数可以为相同的输入返回不同的结果，则索引可能会默默中断。

unaccent()只是STABLE没有IMMUTABLE

不幸的是，unaccent()只是STABLE，不是IMMUTABLE。根据pgsql-
bugs上的该线程，这是由于
三个 原因：

1. 这取决于字典的行为。
2. 此字典没有硬线连接。
3. 因此，它还取决于电流search_path，电流很容易改变。

网络上的一些教程只是要求将函数的波动性更改为IMMUTABLE。在某些情况下，这种暴力破解方法可能会崩溃。

其他人建议使用一个简单的IMMUTABLE包装函数（就像我过去做的一样）。

是否使用两个参数
IMMUTABLE显式声明所用字典的变体一直在争论中。在这里或这里阅读。

另一个选择是该模块，由Github提供，具有Musicbrainz提供的IMMUTABLEunaccent()功能。我自己还没有测试过。我想我提出了一个 更好的主意 ：

随着其他解决方案的发展， 这种方法 更有效，也更安全 。
创建一个IMMUTABLESQL包装函数，该函数使用硬连线的模式限定函数和字典执行两参数形式。

由于嵌套一个不可改变的函数将禁用函数内联，因此也应基于声明的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）防止恶意用户使用功能的重载变体进行假设攻击。

以前，我提倡基于
unaccent模块附带的STABLE函数的包装函数unaccent()。该禁用功能内联。这个版本的执行
速度 比我之前在这里提到的简单包装器功能 快十倍 。
并且这已经是添加SET search_path = public, pg_temp到该函数的第一个版本的速度的两倍-
直到我发现字典也可以通过模式限定。从文档中仍然（Postgres
12）不太明显。

如果 您缺少创建C函数的必要特权，那么您将回到第二种最佳实现：将模块提供的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'直接提供未重音的字符串。

使用 表达式索引 ，更快的功能不会转换为快得多的查询。这对预先计算的值起作用，并且已经非常快了。但是索引维护和查询不使用索引的好处。

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

• postgres日志中的“文本搜索字典’naccent’不存在”条目，据推测是在自动分析过程中

连字

在Postgres 9.5或更旧的 连字中，如’艗’或’脽’必须手动扩展（如果需要），因为unaccent()总是替换 单个 字母：

SELECT unaccent('艗 脝 艙 忙 脽');

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

您会喜欢此更新在Postgres
9.6中 不突出显示的：

扩展contrib/unaccent标准unaccent.rules文件以处理Unicode已知的所有变音符号，并 正确扩展连字
（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') || '%');

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

• 在PostgreSQL中使用LIKE，SIMILAR TO或正则表达式进行模式匹配

pg_trgm还为“相似性”（%）和“距离”（<->）提供有用的运算符。

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