Module 7: Useful Extensions

`citext` vs `LOWER()`: case-insensitive text

Email, username, product code, identifiers. All cases where john@example.com and JOHN@EXAMPLE.COM are the same value. PostgreSQL offers two ways to handle case-insensitivity: LOWER() (manual) and citext (extension). This capsule gives you the decision matrix with contrasted code.

The problem without the extension

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(200) NOT NULL,
    name VARCHAR(100)
);

INSERT INTO users (email, name) VALUES ('John@Example.com', 'John');
INSERT INTO users (email, name) VALUES ('JOHN@example.com', 'John2');  -- Duplicate?
INSERT INTO users (email, name) VALUES ('john@example.com', 'John3');  -- Duplicate?

Without a constraint, they all go through. But they should be duplicates.

For queries:

-- Find the user — depends on the case the client sends
SELECT * FROM users WHERE email = 'john@example.com';
-- Returns only 1 row (not the 3)

Typical bug: a user signs up with John@Example.com, then tries to log in with john@example.com, and isn't found.

Approach 1: `LOWER()` everywhere

-- Insert normalized
INSERT INTO users (email, ...) VALUES (LOWER('John@Example.com'), ...);

-- Query with LOWER on both sides
SELECT * FROM users WHERE email = LOWER('john@example.com');

-- UNIQUE constraint based on LOWER
CREATE UNIQUE INDEX uq_users_email_lower ON users (LOWER(email));

Pros:

No extension, vanilla PostgreSQL.
Available on ALL providers.

Cons:

❌ You have to remember LOWER() in every query. Forget one = bug.
❌ The email is stored in lowercase — you lose the original case (John becomes john).
❌ The index expression is more complex to maintain.
❌ ORM queries may not use the index if they don't include LOWER().

Approach 2: `citext`

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email CITEXT NOT NULL UNIQUE,  -- type-aware case-insensitive
    name VARCHAR(100)
);

-- Transparent behavior
INSERT INTO users (email, name) VALUES ('John@Example.com', 'John');
INSERT INTO users (email, name) VALUES ('JOHN@example.com', 'John2');
-- ERROR: duplicate key — works as you'd expect

-- Simple query — automatic case-insensitive
SELECT * FROM users WHERE email = 'JOHN@EXAMPLE.COM';
-- Returns the row even though it was stored as 'John@Example.com'

-- But the SELECT shows the original case
SELECT email FROM users WHERE id = 1;
-- 'John@Example.com' — preserved as it was entered

Pros:

✅ Cleaner queries — no LOWER() in the code.
✅ Original case preserved on SELECT. Better UX.
✅ Impossible to forget case-insensitivity in queries.
✅ The UNIQUE constraint works naturally.

Cons:

❌ Extension — check availability on your cloud provider (most have it).
❌ Small comparison overhead (internal case folding).

Decision matrix

Case	Recommendation
Email	`citext` — always. Case-insensitivity is semantically correct.
Username	`citext` — users don't expect duplicates by case.
Product code	`citext` if the codes are normalized, plain varchar if not.
Proper names	VARCHAR normal — `John Smith` and `john smith` are different people.
URLs	VARCHAR normal — case can matter (path component).
Hashes / tokens	VARCHAR normal — case matters, they're binary.
Tags	`citext` generally — `Python` and `python` should be the same tag.

Simple rule: if "X" and "x" should be the same value for the business → citext. If not → VARCHAR.

Implementation with SQLAlchemy 2.0

from sqlalchemy.dialects.postgresql import CITEXT
from sqlalchemy.orm import Mapped, mapped_column


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(CITEXT, unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100))


# Query
user = await session.scalar(
    select(User).where(User.email == "JOHN@EXAMPLE.COM")
)
# Automatic case-insensitive match

Migration:

# alembic/versions/XXX_use_citext_for_email.py
def upgrade() -> None:
    op.execute("CREATE EXTENSION IF NOT EXISTS citext")
    op.alter_column(
        'users', 'email',
        type_=postgresql.CITEXT(),
        existing_type=sa.String(200),
    )


def downgrade() -> None:
    op.alter_column(
        'users', 'email',
        type_=sa.String(200),
        existing_type=postgresql.CITEXT(),
    )

