Enforced lowercase on the frontend and the backend. The migrated data had other ideas.
Go-live was clean. No critical errors in the first hours. The team was relieved. We had migrated 1.5 million user accounts from the old system to the new one, and everything looked fine.
Then the support tickets started.
Users said they could not log in. They were certain they were using the same credentials as the old system. Password reset was not helping because the reset email was not arriving. Not because the mail server was down. Because the email they typed did not match any record in the database.
Except it did. We could see it. john@example.com was right there in the users table.
The email they were typing was John@example.com.
We had 600 affected accounts by the time we identified the pattern. Out of 1.5 million migrated users, 600 had emails stored in the old system with mixed casing in the local part. Some had a capital first letter. Some had uppercase characters mid-address. A few had inconsistent casing throughout, presumably from how they originally registered years ago.
In the old system, none of that mattered. In the new one, it was a critical auth failure.
I had a UNIQUE constraint on the email column in the new database. I had .toLowerCase() on the frontend and strtolower() on the backend for every new registration. I had none of the right assumptions about what the migrated data actually contained.
That was the day I learned what it costs to carry old assumptions into a new system.
The Assumption That Broke Everything
The old system ran on MySQL. The new system runs on PostgreSQL. That sentence sounds like a configuration change. It is not. It is a behavioral change, and the behavior that matters here is how each engine compares strings.
MySQL, using its default collation (utf8mb4_0900_ai_ci in MySQL 8.0, or utf8mb4_general_ci in older versions), performs case-insensitive string comparisons. john@example.com and John@example.com are the same value to MySQL. The unique constraint catches both. A WHERE email = ? query finds both. Users could register with any casing variation and the system would correctly reject it as a duplicate. Over the years the old system accumulated emails stored exactly as users originally typed them. Some had capitals. Most did not. MySQL never cared.
PostgreSQL does the opposite. It is case-sensitive by default for all string comparisons. john@example.com and John@example.com are two completely different values. A WHERE email = 'john@example.com' query will not return the row where the email is stored as John@example.com. They do not match.
When we migrated 1.5 million user records, we moved the emails exactly as stored. The old system held them in whatever casing the user originally provided. That was fine for MySQL. It was a silent bomb for PostgreSQL.
We enforced lowercase on every new registration in the new system. Frontend validation, backend mutator, the works. A new user typing John@example.com would have it stored as john@example.com immediately. But that enforcement only applied to new data. The migrated records retained their original casing. When those users typed their email to log in, the login query normalised their input to lowercase, then compared it against a stored value that was not lowercase. No match. Authentication failed.
Why Staging Caught Nothing
Staging used a partial data migration. About ten thousand records, all seeded from IT department accounts created specifically for testing. Every tester typed their email in lowercase. Every seeded account had a lowercase email. Nobody on the team registered with John@example.com because nobody thought to.
This is the part that stings. We were not being careless. We enforced lowercase in the new application. We validated it. We tested registration and login extensively. What we did not test was the migration path for users who had registered in the old system years ago with whatever casing their keyboard produced.
The old MySQL system never exposed this data quality problem because it never needed to. Case-insensitive comparison meant John@example.com and john@example.com were always the same row. The dirty casing sat in the database harmlessly for years. There was no reason to clean it. There was no symptom that it needed cleaning.
When we wrote the migration script, we wrote it to move data faithfully. We were not transforming it. We were preserving it. That was the correct instinct for most columns and the wrong instinct for the email column specifically.
The local part of an email address (everything before the @) is technically case-sensitive according to RFC 5321. In practice, no major email provider differentiates on case. Gmail, Outlook and Yahoo all treat the local part as case-insensitive. But that practical reality is what caused years of mixed-case emails to accumulate in the old system without complaint. The RFC is technically correct and practically what created our data problem.
The Three Ways to Fix This
There is no single correct answer. The right fix depends on your stack and your tolerance for complexity. Here are the three approaches in order of preference.
Option 1: Normalise at the Application Layer Before Anything Touches the Database
This is the simplest and most portable fix. Before you insert or query an email, lowercase it. Every time. No exceptions.
In Laravel:
// In your FormRequest or before saving
$email = strtolower(trim($request->input('email')));Or enforce it at the model level with a mutator so it cannot be bypassed:
protected function email(): Attribute
{
return Attribute::make(
set: fn (string $value) => strtolower(trim($value)),
);
}With this in place, John@example.com is stored as john@example.com. The existing unique constraint now works correctly because the values reaching the database are always normalised. Your WHERE email = ? queries also work correctly because you normalise the input before querying.
This approach works on any database. It does not require database-level changes. It is the right starting point.
The risk: if you have existing dirty data in the column, normalising at the application layer does not clean it up. You need a one-time migration to lowercase all existing email values before you enforce this. Run it in a transaction, test it on a backup first.
Option 2: Add a Functional Index on LOWER(email)
If you want the database itself to enforce case-insensitive uniqueness without changing your column type, PostgreSQL supports functional indexes:
CREATE UNIQUE INDEX users_email_unique_ci
ON users (LOWER(email));This index does not change how values are stored. It indexes the lowercase version of the value and enforces uniqueness on that. john@example.com and John@example.com will now violate the constraint because they produce the same lowercase output.
Your queries also benefit. If you query with the same function:
SELECT * FROM users WHERE LOWER(email) = LOWER('John@example.com');PostgreSQL can use the index and the query performs well even on large tables.
In a Laravel migration:
DB::statement('CREATE UNIQUE INDEX users_email_unique_ci ON users (LOWER(email))');Drop the existing case-sensitive unique index first if you have one:
DB::statement('DROP INDEX IF EXISTS users_email_unique');
DB::statement('CREATE UNIQUE INDEX users_email_unique_ci ON users (LOWER(email))');This approach is good as a safety net even if you are already normalising at the application layer. Defence in depth.
Option 3: Use the citext Extension
PostgreSQL ships with a built-in extension called citext (case-insensitive text). It is a column type that behaves like TEXT in every way except that all comparisons are case-insensitive.
Enable it:
CREATE EXTENSION IF NOT EXISTS citext;Change the column type:
ALTER TABLE users ALTER COLUMN email TYPE citext;Now the column handles case-insensitivity natively. Your existing unique constraint works correctly. Your queries work correctly. You do not need to remember to call LOWER() anywhere.
In a Laravel migration:
DB::statement('CREATE EXTENSION IF NOT EXISTS citext');
Schema::table('users', function (Blueprint $table) {
DB::statement('ALTER TABLE users ALTER COLUMN email TYPE citext');
});The tradeoff: citext is PostgreSQL-specific. If your application needs to run on multiple database engines, this ties you to Postgres. For most production applications that is not a real constraint, but it is worth naming.
Which One Should You Use
Use all three in combination if you can.
Normalise at the application layer because it is portable and makes intent explicit. Add the functional index as a database-level safety net because application code can be bypassed. Skip citext unless you are starting fresh or comfortable with the migration, because retrofitting a column type change on a live production table with existing data requires careful planning.
If I were starting a new PostgreSQL project today, I would use citext for email columns from day one and normalise in the application layer as a belt-and-braces measure. If I were inheriting a migrated dataset, I would run UPDATE users SET email = LOWER(email) in a transaction on a backup first, then apply the functional index, then enforce lowercase in the application layer going forward.
The Cleanup No One Talks About
Fixing the code is the easy part. The hard part is the 600 users who cannot log in right now while you are reading the stack trace.
Our immediate fix was a hotfix query. We lowercased all email values in the users table directly:
UPDATE users SET email = LOWER(email);We ran it in a transaction on a backup first, verified the row count matched, then applied it to production. The 600 affected users could log in within the hour.
The longer cleanup was auditing for cases where the casing difference had created duplicate accounts. In our migration, because we were moving from one system to another rather than having users register twice, most affected users had exactly one account. The casing was wrong but the account was unique. A smaller number had registered in the new system before we caught the issue, creating a genuine duplicate. Those required manual review.
Document everything you do during cleanup. If your system has audit trail requirements, the bulk update needs to be logged as an administrative action with a timestamp, a reason and the account of whoever ran it. Regulators do not care that PostgreSQL behaves differently from MySQL. They care that you modified user records and whether you can account for it.
The Lesson That Outlasts This Specific Bug
The deeper problem here is not PostgreSQL. It is assumption transfer during migration.
When you migrate from one database engine to another, you carry not just the data but the silent assumptions the old engine was enforcing on your behalf. MySQL was silently treating your email comparisons as case-insensitive for years. You got that behavior for free. You did not build it. You did not document it. And when you moved to a system that does not provide it by default, you had no checklist item that said “verify the behaviors this engine was providing implicitly.”
The fix is not to stop using PostgreSQL. PostgreSQL is stricter and more correct than MySQL in most of these cases. The fix is to treat a database migration as a behavioral audit, not a data transfer.
Before any MySQL-to-PostgreSQL migration, ask these questions about your existing data. Are there any string columns where comparisons in the old system were implicitly case-insensitive? Does any login or lookup flow rely on that behavior? What does the data actually look like, including casing, whitespace and Unicode characters, and does it match what the new system expects?
Your migration script is not done when the row counts match. It is done when the data in the new system behaves the same way for users as the data in the old system did.
Normalise early. Enforce at every layer. And include a data quality audit in your migration checklist before you move a single row to production.
Your Business — On AutoPilot with DDImedia AI Assistant
(Join Our Waitlist)
Visit us at DataDrivenInvestor.com
Join our creator ecosystem here.
DDI Official Telegram Channel: https://t.me/+tafUp6ecEys4YjQ1
