如何仅基于小写在 PostgreSQL 中创建索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3980050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create an index in PostgreSQL based on lowercase only?
提问by Paul Townsend
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?
另外，我可以运行查询并只返回小写的索引值吗？
回答by Pointy
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.
您可以创建索引并将字段转换为大写或小写。然后当你做你的查询时，你可以做同样的转换，它会做正确的事情。
So:
所以：
CREATE UNIQUE INDEX lower_case_username ON users ((lower(username)));
Then query for the same thing:
然后查询同样的事情：
SELECT username FROM users WHERE lower(username) = 'bob';
回答by mezzie
回答by Adrian Smith
CREATE UNIQUE INDEX my_index_name ON my_table (LOWER(my_field));
回答by Chris
You can also use this for wildcard searches:
您还可以将其用于通配符搜索：
CREATE INDEX IF NOT EXISTS foo_table_bar_field ON foo_table(lower(username))
Query like so:
像这样查询：
SELECT * FROM foo_table WHERE lower(username) like 'bob%'
