case-insensitive-uniquenesslisted

# Case-Insensitive Uniqueness (Functional Indexes) > Source: [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md) > -- Audit Finding #6 and Backlog #3 ## What Creating a case-insensitive unique constraint on text columns (typically email) using a **functional B-tree index** on `lower(column)` instead of the `citext` extension. ## Why Email addresses `Alice@Example.com` and `alice@example.com` are the same mailbox but different PostgreSQL text values. Without case-insensitive uniqueness: - Duplicate accounts can be created with different casing - Login lookups may fail (searching for `alice@` when stored as `Alice@`) - Data quality degrades silently The decision to use `lower()` instead of `citext`: - `citext` requires an extension (`CREATE EXTENSION citext`) -- adds surface area - `lower()` + B-tree is native, lightweight, and requires no extension - Both approaches are equally effective for ASCII email - Our Best Practices recommend `lower()` until `citext` is justified ## How ### Create the functional unique index ```sql CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uq_users__email_lower ON public."Users" (lower("Email")); ``` ### Query using the same expression ```sql -- MUST use lower() in the query for the index to be used SELECT "UserId", "Email" FROM public."Users" WHERE lower("Email") = lower('Alice@Example.com'); ``` ### Idempotent pattern for scripts ```sql DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_indexes