CITEXT

The CITEXT 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 , 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', '[e-mail]'),
('lincoln', '[e-mail]');

Because CITEXT comparisons are case-insensitive, an equality predicate matches regardless of letter case:

SELECT * FROM logins WHERE username = 'roach';

  username |       email
-----------+--------------------
  Roach    | [e-mail]
(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)

Known limitations

• CITEXT types are not currently compatible with the LIKE and ILIKE operators.

See also