数据库中带索引的不区分大小写搜索？

Question

我正在使用Postgres数据库。在我的数据库中有一张Articles表，其中包含一个名为url的列用于存储URL slugs。这样做是为了让我能够将该表中的文章展示在网站上，而不是像“example.com/23323”那样，而是像“example.com/Funny_Thing_Happened_to_Me”这样。实现这个功能很简单，随着文章数量的增加，我在url slugs上添加了一个索引。

但是我后来意识到，虽然我希望在URL中显示大写字母，但我希望它们在用户输入时不区分大小写，并且我希望以不区分大小写的方式强制执行URL的唯一性。

是否有一种简单的方法可以快速地进行不区分大小写的文本列搜索，并以不区分大小写的方式强制执行唯一性？

我尝试过使用类似“lower(url) =”这样的搜索方式，但这会导致Postgres决定不使用索引。

- William Jones

Answer 1

使用函数索引：

CREATE UNIQUE INDEX ix_test1 on articles (lower(url));

如果您使用的是8.4版本，并且可以安装贡献模块，那么请查看citext类型。它将所有的大写/小写内容抽象出来，并且性能稍微更好。请参考citext类型。

- rfusca

---

谢谢，这似乎是正确的方法。唯一的问题是由于现有冲突在 lower(url) 上，我不能直接创建索引，但是我看不到除了修复数据之外的其他解决办法。 - William Jones

@williamjones：暂时将索引设置为非唯一，以获得性能优势。然后修复唯一性，创建一个新的唯一索引，并删除旧的索引。 - rfusca

Answer 2

SELECT * FROM sometable WHERE textfield ILIKE 'value%';

这是您要找的吗？ 您所说的“以不区分大小写的方式强制唯一性”是什么意思？

如果您想坚持使用“lower()”，可以选择以下内容：

SELECT * FROM sometable WHERE UPPER(textfield) LIKE (UPPER('value') || '%');

- Matt N.