citext

data type for case-insensitive character strings

https://www.postgresql.org/docs/current/citext.html

Overview

Package	Version	Category	License	Language
`citext`	`1.6`	TYPE	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
3980	`citext`	No	Yes	No	Yes	Yes	No	-

Related	`unaccent` `icu_ext` `pg_trgm` `pg_xenophile` `gb18030_2022` `pg_cjk_parser` `dict_xsyn` `pgroonga` `pg_tokenizer` `pgpcre`

Version

PG18	PG17	PG16	PG15	PG14
1.6	1.6	1.6	1.6	1.6

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION citext;

Usage

citext: case-insensitive character string type

The citext extension provides a case-insensitive text type that eliminates the need for lower() calls in queries.

CREATE EXTENSION citext;

Basic Usage

CREATE TABLE users (
    nick citext PRIMARY KEY,
    pass text NOT NULL
);

INSERT INTO users VALUES ('Larry', 'secret123');

-- Case-insensitive matching
SELECT * FROM users WHERE nick = 'larry';   -- matches 'Larry'
SELECT * FROM users WHERE nick = 'LARRY';   -- matches 'Larry'

Behavior

citext performs comparisons by internally converting strings to lowercase. The following operations are case-insensitive with citext:

Comparison operators: =, <>, <, >, <=, >=
Pattern matching: LIKE, ILIKE, ~~, ~~*
Regular expressions: ~, ~*, !~, !~*

Case-Insensitive Functions

When arguments are citext, these functions perform case-insensitive matching:

regexp_match(), regexp_matches(), regexp_replace(), regexp_split_to_array(), regexp_split_to_table(), replace(), split_part(), strpos(), translate()

Advantages Over lower()

Eliminates verbose lower() calls in WHERE clauses
Supports case-insensitive PRIMARY KEY and UNIQUE constraints
No need for functional indexes
Transparent case-folding in all operations

Limitations

Case-folding depends on LC_CTYPE at database creation
Slightly less efficient than text (copying and conversion overhead)
Does not support B-tree deduplication
For better Unicode handling, consider nondeterministic collations instead