2026-09-30 · 8 min read
- sql
- postgresql
- identity
Email addresses, usernames, and invite codes often need uniqueness without regard to case. PostgreSQL offers two common designs: the citext extension type, and a text column with a unique expression index on lower(column).
They are not interchangeable. citext changes comparison operators on the column. An expression index changes only the queries and constraints that use that expression. Pick one and write the lookup path to match it.
Own the extension and the column type
citext exists only after CREATE EXTENSION in that database. Put the extension in the same migration path as the table, and install it on every restore target before loading data. A dump that assumes citext will fail on a fresh server.
- Install citext deliberately, not from a developer's local template.
- Record which columns use the type.
- Reject an ORM migration that rewrites them as plain text.
- Keep a rollback that preserves existing keys.
Know the case-fold limit
citext compares values by lowercasing with the database's character classification. That covers ordinary ASCII letters and many locale rules, and it is not full Unicode case folding. Characters such as the Turkish dotless ı can still produce two keys for one identity.
If the product requires a specific fold, normalize in the application to a canonical text value and enforce uniqueness on that text. citext is a poor place to hide a business rule the application also implements differently.
Match operators and indexes
Equality between two citext values is case-insensitive and can use a citext unique index. Comparing citext to text may choose another operator and ignore that index. Write joins and filters so the types match.
A unique index on lower(email) is used when the query also applies lower() to the column, or when the planner can match that expression. A plain WHERE email = $1 on a text column remains case-sensitive and can miss the index.
Keep the API and the database in one canonical form
Decide whether stored text preserves the user's casing while comparisons ignore it, or whether you store a canonical lowercase value. citext preserves the original bytes and ignores case on compare. A lower() column stores the fold and loses the display casing unless you keep a second column.
Validate the character set you accept before insert. A unique constraint will not save you from homoglyphs, plus-addressing, or domain aliases unless the application defines those rules.
Test the identity, not only the happy insert
Use Flashman's SQL formatter for the constraint and lookup, the case converter for address variants, diff for migration SQL, the JSON formatter for API bodies, and the regex tester for the accepted syntax.
Cover both casings, a second insert of the same fold, a join to text, an ORM round trip, a restore without the extension, and one non-ASCII fold your locale actually uses.