citext
citext
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>.
Rationale
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 uppercase and
lowercase 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 datatype, you don't have to remember to do
anything special in your queries.
How to Use It
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>.
Limitations
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.
The pattern-matching comparison operators, such as LIKE>,
~>, ~~>, !~>, !~~>, etc.,
have been overloaded to make case-insensitive comparisons when their
left-hand argument is of type citext>. However, related
functions have not been changed, including:
regexp_replace()>
regexp_split_to_array()>
regexp_split_to_table()>
replace()>
split_part()>
strpos()>
translate()>
Of course, for the regular expression functions, you can specify
case-insensitive comparisons in their flags> arguments, but
the same cannot be done for the the non-regexp functions.
citext> is not as efficient as text> because the
operator functions and the btree 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.
PostgreSQL> supports casting between text>
and any other type (even non-string types) by using the other type's
I/O functions. This doesn't work with citext>. However,
you can cast via I/O functions in two steps, for example
somevalue>::text::citext or
citextvalue>::text::sometype>.
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.
Author
David E. Wheeler david@kineticode.com
Inspired by the original citext> module by Donald Fraser.