Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.
TL;DR: On PostgreSQL 18, put a nondeterministic collation on the email column: a plain
UNIQUE, a plainWHERE email = $1,ON CONFLICT (email)and foreign keys all work, andLIKEnow does too. A unique index onlower(email)only works for queries that also saylower(), andcitextquietly turns case-sensitive when the parameter arrives typed astext.
For a case-insensitive unique email on PostgreSQL 18, use a nondeterministic collation on the column. It is the only one of the three common designs where the code you already write, WHERE email = $1, ON CONFLICT (email) and a foreign key to users.email from a column with the same collation, keeps working unchanged. A unique index on lower(email) enforces the rule but makes every lookup that forgets lower() miss the row. citext behaves well until a driver sends the parameter typed as text, and then it compares case-sensitively without an error.
Everything below ran on PostgreSQL 18.3 in a throwaway Docker container, with PostgreSQL 17.11 and MySQL 8.4.11 for the comparisons. Each design was loaded with the same 1,000,000 rows of the form User1@Example.com. Timings come from a laptop and are there to compare the three designs with each other, not to predict a server.
What are the three ways to make email case-insensitive in PostgreSQL?
The three are a citext column, a unique index on lower(email) over a text column, and a text column with a nondeterministic collation, and all three refuse the duplicate.
-- 1. citext: an extension type that compares as lower case CREATE EXTENSION citext; CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email citext NOT NULL UNIQUE); -- 2. text, with uniqueness on an expression CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL); CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email)); -- 3. text, with a collation that ignores case CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false); CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text COLLATE case_insensitive NOT NULL UNIQUE); With Ana@Example.com already stored, inserting ana@example.com fails on every design with ERROR: duplicate key value violates unique constraint, so the rule itself is never the difference. The difference is everything else the application does with the column. The collation is the example from PostgreSQL's collation chapter: ICU level 2 compares letters and accents but not case, so jose@example.com and josé@example.com stay two addresses. MySQL's default collation, utf8mb4_0900_ai_ci, ignores accents as well and refuses the second one with error 1062.
citext vs lower index vs collation: which should you use?
The collation, because it is the only design where an ordinary equality, an upsert and a foreign key all behave case-insensitively without anyone remembering to.
citext | lower(email) unique index | Nondeterministic collation | |
|---|---|---|---|
WHERE email = 'ANA@EXAMPLE.COM' | Finds the row, uses the index | Returns 0 rows, sequential scan | Finds the row, uses the index |
Same query with a text-typed parameter | Returns 0 rows, sequential scan | Returns 0 rows, sequential scan | Finds the row, uses the index |
ON CONFLICT (email) | Works | Error: no matching constraint | Works |
Foreign key to users (email) | Works | Error: no unique constraint | Works |
LIKE 'ana@%' | Works | Only on lower(email) | Works on 18, error on 17 |
ILIKE and ~ regular expressions | Both ignore case | ILIKE works, ~ is case-sensitive unless run on lower(email) | Error: not supported |
| Building the unique index, 1M rows | 9.17 s | 1.39 s | 2.09 s |
| 100,000 lookups by email, each design queried the way it needs | 3.08 s | 1.71 s, with lower(email) = lower($1) | 1.71 s |
| Needs an extension | Yes | No | No |
A foreign key to the citext or collation column needs the referencing column declared the same way: a plain text column is refused with incompatible types: text and citext, or with incompatible collations, and PostgreSQL 18's release notes list that requirement for nondeterministic collations as new. The unique indexes were all 39 MB, the same as a plain text one. 100,000 lookups on a plain case-sensitive text column took 1.62 seconds, so the collation costs almost nothing on equality here. The citext documentation says it is "slightly more efficient than using lower"; on this machine it was the slowest at both building and looking up.
Why does a lower() index miss rows?
Because PostgreSQL only uses an expression index when the query repeats the expression, and a query that does not repeat it is a different, case-sensitive comparison.
SELECT count(*) FROM users WHERE email = 'ana@example.com'; -- 0 SELECT count(*) FROM users WHERE lower(email) = lower('ANA@EXAMPLE.COM'); -- 1 The first query is what a typical ORM find-by-email emits, and it does not fail. It returns nothing, after a parallel sequential scan of the 1,000,000 rows, so a signup check passes and the insert then fails with a duplicate key error, a password reset says the account does not exist, and nothing explains why. Every query path has to know about the index: the login handler, the admin search, the support script and the next service.
The upsert and the foreign key fail more loudly. INSERT ... ON CONFLICT (email) DO NOTHING is refused with there is no unique or exclusion constraint matching the ON CONFLICT specification, so it has to be written ON CONFLICT (lower(email)). An expression index cannot be promoted to a constraint (ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX says index "users_email_lower_key" contains expressions), so a foreign key to users (email) fails with there is no unique constraint matching given keys for referenced table. Storing the normalised address in a generated column, email_key text GENERATED ALWAYS AS (lower(email)) STORED UNIQUE, fixes the upsert and the key, but brings back the same discipline: every query has to search the generated column.
When does citext stop being case-insensitive?
When it is compared with a value typed text or varchar, because PostgreSQL then casts the column to text and uses the case-sensitive operator.
PREPARE by_email(text) AS SELECT count(*) FROM users WHERE email = $1; EXECUTE by_email('user5@example.com'); -- 0, the stored value is User5@Example.com -- Filter: ((email)::text = 'user5@example.com'::text), Parallel Seq Scan The same statement prepared without a parameter type returns 1 through the unique index, because the untyped parameter is resolved as citext. So whether a lookup works depends on how the driver types its string parameters and on any ::text in the query, not on the schema. The citext documentation itself now opens with a tip to consider nondeterministic collations instead, which "handle more Unicode special cases correctly". The collation column returned 1 row through the index for the text-typed parameter as well.
What citext still has is pattern matching. A nondeterministic collation refuses ILIKE and regular expressions (nondeterministic collations are not supported for ILIKE), and on PostgreSQL 17 it refuses LIKE too. PostgreSQL 18's release notes list LIKE with nondeterministic collations as new. None of the three unique indexes served a prefix LIKE 'user5@%'; each query scanned the table.
What does switching an existing email column cost?
A type or collation change does not rewrite the table, but it rebuilds the unique index while holding the table's strongest lock, and it fails if two addresses already differ only by case.
On a 1,000,000-row table with email text UNIQUE, ALTER TABLE users ALTER COLUMN email TYPE text COLLATE case_insensitive took 1.93 seconds and ALTER COLUMN email TYPE citext took 8.11 seconds. The table's file node was unchanged after both, so only the index was rebuilt, but ALTER COLUMN ... TYPE takes an ACCESS EXCLUSIVE lock, which holds back reads as well as writes for that time. Of the three, only the lower() design can be added with CREATE UNIQUE INDEX CONCURRENTLY, which lets writes through.
All three fail on existing data the same way. With User5@Example.com and user5@example.com both present, each change stops with could not create unique index, and the failed CONCURRENTLY build leaves an invalid index behind that has to be dropped before trying again. Find the pairs first and decide which account survives, which is a product question, not a migration:
SELECT lower(email), count(*) FROM users GROUP BY 1 HAVING count(*) > 1; How does the choice show up in the schema diagram?
The collation design puts the rule on the column itself, where a diagram and every engineer reading it can see it, while a lower() index is easy to miss.
Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. On a PostgreSQL diagram connected to a database, the field dialog for a text column has a Collation list read from that database, including a collation you created such as case_insensitive, and the choice is written into CREATE TABLE, ADD COLUMN and ALTER COLUMN TYPE, as described in tables and fields. Leaving it blank keeps the database default.
An expression index such as lower(email) is kept as written from Import SQL and from the live database, and written back unchanged in the migration, so the second design survives a round trip too.
The table comparison above is the reason to make the choice deliberately when you design the next one: a case-insensitive email is a rule about the column, so put it where the column is declared and let every query inherit it.
When a unique rule should be a constraint and when an index, and how to add either without locking the table, is in PostgreSQL unique constraint vs unique index, and the generated column route is compared with a trigger in PostgreSQL generated column vs trigger. How MySQL's case-insensitive default turns into case-sensitive uniqueness during a migration is in MySQL to PostgreSQL schema conversion. What else changes about a unique rule when its column can be empty is in unique constraints and nullable columns, and why the length limit on the same column rarely matters is in PostgreSQL varchar vs text.


