Case insensitive comparisons in PostgreSQL
In Postgres, text comparisons are by default case sensitive, which can be a pain for things like browse filters.
Mike Hanson points out the citext data type, a Postgres extension. From the docs:
The citext data type allows you to eliminate calls to lower
in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the rules of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower
in queries. But because it's done transparently by the data type, you don't have to remember to do anything special in your queries.
I've used functional indexes in the past to help deal with case sensitivity problems, but I like this approach better.
To use the citext type, you must first load extension (or functions) into your database. In version 9.1 or later:
CREATE EXTENSION citext [WITH SCHEMA foo] [NO USER DATA];
In versions before 9.1, you must add all the functions individually: citext.sql.