2026-09-30 · 6 min read · Rahul Chitturi
- sql
- postgresql
- identity
citext is a PostgreSQL extension type that compares text without regard to case by lowercasing with the database's character classification. A unique constraint on a citext email column treats Ada@example.com and ada@example.com as the same key.
A unique index on lower(email) looks similar and behaves differently. Queries must use that expression to hit the index, and the column type remains text. Mixing the two designs is how duplicate accounts and full table scans appear after a migration.
Confirm the type, extension, and constraint
Inspect the column type, the installed citext extension, and the exact unique index or constraint definition. A restore into a database that never ran CREATE EXTENSION citext fails, or a later migration silently rewrites the column as text.
- Record the column type and collation.
- Confirm citext is installed in the right database.
- Read the index expression if it uses lower().
- Compare the stored bytes with the value the user typed.
Watch case folding and operators
citext equality follows the database locale's lowercasing, which is not full Unicode case folding. The Turkish dotless ı is the usual example: a fold that looks correct in a UTF-8 unit test can still split one address into two keys.
Joining citext to text can pick a different operator and skip the citext index. Pattern matches are case-insensitive for citext, so a prefix search can return more rows than the same LIKE predicate on text.
Test inserts, lookups, and the migration
Use Flashman's SQL formatter for the constraint and the lookup, the case converter for the address variants, diff for migration output, and the JSON formatter for the API payload that created the row.
Insert both casings, a quoted local-part if your policy allows it, a Turkish capital I, a join to a text column, an ORM round trip, and a dump restore onto a database without the extension.