如何让我的 postgresql 数据库使用不区分大小写的排序规则？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18807276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make my postgresql database use a case insensitive collation?
提问by mark
In several SO posts OP asked for an efficient way to search text columns in a case insensitive manner.
在几个 SO 帖子中，OP 要求一种以不区分大小写的方式搜索文本列的有效方法。
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.
据我所知，最有效的方法是使用不区分大小写的排序规则的数据库。就我而言，我是从头开始创建数据库，因此我可以完美地控制 DB 排序规则。唯一的问题是我不知道如何定义它，也找不到任何例子。
Please, show me how to create a database with case insensitive collation.
请告诉我如何使用不区分大小写的排序规则创建数据库。
I am using postgresql 9.2.4.
我正在使用 postgresql 9.2.4。
EDIT 1
编辑 1
The CITEXTextension is a good solution. However, it has some limitations, as explained in the documentation. I will certainly use it, if no better way exists.
该CITEXT扩展是一个很好的解决方案。但是，它有一些限制，如文档中所述。如果没有更好的方法，我肯定会使用它。
I would like to emphasize, that I wish ALLthe string operations to be case insensitive. Using CITEXTfor every TEXTfield is one way. However, using a case insensitive collation would be the best, if at all possible.
我想强调的是，我希望所有的字符串操作都不区分大小写。使用CITEXT每一个TEXT字段是一种方式。但是，如果可能的话，最好使用不区分大小写的排序规则。
Now https://stackoverflow.com/users/562459/mike-sherrill-catcallsays 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.
现在https://stackoverflow.com/users/562459/mike-sherrill-catcall说 PostgreSQL 使用底层系统公开的任何排序规则。我不介意让操作系统公开不区分大小写的排序规则。唯一的问题我不知道该怎么做。
采纳答案by pbillen
A lot has changed since this question. Native support for case-insensitive collation has been added in PostgreSQL v12. This basically deprecates the citextextension, as mentioned in the other answers.
自从这个问题以来发生了很多变化。PostgreSQL v12 中添加了对不区分大小写排序规则的本机支持。citext如其他答案中所述，这基本上弃用了扩展名。
In PostgreSQL v12, one can do:
在 PostgreSQL v12 中，可以执行以下操作：
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;
See https://www.postgresql.org/docs/current/collation.htmlfor more information.
有关更多信息，请参阅https://www.postgresql.org/docs/current/collation.html。
回答by Denis de Bernardy
There are no case insensitive collations, but there is the citext extension:
没有不区分大小写的排序规则，但有 citext 扩展名：
回答by ToniTornado
For my purpose the ILIKE keyword did the job.
就我而言，ILIKE 关键字完成了这项工作。
From the postgres docs:
The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.
可以使用关键字 ILIKE 代替 LIKE 使匹配根据活动区域设置不区分大小写。这不在 SQL 标准中，而是 PostgreSQL 扩展。
回答by ?tefan Barto?
This is not changing collation, but maybe somebody help this type of query, where I was use function lower:
这不会改变排序规则，但也许有人可以帮助这种类型的查询，我在那里使用函数lower：
SELECT id, full_name, email FROM nurses WHERE(lower(full_name) LIKE '%bar%' OR lower(email) LIKE '%bar%')
SELECT id, full_name, email FROM nurses WHERE(lower(full_name) LIKE '%bar%' OR lower(email) LIKE '%bar%')
回答by glyphobet
I believe you need to specify your collation as a command line option to initdbwhen you create the database cluster. Something like
我相信您需要initdb在创建数据库集群时将排序规则指定为命令行选项。就像是
initdb --lc-collate=en_US.UTF-8
It also seems that using PostgreSQL 9.3 on Ubuntu and Mac OS X, initdbautomatically creates the database cluster using a case-insensitive collation that is default in the current OS locale, in my case, en_US.UTF-8.
似乎在 Ubuntu 和 Mac OS X 上使用 PostgreSQL 9.3initdb会使用当前操作系统语言环境中默认的不区分大小写的排序规则自动创建数据库集群，在我的情况下为en_US.UTF-8.
Could you be using an older version of PostgreSQL that does not default to the host locale? Or could it be that you are on an operating system that does not provide any case-insensitive collations for PostgreSQL to choose from?
您是否可以使用不默认为主机语言环境的旧版本 PostgreSQL？或者可能是您使用的操作系统不提供任何不区分大小写的排序规则供 PostgreSQL 选择？
