如何在PostgreSQL中进行“不区分大小写”的查询？

Question

在PostgreSQL中有没有办法编写不区分大小写的查询，例如我希望以下三个查询返回相同的结果。

SELECT id FROM groups where name='administrator'

SELECT id FROM groups where name='ADMINISTRATOR'

SELECT id FROM groups where name='Administrator'

- Jame

---

如果您的Postgres安装中带有citext，请尝试使用citext类型。它是不区分大小写的文本。 - Michael Buen

对于新来者来说，这个问题的链接是官方PostgreSQL文档，其中包含了在此处提供的所有答案，以及其他一些选项。请参考该链接获取更多信息。 - Parthian Shot

先生，请将已接受的答案重新分配给@Arun所提供的答案。它更简单，应用后不会带来一堆麻烦。 - zelibobla

Answer 1

在比较之前，使用LOWER函数将字符串转换为小写。

尝试以下代码：

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')

- Chandu

---

需要注意的是，在谓词列（在这种情况下为“name”）上使用LOWER（或任何函数）将导致索引不再可寻。如果这是一个大型或频繁查询的表，可能会引起麻烦。不区分大小写的排序规则、citext或基于函数的索引可以提高性能。 - Jordan

可以创建这样一个索引：CREATE INDEX idx_groups_name ON groups lower(name); 它会降低“name”字段的大小写，并将其用作索引的一部分。 - Daniel

如果您想让索引与 LIKE 'xxx%' 查询一起使用，请指定 varchar_pattern_ops，即 CREATE INDEX ix_groups_name ON groups (lower(name) varchar_pattern_ops)。 - sayap

使用ILIKE运算符（如下面其他答案所示）是一种更简单的方法，尽管这是得票最多的答案。 - Ryan

阅读这里的评论，很多建议使用ILIKE，它可以工作，但响应速度较慢。为了快速访问基于计算结果的表，我建议任何人只需检查已接受的答案。在这里和这里查看更多详细信息。 - Afolabi Olaoluwa

Answer 2

使用 ILIKE 代替 LIKE

SELECT id FROM groups WHERE name ILIKE 'Administrator'

- Mohammad Reza Norouzi

---

请注意，当在Spring Boot中使用时，Hibernate不支持ILIKE。 - AnT

@AnT 它可以与 org.hibernate.dialect.PostgreSQL94Dialect 和 Spring Boot 2.0.6.RELEASE 一起使用。但 IntelliJ 对此表示不满。 - skaveesh

ilike会变得更慢吗？特别是当字段被索引时？ - Ye Zhang

这是实际的答案，被接受的那个是一个hack。 - Igor Beaufils

实际上，“ILIKE”是最简单的答案，但并非所有情况下的“真正”答案。 “ILIKE”是Postgres的非标准扩展，它的性能非常慢。使用“LOWER”函数的被接受的答案，以及适当的索引，将执行得更好，并且将受到所有客户端库和ORMS的支持。这取决于您特定的用例。 - gerrard00

我有一个有10万行的表格。name ILIKE 'Administrator' 和 LOWER(name)=LOWER('Administrator') 之间的区别是5000毫秒（尝试了gist和gin trgm索引，结果相似），而btree lower index只需要5毫秒。请勿使用此方法。 - undefined

Answer 3

最常用的方法是将搜索字符串和数据转换为小写或大写。但是这种方法存在两个问题：

1.它适用于英语，但不是所有语言都适用（甚至可能大多数语言都不适用）。并非每个小写字母都有对应的大写字母；也不是每个大写字母都有对应的小写字母。

2.使用像lower()和upper()这样的函数会给你一个顺序扫描。它不能使用索引。在我的测试系统上，使用lower()所需的时间比可以使用索引的查询长约2000倍。（测试数据有略多于100k行。）

至少有三种不太常用的解决方案可能更有效。

1. 使用citext模块，它基本上模仿了不区分大小写的数据类型行为。加载该模块后，您可以通过CREATE INDEX ON groups (name::citext);创建一个不区分大小写的索引。(但请参见下文。)
2. 使用不区分大小写的排序规则。这是在初始化数据库时设置的。使用不区分大小写的排序规则意味着您可以接受来自客户端代码的几乎任何格式，并且仍然返回有用的结果。(这也意味着您无法进行区分大小写的查询。)
3. 创建一个函数索引。使用CREATE INDEX ON groups (LOWER(name));创建一个小写字母索引。完成后，您可以利用索引进行查询，例如：SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');或SELECT id FROM groups WHERE LOWER(name) = 'administrator'; 但您必须记住使用LOWER()。

---

citext 模块没有提供真正的不区分大小写的数据类型。相反，它表现得好像每个字符串都是小写的。也就是说，它的行为就像在每个字符串上调用了 lower() 函数，就像上面的第 3 种情况一样。优点是程序员不必记得将字符串转换为小写。但在决定使用 citext 之前，您需要阅读文档中的“字符串比较行为”和“限制”部分。

- Mike Sherrill 'Cat Recall'

---

关于＃1：这不应该是一个问题，因为它将是两个不同的字符串（就像执行col ='a'和col ='b'一样）。关于＃2：正如你所说，你可以在表达式上创建索引，所以这并不是真正的问题。但我同意你的观点，改变排序规则很可能是最好的解决方案。 - Vincent Savard

