LLM Best Practices

citext

2 min read

View markdown source

Definition

citext (case-insensitive text) is a PostgreSQL extension data type in contrib/citext. It stores the original case but compares and sorts case-insensitively using lower() semantics. Equality, LIKE, ILIKE, ORDER BY, and unique constraints all operate case-insensitively.

Without citext, case-insensitive lookups require a function-based index:

SELECT * FROM users WHERE lower(email) = lower($1);
CREATE INDEX idx_users_email_lower ON users (lower(email));

With citext, a plain B-tree index works and the query needs no lower():

SELECT * FROM users WHERE email = $1; -- 'user@EXAMPLE.com' = 'user@example.com'
CREATE INDEX idx_users_email ON users (email);

citext comparisons call lower() internally on both sides. This is correct for ASCII and most Latin scripts but may not behave as expected for Turkish or Greek locale-sensitive case folding. For locale-aware case folding, use ICU collations instead.

Unique constraints on citext columns enforce uniqueness case-insensitively.

When it applies

Use citext for email addresses, usernames, slugs, and any column where case-insensitive equality or uniqueness is required. Install the extension once per database with CREATE EXTENSION citext. Avoid citext when you need to preserve and compare the original case (e.g., passwords, tokens, code identifiers).

Example

CREATE EXTENSION IF NOT EXISTS citext;
 
CREATE TABLE users (
  id       bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  email    citext NOT NULL,
  username citext NOT NULL,
  UNIQUE (email),
  UNIQUE (username)
);
 
INSERT INTO users (email, username) VALUES ('Alice@Example.com', 'Alice');
INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice'); -- ERROR: unique violation
 
SELECT * FROM users WHERE email = 'ALICE@EXAMPLE.COM'; -- returns row
  • btree-index - standard B-tree indexes work on citext without function wrapping.
  • query-plan - citext equality uses a standard index scan; no special planning needed.
  • gin-index - use GIN on citext for full-text or ILIKE pattern queries.
  • postgres - the Postgres deep-dive.

Citing this term

See citext (llmbestpractices.com/glossary/citext).

Graph View

Backlinks