F.6. 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.

F.6.1. 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:

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.

F.6.2. How to Use It

Here's a simple example of usage:

    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.

F.6.3. String Comparison Behavior

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:

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.

F.6.4. Limitations

F.6.5. Author

David E. Wheeler

Inspired by the original citext module by Donald Fraser.