Anyone had success using a specific locale for a PostgreSQL database so that text comparison is case-insensitive?

Question

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:

SELECT * FROM documents WHERE title = 'incredible document'

should return the same result as:

SELECT * FROM documents WHERE title = 'Incredible Document'

Just to be clear, I don't want to use:

(1) LIKE in the where clause or any other type of special comparison operators

(2) citext for the column datatype or any other special column index

(3) any type of full-text software like Sphinx

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.

Thanks!

Update

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).

Special thanks to all who responded.

Answer 1

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))

Answer 2

With all the limitations you have set, possibly the only way to make it work is to define your own = operator for text. It is very likely that it will create other problems, such as creating broken indexes. Other than that, your best bet seems to be to use the citext datatype; that would still let the ORM stuff you're using generate the SQL.

(I am not mentioning the possibility of creating your own locale definition because I haven't ever heard of anyone doing it.)