| F.9. citext — a case-insensitive character string type | ||||
|---|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules and Extensions | Home | Next |
F.9. citext — a case-insensitive character string type#
Thecitext module provides a case-insensitive character string type,citext. Essentially, it internally callslower when comparing values. Otherwise, it behaves almost exactly liketext.
Tip
Consider usingnondeterministic collations (seeSection 23.2.2.4) instead of this module. They can be used for case-insensitive comparisons, accent-insensitive comparisons, and other combinations, and they handle more Unicode special cases correctly.
This module is considered“trusted”, that is, it can be installed by non-superusers who haveCREATE privilege on the current database.
F.9.1. Rationale#
The standard approach to doing case-insensitive matches inPostgreSQL has been to use thelower 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
loweron 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
UNIQUEorPRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively.
Thecitext data type allows you to eliminate calls tolower in SQL queries, and allows a primary key to be case-insensitive.citext is locale-aware, just liketext, which means that the matching of upper case and lower case characters is dependent on the rules of the database'sLC_CTYPE setting. Again, this behavior is identical to the use oflower 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.
F.9.2. 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', sha256(random()::text::bytea) );INSERT INTO users VALUES ( 'Tom', sha256(random()::text::bytea) );INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );INSERT INTO users VALUES ( 'NEAL', sha256(random()::text::bytea) );INSERT INTO users VALUES ( 'Bjørn', sha256(random()::text::bytea) );SELECT * FROM users WHERE nick = 'Larry';
TheSELECT statement will return one tuple, even though thenick column was set tolarry and the query was forLarry.
F.9.3. String Comparison Behavior#
citext performs comparisons by converting each string to lower case (as thoughlower were called) and then comparing the results normally. Thus, for example, two strings are considered equal iflower would produce identical results for them.
In order to emulate a case-insensitive collation as closely as possible, there arecitext-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 tocitext: they both match case-insensitively. The same is true for!~ and!~*, as well as for theLIKE operators~~ and~~*, and!~~ and!~~*. If you'd like to match case-sensitively, you can cast the operator's arguments totext.
Similarly, all of the following functions perform matching case-insensitively if their arguments arecitext:
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 totext before using one of these functions if you want case-sensitive behavior.
F.9.4. Limitations#
citext's case-folding behavior depends on theLC_CTYPEsetting 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 withcitext'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 ofPostgreSQL 9.1, you can attach a
COLLATEspecification tocitextcolumns or data values. Currently,citextoperators will honor a non-defaultCOLLATEspecification while comparing case-folded strings, but the initial folding to lower case is always done according to the database'sLC_CTYPEsetting (that is, as thoughCOLLATE "default"were given). This may be changed in a future release so that both steps follow the inputCOLLATEspecification.citextis not as efficient astextbecause the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. Also, onlytextcan support B-Tree deduplication. However,citextis slightly more efficient than usinglowerto get case-insensitive matching.citextdoesn'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 thetexttype and manually use thelowerfunction 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 ascitextand explicitly casting the column totextwhen 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
citextoperators must be in the currentsearch_path(typicallypublic); if it is not, the normal case-sensitivetextoperators will be invoked instead.The approach of lower-casing strings for comparison does not handle some Unicode special cases correctly, for example when one upper-case letter has two lower-case letter equivalents. Unicode distinguishes betweencase mapping andcase folding for this reason. Use nondeterministic collations instead of
citextto handle that correctly.
F.9.5. Author#
David E. Wheeler<david@kineticode.com>
Inspired by the originalcitext module by Donald Fraser.