Устранение дублирования записей из-за регистра в email

38. Устранить дубликаты email из-за разного регистра

Условие задачи:
Дана таблица client_service:

client_id — идентификатор клиента;
employee_email — email сотрудника.

Один и тот же email может быть записан в разном регистре:

User@domain.com
user@domain.com

Необходимо устранить дублирование и запретить появление таких записей в дальнейшем.

Спойлеры к решению

Подсказки

💡 Для сравнения email без учёта регистра используй LOWER().
💡 Уникальность должна проверяться для пары client_id + email.
💡 Сначала нужно удалить существующие дубликаты.
💡 После очистки можно создать уникальный индекс по LOWER(employee_email).

Решение

Получить данные без дубликатов:

SELECT DISTINCT
    client_id,
    LOWER(employee_email) AS employee_email
FROM client_service;

Для PostgreSQL существующие дубликаты можно удалить так:

DELETE FROM client_service cs1
USING client_service cs2
WHERE cs1.client_id = cs2.client_id
  AND LOWER(cs1.employee_email) = LOWER(cs2.employee_email)
  AND cs1.ctid > cs2.ctid;

После этого можно нормализовать существующие email:

UPDATE client_service
SET employee_email = LOWER(employee_email);

И запретить появление дублей в дальнейшем:

CREATE UNIQUE INDEX ux_client_service_client_email
    ON client_service (
        client_id,
        LOWER(employee_email)
    );

Теперь для одного клиента записи:

User@domain.com
user@domain.com
USER@DOMAIN.COM

будут считаться одним и тем же email.

Уникальный индекс создаётся после удаления существующих дублей, иначе его создание завершится ошибкой.