ALTER COLUMN ... TYPE requires rewriting the column — for large tables, consider a zero-downtime migration (module 5 of guide #13).

Comparison: queries before/after

Without citext (with LOWER)

# Search
user = await session.scalar(
    select(User).where(func.lower(User.email) == email_input.lower())
)

# Insert
new_user = User(email=email_input.lower(), name=name)

# Duplicate validation
existing = await session.scalar(
    select(User).where(func.lower(User.email) == new_email.lower())
)
if existing:
    raise EmailAlreadyExists()

Every spot requires lower(). Forget one = bug.

With citext

# Search
user = await session.scalar(
    select(User).where(User.email == email_input)
)

# Insert (no need to normalize — citext does it)
new_user = User(email=email_input, name=name)

# Validation
existing = await session.scalar(
    select(User).where(User.email == new_email)
)
if existing:
    raise EmailAlreadyExists()

Cleaner, with no chance of error.

Real case: refactoring an auth system

Suppose your current app uses:

class User(Base):
    email: Mapped[str] = mapped_column(String(200), unique=True)

And a bug is reported: user Jane@Example.com can't recover their password because the reset email doesn't match (the client sends it in lowercase).

Refactor to citext:

Add the extension:

op.execute("CREATE EXTENSION IF NOT EXISTS citext")

Change the type:

op.alter_column('users', 'email', type_=CITEXT())

Update the model:

email: Mapped[str] = mapped_column(CITEXT, unique=True)

Remove LOWER() from queries — automatic now.
Test:

async def test_email_case_insensitive(session):
    session.add(User(email="Test@Example.com"))
    await session.commit()

    # Search with a different case
    user = await session.scalar(
        select(User).where(User.email == "TEST@EXAMPLE.COM")
    )
    assert user is not None
    assert user.email == "Test@Example.com"  # original case preserved

Bug solved. Cleaner code. Better UX (original case preserved).

Variants and traps

`citext` with a GIN index for fuzzy

citext doesn't solve fuzzy/partial matching — only case. For LIKE '%john%' case-insensitive, you need pg_trgm (capsule 06).

Performance overhead

citext does case folding on comparison. Small overhead per query. In benchmarks, ~5-10% slower than VARCHAR for pure comparisons. Negligible in most cases.

`citext` with ORDER BY

SELECT email FROM users ORDER BY email;
-- Order is case-insensitive (alphabetical, case-agnostic)

John and jane sort correctly regardless of case.

Traps and common mistakes

1. citext without installing the extension.

CREATE TABLE with CITEXT fails if the extension isn't active. The migration must install it first.

2. ALTER from VARCHAR to CITEXT on a large table with downtime.

ALTER COLUMN TYPE rewrites the table. For 10M rows, it locks for minutes. Zero-downtime requires expand-contract.

3. Mixing citext comparisons with VARCHAR.

-- email is CITEXT, name is VARCHAR
SELECT * FROM users WHERE email = name;
-- ERROR: incompatible types

Explicit cast: email::text = name.

4. citext with a foreign key.

-- Table A: email CITEXT
-- Table B: email_ref VARCHAR REFERENCES A(email)
-- ❌ Incompatible types

If you reference it, both sides CITEXT.

5. JSON stringification.

{"email": "John@Example.com"}  # Pydantic

Pydantic doesn't know the field is CITEXT. It treats it as a plain string. It only matters when it hits the DB. No issue.

6. Forgetting that CHAR/VARCHAR in a query generates a CAST.

WHERE email = 'john@example.com'  -- 'john@example.com' is text, cast to citext, OK
WHERE email = 'john'::varchar  -- explicit cast, may cause a non-case-insensitive comparison

Generally PG handles it well implicitly. If something's odd, check the casts.

7. Index over LOWER(citext) — redundant.

-- ❌ Not needed — citext is already case-insensitive
CREATE INDEX ON users (LOWER(email));  -- redundant

-- ✅ Normal index
CREATE INDEX ON users (email);

Exercise: compare approaches

Setup: two tables, one with VARCHAR + LOWER(), another with CITEXT.

CREATE TABLE users_lower (
    id SERIAL PRIMARY KEY,
    email VARCHAR(200) NOT NULL,
    UNIQUE (email)
);
-- For case-insensitive UNIQUE:
CREATE UNIQUE INDEX uq_users_lower_email ON users_lower (LOWER(email));


CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users_citext (
    id SERIAL PRIMARY KEY,
    email CITEXT NOT NULL UNIQUE
);


-- Insert the same email with different cases
INSERT INTO users_lower (email) VALUES ('Test@Example.com');
INSERT INTO users_lower (email) VALUES ('TEST@EXAMPLE.COM');
-- Does it fail?

INSERT INTO users_citext (email) VALUES ('Test@Example.com');
INSERT INTO users_citext (email) VALUES ('TEST@EXAMPLE.COM');
-- Does it fail?

Step 1: verify that both prevent duplicates.

Step 2: compare the queries.

-- Lower approach
SELECT * FROM users_lower WHERE LOWER(email) = LOWER('test@example.com');

-- Citext approach
SELECT * FROM users_citext WHERE email = 'test@example.com';

Step 3: verify that the original case is preserved in CITEXT.

SELECT email FROM users_citext WHERE id = 1;
-- Expected: 'Test@Example.com' (not 'test@example.com')

Step 4: measure the overhead.

-- Insert 100k rows in each
-- Compare queries

EXPLAIN ANALYZE SELECT email FROM users_lower WHERE LOWER(email) = LOWER('user@test.com');
EXPLAIN ANALYZE SELECT email FROM users_citext WHERE email = 'user@test.com';

Any significant difference?

See discussion

Step 1: both approaches prevent duplicates correctly.

Step 2: queries with citext are simply cleaner.

Step 3: the original case is preserved in CITEXT. In the LOWER approach, it depends on how you stored it — if you normalized to lowercase, you lost the original.

Step 4: overhead is typically ~5-10% for citext. Negligible at normal scale.

Key takeaways:

Both work. The difference is code clarity and case preservation.
CITEXT wins in UX cases where the original matters (display).
CITEXT wins on maintainability — fewer places to forget LOWER().
The LOWER() approach is the default if you can't install the extension.

Summary and next step

What you learned:

citext = a native case-insensitive type. Comparisons automatically case-insensitive. Original case preserved.
LOWER() = manual approach. More verbose, error-prone.
Decision matrix: emails, usernames, tags → CITEXT. Hashes, URLs, names → VARCHAR.
SQLAlchemy: Mapped[str] = mapped_column(CITEXT, unique=True).
Migration: CREATE EXTENSION + ALTER COLUMN TYPE.
Overhead ~5-10%, negligible.
Traps: install the extension, FK with compatible types, don't use redundant LOWER().

In the next capsule we go to UUIDs: gen_random_uuid() (PG 13+) vs uuid-ossp. When to use each and why gen_random_uuid() is the modern answer almost every time.

Resources

PostgreSQL Docs — citext — official reference.
SQLAlchemy — CITEXT type — reference.
Crunchy Data — citext use cases — analysis with examples.

Capsule 03 of 08 — Module 7 — Advanced PostgreSQL for Backend Guide

← PreviousInstalling extensions + gotchas with cloud providers Next →UUIDs: `gen_random_uuid()` vs `uuid-ossp`