如何使我的postgresql数据库使用不区分大小写的排序规则？ [英] How to make my postgresql database use a case insensitive collation?

问题描述

在一些SO帖子中，OP要求以一种不区分大小写的方式搜索文本列的有效方法。

In several SO posts OP asked for an efficient way to search text columns in a case insensitive manner.

据我所知，最有效的方法是有一个不区分大小写的排序规则的数据库。就我而言，我是从头开始创建数据库的，因此我对DB排序规则具有完美的控制。唯一的问题是我不知道如何定义它，也找不到它的任何示例。

As much as I could understand the most efficient way is to have a database with a case insensitive collation. In my case I am creating the database from scratch, so I have the perfect control on the DB collation. The only problem is that I have no idea how to define it and could not find any example of it.

请告诉我如何使用区分大小写的排序规则创建数据库。

Please, show me how to create a database with case insensitive collation.

我正在使用Postgresql 9.2.4。

I am using postgresql 9.2.4.

编辑1

CITEXT 扩展名是一个很好的解决方案。但是，它有一些限制，如文档中所述。如果没有更好的方法，我一定会使用它。

The CITEXT extension is a good solution. However, it has some limitations, as explained in the documentation. I will certainly use it, if no better way exists.

我想强调一下，我希望 ALL 字符串操作能成为大小写不敏感。一种方法是对每个 TEXT 字段使用 CITEXT 。但是，如果可能的话，最好使用不区分大小写的排序规则。

I would like to emphasize, that I wish ALL the string operations to be case insensitive. Using CITEXT for every TEXT field is one way. However, using a case insensitive collation would be the best, if at all possible.

现在 https://stackoverflow.com/users/562459/mike-sherrill-catcall 说PostgreSQL使用了底层系统公开的任何排序规则。我不介意使操作系统公开不区分大小写的排序规则。唯一的问题我不知道该怎么做。

Now https://stackoverflow.com/users/562459/mike-sherrill-catcall says that PostgreSQL uses whatever collations the underlying system exposes. I do not mind making the OS expose a case insensitive collation. The only problem I have no idea how to do it.

推荐答案

此问题以来发生了很多变化。 PostgreSQL v12中增加了对不区分大小写排序规则的本机支持。如其他答案所述，这基本上弃用了 citext 扩展名。

A lot has changed since this question. Native support for case-insensitive collation has been added in PostgreSQL v12. This basically deprecates the citext extension, as mentioned in the other answers.

在PostgreSQL v12中，可以做到：

In PostgreSQL v12, one can do:

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

    CREATE TABLE names(
      first_name text,
      last_name text
    );

    insert into names values
      ('Anton','Egger'),
      ('Berta','egger'),
      ('Conrad','Egger');

    select * from names
      order by
        last_name collate case_insensitive,
        first_name collate case_insensitive;

请参见 https://www.postgresql.org/docs/current/collat​​ion.html 了解更多信息。

这篇关于如何使我的postgresql数据库使用不区分大小写的排序规则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！