The standard approach to doing case-insensitive matches in
PostgreSQL has been to use the
lower function when comparing
values, for example

SELECT * FROM tab WHERE lower(col) = LOWER(?);

This works reasonably well, but has a number of
drawbacks:

It makes your SQL statements verbose, and you always
have to remember to use lower
on both the column and the query value.

It won't use an index, unless you create a functional
index using lower.

If you declare a column as UNIQUE or PRIMARY
KEY, the implicitly generated index is case-sensitive.
So it's useless for case-insensitive searches, and it won't
enforce uniqueness case-insensitively.

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.

citext performs comparisons by
converting each string to lower case (as though lower were called) and then comparing the
results normally. Thus, for example, two strings are considered
equal if lower would produce
identical results for them.

In order to emulate a case-insensitive collation as closely
as possible, there are citext-specific
versions of a number of string-processing operators and
functions. So, for example, the regular expression operators
~ and ~*
exhibit the same behavior when applied to citext: they both match case-insensitively. The
same is true for !~ and !~*, as well as for the LIKE operators ~~ and
~~*, and !~~
and !~~*. If you'd like to match
case-sensitively, you can cast the operator's arguments to
text.

Similarly, all of the following functions perform matching
case-insensitively if their arguments are citext:

regexp_replace()

regexp_split_to_array()

regexp_split_to_table()

replace()

split_part()

strpos()

translate()

For the regexp functions, if you want to match
case-sensitively, you can specify the "c" flag to force a case-sensitive match.
Otherwise, you must cast to text before
using one of these functions if you want case-sensitive
behavior.

citext's case-folding behavior
depends on the LC_CTYPE setting of
your database. How it compares values is therefore
determined when the database is created. It is not truly
case-insensitive in the terms defined by the Unicode
standard. Effectively, what this means is that, as long as
you're happy with your collation, you should be happy with
citext's comparisons. But if you have
data in different languages stored in your database, users
of one language may find their query results are not as
expected if the collation is for another language.

As of PostgreSQL 9.1,
you can attach a COLLATE
specification to citext columns or
data values. Currently, citext
operators will honor a non-default COLLATE specification while comparing
case-folded strings, but the initial folding to lower case
is always done according to the database's LC_CTYPE setting (that is, as though
COLLATE "default" were given).
This may be changed in a future release so that both steps
follow the input COLLATE
specification.

citext is not as efficient as
text because the operator functions
and the B-tree comparison functions must make copies of the
data and convert it to lower case for comparisons. It is,
however, slightly more efficient than using lower to get case-insensitive
matching.

citext doesn't help much if you
need data to compare case-sensitively in some contexts and
case-insensitively in other contexts. The standard answer
is to use the text type and manually
use the lower function when
you need to compare case-insensitively; this works all
right if case-insensitive comparison is needed only
infrequently. If you need case-insensitive behavior most of
the time and case-sensitive infrequently, consider storing
the data as citext and explicitly
casting the column to text when you
want case-sensitive comparison. In either situation, you
will need two indexes if you want both types of searches to
be fast.

The schema containing the citext
operators must be in the current search_path (typically public); if it is not, the normal
case-sensitive text operators will be
invoked instead.