请问有人可以告诉我PostgreSQL内置的大小写不敏感集合(collation)是什么吗？虽然我在选项中看到了它，但是在网上无法找到关于Postgres大小写不敏感集合的任何信息。 - khorvat

@AnupShah：不，我没有这么说。我没有在Windows上运行PostgreSQL。9.4文档如此说明：“在所有平台上，都可以使用名为default、C和POSIX的排序规则。根据操作系统支持，可能还提供其他排序规则。” 您可以使用select * from pg_collation;查看PostgreSQL认为哪些排序规则可用。 - Mike Sherrill 'Cat Recall'

@Matthieu：这是我所知道的最好的主题介绍（和警告）：Edge Cases to Keep in Mind. Part 1 – Text。 - Mike Sherrill 'Cat Recall'

@Matthieu：Unicode FAQ也很有趣。这里是为什么没有唯一的大写字符…… - Mike Sherrill 'Cat Recall'

Answer 4

你可以使用ILIKE，例如。

SELECT id FROM groups where name ILIKE 'administrator'

- ADJ

---

对我来说它是正确的并且正常工作，我正在使用MAC OS X（Mountain Lion）。 - ADJ

这个方法是可行的，但响应速度较慢。为了快速访问基于计算结果的表格，我建议使用 lower 函数。请参阅更多详细信息。 - Afolabi Olaoluwa

这基本上取决于你是在寻找结果，还是过滤已知的值。在后一种情况下，应该在数据级别上保持单一统一的大小写格式，以便使用等号操作符。个人建议对于类型代码值使用大驼峰命名法。 - Chris Marisic

Answer 5

您也可以了解一下 ILIKE 关键字。它在某些情况下非常有用，尽管它不符合SQL标准。更多信息请参见此处：http://www.postgresql.org/docs/9.2/static/functions-matching.html

- Priidu Neemre

---

这里需要注意恶意用户输入。如果运行类似于email ILIKE 'user-input-email-here'这样的查询语句，请确保对用户输入进行转义。否则，人们可以输入像%这样的字符来匹配任何内容。 - Matt De Leon

@MattDeLeon 你好。说得好。但我想问一下，如果我使用ILIKE和prepared statements，这会保护我免受sql注入吗？ - slevin

关键字ILIKE可以代替LIKE，根据活动语言环境进行不区分大小写的匹配。这不是SQL标准，而是PostgreSQL的扩展功能。在9.3版本中效果非常好。 - Aleksey Deryagin

ILIKE 比 lower(column_name) like %expression% 慢。 - Patryk Imosa

@PatrykImosa：您能否详细说明或展示ILIKE较慢的示例？ - Blama

Answer 6

您也可以使用POSIX正则表达式，例如

SELECT id FROM groups where name ~* 'administrator'

SELECT 'asd' ~* 'AsD' returns t

- James Brown

---

我遇到了同样的问题，我需要在我的 PostgreSQL 数据库上进行不区分大小写的搜索。我考虑将用户输入字符串转换为正则表达式。现在，使用 ~* 而不是 = 或 LIKE 就能完美地解决问题！我不需要创建新的索引、列或其他东西。当然，正则表达式搜索比直接字节比较慢，但我认为对性能的影响不会比处理两组数据（一组小写或大写字母仅用于搜索，然后从另一组检索相应的原始数据）更大。此外，这更加简洁！ - Cyberknight

好的，但是例如如何处理regexp_matches()呢？ - WKT

根据PostgreSQL文档： 运算符相当于LIKE，而*对应于ILIKE。 还有!和!*运算符，分别表示NOT LIKE和NOT ILIKE。 所有这些运算符都是特定于PostgreSQL的。 - sh4

当文本中包含括号时，我遇到了一个问题，它无法正常工作。例如："code (LC)"。 - Oshan Wisumperuma

遇到了特殊字符的问题。 - Ben Walton

Answer 7

使用 ILIKE

select id from groups where name ILIKE 'adminstration';

如果您来自expressjs背景，且名称是变量，请使用：

select id from groups where name ILIKE $1;

- MUGABA

---

直接匹配和 ILIKE 之间的性能权衡是什么？ - Nikhil VJ

这个想法是确保你不匹配每个字符串变体。为了进入你的观点，时间和空间是相同的。然而，ILIKE进行了深度比较。 - MUGABA

Answer 8

使用~*可以大大提高性能，具有INSTR的功能。

SELECT id FROM groups WHERE name ~* 'adm'

返回姓名包含OR等于'adm'的行。

- Robin Goh

---

嘿，罗宾，欢迎来到 SO。詹姆斯·布朗已经提出了这个解决方案。此外，你提出的答案没有以任何方式利用正则表达式。 - Rafael

~* 不是完全不区分大小写的匹配。它是正则表达式模式匹配。在您的示例中，如果 db 包含 ADM，则 where name ~* 'Adm' 或 where name ~* 'Ad' 将产生结果。请改用 ILIKE。 - nabster

Answer 9

在这种情况下，ILIKE是有效的：

SELECT id 
  FROM groups
 WHERE name ILIKE 'Administrator'

- samzna

Answer 10

如果你不仅想要大小写转换，还想要重音符号的转换，你可以实现自己的函数：

CREATE EXTENSION unaccent;

CREATE OR REPLACE FUNCTION lower_unaccent(input text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
BEGIN
    return lower(unaccent(input));
END;
$function$;

调用就是这样。

select lower_unaccent('Hôtel')
>> 'hotel'

- pink_demon