如何在PostgreSQL中创建仅基于小写字母的索引？

Question

如何基于小写字符设置索引？

即使实际字段包含大写字母和小写字母。

另外，我是否可以运行查询并仅返回小写索引值？

- Paul Townsend

Answer 1

你可以创建索引并将字段转换为大写或小写。然后在进行查询时，可以进行相同的转换，这样它就会执行正确的操作。

因此：

CREATE UNIQUE INDEX lower_case_username ON users ((lower(username)));

然后查询同样的东西：

SELECT username FROM users WHERE lower(username) = 'bob';

- Pointy

---

我不知道 "\d public.members" 是什么意思。我正在使用8.4版本——我已经在另一个测试表上使其工作了。 - Paul Townsend

另外，请确保将您的绑定变量设置为小写；-) - atrain

请原谅我，但是如果您在查询中必须使用lower()函数，那么创建小写索引的意义何在呢？使用lower会防止Postgres使用索引。 - 111

不需要，PostgreSQL 足够聪明，能够识别函数应用程序是否符合索引的定义。参考链接 - Pointy

很棒的答案。运行得很好。 - zumzum

Answer 2

根据文档，您可以这样做：

CREATE UNIQUE INDEX lower_title_idx ON films ((lower(title)));

- mezzie

---

感谢您的帮助！这是我的SQL语句：CREATE UNIQUE INDEX "lcase_idx" ON "public"."members" USING btree ((lower(Memb_Name)));错误：列“memb_name”不存在。似乎并不是使用Memb_Name的小写来构建索引，而是lower函数只是将所引用字段的名称变成小写。我尝试过在Memb_Name周围加上引号和不加引号。 - Paul Townsend

PS：我似乎工作正常...只有当字段名称全部小写时。 - Paul Townsend

Answer 3

你也可以用通配符搜索来实现这个功能：

CREATE INDEX IF NOT EXISTS foo_table_bar_field ON foo_table(lower(username))

如此查询：

SELECT * FROM foo_table WHERE lower(username) like 'bob%'

- Chris

Answer 4

CREATE UNIQUE INDEX my_index_name ON my_table (LOWER(my_field));

- Adrian Smith

---

感谢您的帮助！这是我的SQL语句：CREATE UNIQUE INDEX "lcase_idx" ON "public"."members" USING btree ((lower(Memb_Name)));ERROR: column "memb_name" does not exist. 似乎不是使用Memb_Name的小写来构建索引，而是lower函数将所引用的字段名变成了小写。我尝试在Memb_Name周围加上和没有引号，但结果相同。 - Paul Townsend

PS：我似乎工作得很好...只有当字段名称全部小写时。 - Paul Townsend

这是十年后的事了，但据我所知，PostgreSQL假定符号名称为小写，除非你明确地引用它们。在你的情况下，你需要使用 lower("Memb_Name")。 - Kiran Jonnalagadda