# Case-Insensitive Email in PostgreSQL: citext, a lower() Index, or a Nondeterministic Collation?

> On PostgreSQL 18, put a nondeterministic collation on the email column: a plain `UNIQUE`, a plain `WHERE email = $1`, `ON CONFLICT (email)` and foreign keys all work, and `LIKE` now does too. A unique index on `lower(email)` only works for queries that also say `lower()`, and `citext` quietly turns case-sensitive when the parameter arrives typed as `text`.

Source: https://schemity.com/blog/postgres-case-insensitive-email-citext-lower-index-collation/

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.

```sql
-- 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](https://www.postgresql.org/docs/18/collation.html): 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](https://www.postgresql.org/docs/18/release-18.html) 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.

```sql
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.

```sql
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](https://www.postgresql.org/docs/18/citext.html) 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:

```sql
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](https://schemity.com/doc/tables-and-fields/). Leaving it blank keeps the database default.

![Schemity's field dialog for the email column of customers on a PostgreSQL diagram: type TEXT, and the Collation list open on the database's collations, from zh-Hant-MO-x-icu to zu-x-icu, with case_insensitive, the collation created for this post, selected at the end](https://schemity.com/images/blog/postgres-case-insensitive-email-citext-lower-index-collation/field-dialog-collation.webp)

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.

![Schemity's index dialog for customers_email_lower_key on a PostgreSQL diagram: Keys lower(email), Type BTREE, Unique ticked, with the same index listed in the entity's index table behind it](https://schemity.com/images/blog/postgres-case-insensitive-email-citext-lower-index-collation/index-dialog-lower-email.webp)

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](https://schemity.com/blog/postgres-unique-constraint-vs-unique-index/), and the generated column route is compared with a trigger in [PostgreSQL generated column vs trigger](https://schemity.com/blog/postgres-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](https://schemity.com/blog/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](https://schemity.com/blog/unique-constraints-and-nullable-columns/), and why the length limit on the same column rarely matters is in [PostgreSQL varchar vs text](https://schemity.com/blog/postgres-varchar-vs-text/).

## Frequently asked questions

### How do I make an email column unique regardless of case in PostgreSQL?

Create a case-insensitive collation once, `CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false)`, and declare the column `email text COLLATE case_insensitive NOT NULL UNIQUE`. Inserting `ana@example.com` beside `Ana@Example.com` then fails with a duplicate key error, and `WHERE email = 'ANA@EXAMPLE.COM'` finds the row through the index.

### Should I use `citext` or a nondeterministic collation?

A collation, on PostgreSQL 12 or later. The `citext` documentation itself recommends nondeterministic collations instead, and `citext` has a trap: compared with a parameter typed `text` or `varchar`, PostgreSQL uses the case-sensitive text operator and a sequential scan. Stay on `citext` only if you need `ILIKE` or regular expressions on the column (or `LIKE` before PostgreSQL 18), which a nondeterministic collation refuses.

### Why does my query not find the email when I have a unique index on `lower(email)`?

Because the index only serves queries that compare `lower(email)`. A plain `WHERE email = 'ana@example.com'` compares the stored text case-sensitively, so it misses `Ana@Example.com` and scans the whole table. The same index cannot back `ON CONFLICT (email)` or a foreign key.
