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.