citext

What citext does

citext is internally a text type with case-insensitive comparison operators. When you compare two citext values, PostgreSQL folds both to lowercase before comparing. The stored value retains its original case — only comparisons are affected.

This means a column declared as citext quietly resolves the most common case-sensitivity headaches: duplicate emails with different casing, login lookups that fail because the user typed "Alice" instead of "alice", and UNIQUE constraints that let both Bob@example.com and bob@example.com slip through. The elegance is that you solve it once, at the type level, rather than remembering to apply lower() in every query that touches the column. One of those approaches scales. The other produces bug reports.

When to use citext

citext is a good fit for columns where case-insensitive uniqueness and lookup are the default expectation:

• Email addresses — the RFC specifies the local part as case-sensitive, but in practice every email provider treats it as case-insensitive
• Usernames — "Alice" and "alice" should be the same user
• URL slugs — /About and /about should resolve to the same page
• Country codes, currency codes, tags — short string identifiers where case variation is noise

citext is less appropriate when you need case-sensitive behavior some of the time, or when you are dealing with non-ASCII text where locale-aware case folding matters (Turkish İ/i, German ß, Greek final sigma). For those cases, nondeterministic collations or application-level normalization are better options.

Installation and setup

citext is a contrib module that ships with PostgreSQL — no extra packages to install. It is a trusted extension, so any user with CREATE privilege on the database can enable it. No server restart is required.

-- citext is a contrib module — no extra packages needed
CREATE EXTENSION IF NOT EXISTS citext;

-- Verify it's installed
SELECT extname, extversion FROM pg_extension WHERE extname = 'citext';

Practical examples

Basic usage

Declare a column as citext and queries match case-insensitively without any extra syntax.

-- Create a table with a case-insensitive email column
CREATE TABLE users (
  id serial PRIMARY KEY,
  email citext NOT NULL UNIQUE,
  display_name text NOT NULL
);

-- Insert some rows
INSERT INTO users (email, display_name) VALUES
  ('Alice@Example.com', 'Alice'),
  ('bob@example.com', 'Bob');

-- This query matches regardless of case — no lower() needed
SELECT * FROM users WHERE email = 'alice@example.com';
--  id |       email        | display_name
-- ----+--------------------+--------------
--   1 | Alice@Example.com  | Alice

Case-insensitive uniqueness

UNIQUE constraints on citext columns prevent duplicates regardless of casing — the most common reason people reach for this extension.

-- The UNIQUE constraint is case-insensitive automatically
INSERT INTO users (email, display_name) VALUES ('ALICE@EXAMPLE.COM', 'Alice2');
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(ALICE@EXAMPLE.COM) already exists.

Comparison with lower() approach

Without citext, you need functional indexes and must remember to wrap every comparison in lower(). Every query, every developer, every time. citext eliminates both requirements.

-- Without citext: you must remember lower() everywhere
CREATE INDEX idx_users_email_lower ON users_legacy (lower(email));
SELECT * FROM users_legacy WHERE lower(email) = lower('Alice@Example.com');

-- With citext: comparisons are case-insensitive by default
SELECT * FROM users WHERE email = 'Alice@Example.com';

-- Standard B-tree indexes work directly on citext columns
CREATE INDEX idx_users_email ON users (email);

Limitations

• Locale-dependent case folding — citext uses the database's LC_CTYPE setting to determine case folding. It calls the C library's tolower() function, which does not handle all Unicode special cases correctly (e.g., the Turkish dotless-i, or characters where one uppercase letter maps to two lowercase letters).
• No case-sensitive fallback — all comparisons on a citext column are case-insensitive. You must cast to text for case-sensitive behavior.
• No B-tree deduplication — only the text type supports B-tree deduplication (PostgreSQL 13+). citext indexes may be slightly larger when there are many duplicate values.
• Mixed-type comparisons — when one side of an operator is citext and the other is text, the comparison is case-insensitive. This can cause surprises if you are not aware of the implicit behavior.

Cloud availability

How Gold Lapel relates

I should mention that Gold Lapel recognizes citext columns and accounts for their case-insensitive comparison semantics during query analysis. No special configuration on your part — the proxy sees the type and adjusts accordingly. Index recommendations work the same as they do for regular text columns.