如何在PostgreSQL中仅基于小写创建索引？ [英] How do I create an index in PostgreSQL based on lowercase only?

问题描述

我如何仅根据小写设置索引？

How would I set up an index based on lower case only?

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

Even though the actual field contains both upper and lower case letters.

另外，我可以运行查询并且只返回小写索引值吗？

Also, can I run a query and have only the lower case index value returned?

推荐答案

您可以创建索引并将字段转换为大写或小写。然后，当你进行查询时，你可以做同样的转换，它会做正确的事。

You can create the index and transform the field to upper- or lower-case. Then when you do your queries, you can do the same transform and it'll do the right thing.

所以：

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

然后查询同样的事情：

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

这篇关于如何在PostgreSQL中仅基于小写创建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！