问题描述
我正在使用PostgreSQL 8.4在OS X的Rails中开发一个应用程序.我需要为应用程序设置数据库，以便标准文本查询不区分大小写.例如:
I'm developing an app in Rails on OS X using PostgreSQL 8.4. I need to setup the database for the app so that standard text queries are case-insensitive. For example:
应返回与以下结果相同的结果:
should return the same result as:
请明确一点，我不想使用:
Just to be clear, I don't want to use:
(1)喜欢where子句或任何其他类型的特殊比较运算符
(1) LIKE in the where clause or any other type of special comparison operators
(2)列数据类型或任何其他特殊列索引的文本
(2) citext for the column datatype or any other special column index
(3)任何类型的全文本软件，例如Sphinx
(3) any type of full-text software like Sphinx
我想要的是设置数据库区域设置以支持不区分大小写的文本比较.我在Mac OS X(10.5 Leopard)上，并且已经尝试将编码"设置为"LATIN1"，而归类"和"Ctype"都设置为"en_US.ISO8859-1".到目前为止没有成功.
What I do want is to set the database locale to support case-insensitive text comparison. I'm on Mac OS X (10.5 Leopard) and have already tried setting the Encoding to "LATIN1", with the Collation and Ctype both set to "en_US.ISO8859-1". No success so far.
任何帮助或建议，我们将不胜感激.
Any help or suggestions are greatly appreciated.
谢谢！
更新
出于对响应者的尊重，我已将给出的答案之一标记为正确答案.但是，我选择的解决方案与建议的解决方案不同.在进一步审查该应用程序之后，只有少数几个实例需要与数据库字段进行不区分大小写的比较，因此，我将为需要比较大小写的字段创建 shadow 数据库字段.麻木不仁.例如，name和name_lower.我相信我在网上某个地方遇到过这种解决方案.希望PostgreSQL将允许使用与SQL Server将来提供的类似排序规则选项(即DOCI).
I have marked one of the answers given as the correct answer out of respect for the folks who responded. However, I've chosen to solve this issue differently than suggested. After further review of the application, there are only a few instances where I need case-insensitive comparison against a database field, so I'll be creating shadow database fields for the ones I need to compare case-insensitively. For example, name and name_lower. I believe I came across this solution on the web somewhere. Hopefully PostgreSQL will allow similar collation options to what SQL Server provides in the future (i.e. DOCI).
特别感谢所有答复.
推荐答案
您可能需要做一些事情，例如使用列函数来转换文本，例如转换为大写-示例:
You will likely need to do something like use a column function to convert your text e.g. convert to uppercase - an example :
SELECT * FROM documents WHERE upper(title) = upper('incredible document')
请注意，这可能会破坏使用索引扫描的性能，但是如果出现问题，则可以在目标列上定义包括列函数的索引，例如
Note that this may mess up performance that used index scanning, but if it becomes a problem you can define an index including column functions on target columns e.g.
CREATE INDEX I1 on documents (upper(title))
这篇关于在PostgreSQL数据库中使用特定语言环境的人是否成功，所以文本比较不区分大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！