PostgreSQL：如何进行“不区分大小写”查询

本文翻译自：PostgreSQL: How to make “case-insensitive” query

Is there any way to write case-insensitive queries in PostgreSQL, Eg I want that following 3 queries return same result. 有什么办法可以在PostgreSQL中编写不区分大小写的查询，例如，我希望以下3个查询返回相同的结果。

SELECT id FROM groups where name='administrator'

SELECT id FROM groups where name='ADMINISTRATOR'

SELECT id FROM groups where name='Administrator'

---

#1楼

参考：https://stackoom.com/question/tooK/PostgreSQL-如何进行-不区分大小写-查询

---

#2楼

You can also read up on the ILIKE keyword. 您也可以阅读ILIKE关键字。 It can be quite useful at times, albeit it does not conform to the SQL standard. 尽管它不符合SQL标准，但有时可能会非常有用。 See here for more information: http://www.postgresql.org/docs/9.2/static/functions-matching.html 请参阅此处以获取更多信息： http : //www.postgresql.org/docs/9.2/static/functions-matching.html

---

#3楼

You can use ILIKE . 您可以使用ILIKE 。 ie 即

SELECT id FROM groups where name ILIKE 'administrator'

---

#4楼

使用ILIKE代替LIKE

SELECT id FROM groups WHERE name ILIKE 'Administrator'

---

#5楼

You could also use POSIX regular expressions, like 您还可以使用POSIX正则表达式，例如

SELECT id FROM groups where name ~* 'administrator'

SELECT 'asd' ~* 'AsD' returns t SELECT 'asd' ~* 'AsD'返回t

---

#6楼

Using ~* can improve greatly on performance, with functionality of INSTR. 使用~*可以通过INSTR的功能极大地提高性能。

SELECT id FROM groups WHERE name ~* 'adm'

return rows with name that contains OR equals to 'adm'. 返回名称包含OR的行等于“ adm”。