Skip to main content

PG.L8.UNIQUE_CASE_FOLDED — A uniqueness guarantee your queries cannot see

  • Category: convention
  • Level: 8
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18 and newer

The index is right. That is the point​

PG.L8.UNIQUE_CASE_FOLDED reports no defect. A unique index over an expression is a deliberate, correct thing to build, and this rule exists because it is a deliberate correct thing that is invisible from the application side.

CREATE UNIQUE INDEX users_email_lower_unique ON users ((lower(email)));

After that statement the table guarantees something the column does not advertise: no two rows may hold addresses that are equal once case is folded away. The column is still email. A model still says unique:users,email. Nothing about the schema as an ORM reads it has changed.

Where the two halves come apart​

An index on an expression stores the computed values, and the planner uses it only for a query written with the same expression. Both halves of that sentence bite at once:

// Compares the COLUMN. The index constrains lower(email).
$taken = User::where('email', $input)->exists();

For an address already stored as [email protected], a check for [email protected] finds nothing — so the code concludes the address is free and writes. The index then refuses the write.

The bug is not the refusal; the refusal is the guarantee working. The bug is that the caller was told two different things by two parts of the same system, and the write is where it surfaces — as something that looks like a database fault rather than a mismatched comparison.

On an endpoint deliberately built not to reveal whether an account exists, that gap is the disclosure. A registration form that answers "we have sent you an email" for a free address and throws a server error for a taken one has an enumeration oracle in it, however careful the response copy is. This has happened in a real application and was found by hand.

Flagged​

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email');
});

// The guarantee lives in the index and nowhere else: the column still holds whatever case was
// typed, and nothing in the model or the validation rules says so.
DB::statement('CREATE UNIQUE INDEX users_email_lower_unique ON users ((lower(email)))');

Preferred​

Schema::create('users', function (Blueprint $table) {
    $table->id();
    // Folded before it is written, so the value in the column IS the comparison key and an
    // ordinary unique index carries the whole guarantee.
    $table->string('email')->unique();
});

Folding on the way in makes the stored value the comparison key, so an ordinary unique index carries the whole guarantee and nothing can drift.

Keeping the functional index is also a legitimate answer, and sometimes the better one — it still holds for rows written by a job, a console command or a second application that never passes through your validation. Then the rule is asking you to make every lookup fold too, not to drop the index. Dropping it removes the guarantee instead of fixing the mismatch.

What this rule cannot see​

  • Your application. This is a reader of databases. Whether your code compares the same way is the half that decides the outcome, and no catalog says.
  • The same promise made another way. A citext column, or a column under a case-insensitive collation, carries an ordinary unique index and promises the same thing with no expression to recognize. Those are silent here — silence never means "this schema folds no case".
  • A fold wrapped in something else. lower(btrim(email)) is unique over a value that is folded and trimmed. The sentence this rule prints would be false about it, so it is refused rather than approximated.

A note on the spelling​

The check reads what the server prints for each key position, not what your migration said. Two things change on the way:

-- written, with the doubled parentheses MySQL requires for a functional key
CREATE UNIQUE INDEX users_email_lower_unique ON users ((lower(email)));
-- read back, for a varchar column
lower(email::text)

The extra parentheses are gone, and a cast has appeared, because the column is varchar rather than text and the function is defined over text. Laravel's string() column is varchar, so the cast form is the ordinary one.

MySQL has no equivalent​

By absence rather than by oversight. MySQL's default collations are already case-insensitive, so the guarantee usually belongs to the column itself and a functional unique index over lower() is rare there. If your MySQL schema does carry one, this rule will not tell you.