В PostgreSQL имена пользователей не должны различаться только регистром, но текущая схема допускает такую п...

CREATE TABLE app_user (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL UNIQUE
);

INSERT INTO app_user (username) VALUES ('Alice');
INSERT INTO app_user (username) VALUES ('alice');

Краткий ответ

Обычное UNIQUE (username) гарантирует уникальность исходных значений, но не обязательно их вариантов без учёта регистра. В PostgreSQL для правила «уникально после приведения к нижнему регистру» следует создать уникальный индекс по выражению lower(username).

CREATE UNIQUE INDEX app_user_username_lower_uq
    ON app_user (lower(username));

После этого значения Alice и alice будут считаться конфликтующими. Уникальность проверяется базой данных, поэтому она сохраняется и при конкурентных вставках из нескольких приложений.

Исторический контекст

Реляционные ограничения сравнивают значения согласно типу данных и правилам сортировки, или collation. SQL не задаёт универсальное правило, согласно которому текст всегда сравнивается без учёта регистра: это зависит от СУБД, типа данных и выбранной локали.

Поэтому бизнес-правило о регистронезависимых именах нельзя надёжно оставлять только на уровне формы или проверки в приложении. Ограничение должно быть выражено через нормализованное представление значения, для которого требуется уникальность.

Постановка проблемы

В исходной схеме Alice и alice являются разными строковыми значениями. Ограничение UNIQUE видит два различных значения и разрешает вторую вставку.

Проверка вида SELECT перед вставкой ненадёжна: два параллельных запроса могут одновременно не найти пользователя и затем оба попытаться вставить одинаковое имя. Кроме того, разные клиенты могут применять разные правила преобразования регистра.

Нужно заранее определить точную политику сравнения. Простое приведение к нижнему регистру подходит для правила, основанного именно на lower, но не является универсальной реализацией всех вариантов Unicode-регистронезависимого сравнения.

Подробное решение

В PostgreSQL уникальный индекс может быть построен не по самому столбцу, а по выражению:

CREATE TABLE app_user (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX app_user_username_lower_uq
    ON app_user (lower(username));

INSERT INTO app_user (username) VALUES ('Alice');
-- Следующая вставка завершается ошибкой уникальности:
INSERT INTO app_user (username) VALUES ('alice');

Индекс хранит результат lower(username) и запрещает одинаковые результаты для разных строк. NOT NULL отдельно запрещает отсутствие имени; уникальный индекс не заменяет это ограничение.

Запросы поиска должны использовать то же выражение, например WHERE lower(username) = lower(:username), чтобы планировщик мог эффективно применить индекс. При изменении имени индексная проверка выполняется заново для нового значения.

Иногда используют расширение PostgreSQL citext, которое предоставляет регистронезависимое сравнение через специальный тип. Это может сделать запросы проще, но связывает схему с расширением и не устраняет необходимость явно определить ожидаемое Unicode-поведение.

Перед созданием уникального индекса нужно найти и устранить уже существующие конфликты, иначе создание индекса завершится ошибкой. Также следует учитывать пробелы, Unicode-нормализацию и локаль: lower не означает автоматическую нормализацию всех визуально похожих или канонически эквивалентных строк.

Ситуация из практики

В сервисе регистрации приложение сначала выполняло поиск пользователя по имени, приведённому к нижнему регистру, а затем вставляло новую строку. При обычной нагрузке решение работало, но при одновременной регистрации одного имени возникали дубликаты: обе транзакции успевали пройти предварительную проверку.

Рассматривались три варианта. Проверка только в приложении была простой, но не защищала от гонок и других клиентов. Нормализация имени перед записью в отдельный столбец упрощала поиск, но требовала дисциплины при каждом обновлении и дополнительного контроля согласованности. Уникальный индекс по lower(username) централизовал правило в базе и автоматически обеспечивал безопасную проверку при конкурирующих операциях.

Выбрали функциональный уникальный индекс, потому что требовалось сохранить исходное написание имени для отображения, но сравнивать имена без учёта регистра. Один запрос регистрации мог завершиться ошибкой уникальности, после чего приложение корректно сообщало, что имя уже занято; дубликаты больше не появлялись.

Что кандидаты часто упускают

1. Достаточно ли сделать lower(username) в приложении перед вставкой?

Нет. Это уменьшает вероятность ошибки, но не создаёт гарантии целостности. Другой код может записать значение напрямую, а параллельные транзакции всё равно могут одновременно пройти проверку. Гарантию даёт именно уникальное ограничение или уникальный индекс в базе данных.

2. Можно ли заменить индекс обычным UNIQUE (username) после настройки регистронезависимой сортировки?

Иногда это возможно, но зависит от конкретной СУБД, collation и её семантики сравнения. Нельзя предполагать, что любая регистронезависимая локаль одинаково обрабатывает Unicode или поддерживается одинаково на всех окружениях. Для PostgreSQL явный индекс по lower(username) однозначно выражает выбранное правило lower, а более сложные требования требуют отдельной проверки возможностей collation или типа citext.

3. Что произойдёт при добавлении такого индекса в существующую таблицу с конфликтами?

Создание индекса завершится ошибкой, потому что индекс не может построить уникальную структуру при повторяющихся результатах lower(username). Сначала нужно найти группы конфликтующих строк, выбрать политику их объединения или переименования, исправить данные, а затем создать индекс. При большой таблице миграцию следует планировать отдельно, поскольку построение индекса может блокировать операции или потребовать специальной процедуры, зависящей от версии PostgreSQL и требований к доступности.