CITEXT in Postgres: Case-Insensitive Text Without the LOWER Trap

The most common pattern for case-insensitive text in Postgres is LOWER(email) = LOWER('[email protected]'). It works, but it requires every query and every index to consistently apply LOWER. Forget once and you have a bug.

The alternative is the CITEXT type, which makes case-insensitivity a property of the column, not a discipline applied per query. For workloads where this matters, CITEXT is simpler.

The basics

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email CITEXT NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('[email protected]');
SELECT * FROM users WHERE email = '[email protected]';  -- finds it

The email column compares case-insensitively. The UNIQUE constraint enforces uniqueness regardless of case — '[email protected]' and '[email protected]' are considered the same. Insert second one fails.

No LOWER() calls in queries. No expression indexes. The schema does the work.

How it compares with LOWER() expression indexes

The alternative pattern:

CREATE TABLE users (
  email TEXT NOT NULL
);

CREATE UNIQUE INDEX users_email_lower_unique
  ON users (LOWER(email));

Queries must use LOWER(email) = LOWER($1) to use the index. Forget the LOWER and the index does not match.

CITEXT removes this trap. The query is just email = $1; case insensitivity is automatic.

The trade: CITEXT comparisons are slightly slower than plain text equality (Postgres has to lowercase both sides). For most workloads, the difference is invisible. For very high-frequency lookups on extremely hot indexes, profile to confirm.

What CITEXT does not do

It does not change storage. The original case is preserved. '[email protected]' is stored as '[email protected]', not '[email protected]'. SELECT returns the original value.

It does not affect display. "Hi [email protected]" still shows the original. Use LOWER() in display logic if you want lowercase display.

It does not affect ordering. ORDER BY uses the case-insensitive comparison, but values are returned as stored. Mixed-case values appear in case-insensitive sort order.

It does not handle Unicode collation. CITEXT uses the equivalent of LOWER(), which is locale-dependent. For Turkish-style I/i, German ß, or other Unicode special cases, CITEXT may not produce the case-folding you expect. The Postgres docs specifically warn about this. For full Unicode-aware case folding, ICU collation or a different approach is needed.

When CITEXT is the right call

Email columns. Email addresses are case-insensitive by spec (the local part is technically case-sensitive, but virtually no real-world systems treat it that way). CITEXT is the right type.

Usernames where you want case-insensitive uniqueness. If Alice and alice should not coexist as separate users, CITEXT is the simplest enforcement.

Tag-like fields. Tags often have case-insensitive matching expectations. CITEXT handles it without per-query discipline.

When CITEXT is wrong

Internationalized text where Unicode case folding matters. Use a proper collation instead. ICU collations can do locale-aware case insensitivity that CITEXT cannot.

Performance-critical hot paths where the small overhead matters. Profile. Most workloads will not notice.

Cross-database compatibility. CITEXT is Postgres-specific. Code that needs to work on multiple databases cannot rely on it.

Migrating from TEXT to CITEXT

If you have an existing TEXT column with LOWER() discipline scattered throughout the application:

1. ALTER TABLE users ALTER COLUMN email TYPE CITEXT;
2. The migration rewrites the table — slow on large tables, fast on small ones.
3. Drop the LOWER expression index if it existed.
4. Update application queries to remove explicit LOWER() calls.
5. Verify with a sample of test queries.

The ALTER TABLE is the expensive step on large tables. For a billion-row table, expect hours of write blocking. For most application data, it is minutes.

Modern alternative: case-insensitive collation

Postgres 12+ supports case-insensitive collations via ICU:

CREATE COLLATION case_insensitive
  (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

CREATE TABLE users (
  email TEXT COLLATE case_insensitive NOT NULL
);

The column uses TEXT but compares case-insensitively. This is more standards-compliant than CITEXT and handles Unicode properly.

The tradeoff: requires Postgres 12+ and ICU support (most distributions have it). The collation feature is more flexible than CITEXT but slightly more complex to set up.

For new schemas on modern Postgres, ICU collation is increasingly the better choice. CITEXT is the older, simpler, more compatible option.

What I do today

For a new schema:

• Email and username columns: CITEXT for now (still widely supported), or ICU collation if I am committed to a recent Postgres.
• General text: TEXT with explicit LOWER() in queries where case insensitivity is needed.
• For internationalized data: ICU collation, definitely.

The choice between CITEXT and ICU collation comes down to compatibility. For most applications, both work. For new applications targeting modern Postgres, ICU collation is the more future-proof choice.