PostgreSQL 9.0.23 Documentation | ||||
---|---|---|---|---|
Prev | Up | Appendix F. Additional Supplied Modules | Next |
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 comparison of
upper case and lower case characters is dependent on the rules
of the LC_CTYPE locale 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.
In order to emulate a case-insensitive collation as closely as possible, there are citext-specific versions of a number of the comparison operators and functions. So, for example, the regular expression operators ~ and ~* exhibit the same behavior when applied to citext: they both compare 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 always cast to text before comparing.
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 behavior depends on the LC_CTYPE setting of your database. How it compares values is therefore determined when initdb is run to create the cluster. 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.
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 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, a normal case-sensitive text comparison is performed.
David E. Wheeler <david@kineticode.com>
Inspired by the original citext module by Donald Fraser.