The CITEXT data type represents a case-insensitive string. Like STRING values, CITEXT values preserve their casing when stored and retrieved. Unlike STRING values, comparisons between CITEXT values are case-insensitive for all Unicode characters that have a defined uppercase/lowercase mapping (e.g., 'É' = 'é').
Equality operators (=, !=, <>) and ordering operators (<, >, etc.) treat CITEXT values as case-insensitive by default. Refer to the example.
CITEXT compares values as a STRING column with the und-u-ks-level2 collation, meaning it is case-insensitive but accent-sensitive.
Syntax
To declare a CITEXT column, use the type name directly in your CREATE TABLE statement:
CREATE TABLE logins (
name CITEXT PRIMARY KEY,
email TEXT NOT NULL
);
Size
As with STRING, CITEXT values should be kept below 64 KB for best performance. Because CITEXT values resort to a collation engine on every comparison, CITEXT columns and indexes consume marginally more CPU and memory than their STRING equivalents.
Example
Create and populate a table:
CREATE TABLE logins (
username CITEXT,
email STRING
);
INSERT INTO logins VALUES
('Roach', 'Roach@example.com'),
('lincoln', 'lincoln@example.com');
Because CITEXT comparisons are case-insensitive, an equality predicate matches regardless of letter case:
SELECT * FROM logins WHERE username = 'roach';
username | email
-----------+--------------------
Roach | Roach@example.com
(1 row)
An ordering comparison is also case-insensitive with CITEXT:
SELECT username FROM logins WHERE username < 'Xavi';
username
------------
Roach
lincoln
(2 rows)
For case-sensitive comparisons on CITEXT values, cast to STRING explicitly. In the default Unicode ordering, an uppercase value is considered less than the lowercase value in the table:
SELECT username FROM logins WHERE username::STRING < 'Xavi';
username
------------
Roach
(1 row)
