The citext
module provides a case-insensitive
character string type, citext
. Essentially, it internally calls
lower
when comparing values. Otherwise, it behaves almost
exactly like text
.
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.
Here's a simple example of usage:
CREATE TABLE users ( nick CITEXT PRIMARY KEY, pass TEXT NOT NULL ); INSERT INTO users VALUES ( 'larry', md5(random()::text) ); INSERT INTO users VALUES ( 'Tom', md5(random()::text) ); INSERT INTO users VALUES ( 'Damian', md5(random()::text) ); INSERT INTO users VALUES ( 'NEAL', md5(random()::text) ); INSERT INTO users VALUES ( 'Bjørn', md5(random()::text) ); SELECT * FROM users WHERE nick = 'Larry';
The SELECT
statement will return one tuple, even though
the nick
column was set to larry
and the query
was for Larry
.
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_match()
regexp_matches()
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.
David E. Wheeler <david@kineticode.com>
Inspired by the original citext
module by Donald Fraser.