Удаление дублей из таблицы emails

50. Удалить дублирующиеся email, оставив одну запись

Условие задачи:
Дана таблица emails(id, email), в которой один и тот же email может встречаться несколько раз.

Необходимо удалить дубликаты и оставить только одну запись для каждого уникального email.

Код:

CREATE TABLE emails (
    id INT,
    email VARCHAR(30)
);

INSERT INTO emails(id, email)
VALUES
    (1, 'aa@yandex.ru'),
    (2, 'bb@yandex.ru'),
    (3, 'aa@yandex.ru');

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

Подсказки

💡 Сначала пронумеруй строки внутри каждой группы одинаковых email.
💡 Для этого удобно использовать ROW_NUMBER().
💡 Первую строку в каждой группе нужно оставить, остальные удалить.
💡 В PostgreSQL для однозначного удаления конкретной строки можно использовать ctid.

Решение

В PostgreSQL можно использовать ROW_NUMBER() вместе с ctid:

WITH duplicates AS (
    SELECT
        ctid,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY id
        ) AS rn
    FROM emails
)
DELETE FROM emails
WHERE ctid IN (
    SELECT ctid
    FROM duplicates
    WHERE rn > 1
);

ROW_NUMBER() нумерует записи отдельно для каждого email:

email         | rn
--------------+---
aa@yandex.ru  | 1
aa@yandex.ru  | 2
bb@yandex.ru  | 1

Удаляются только строки с:

rn > 1

В результате останется:

id | email
---+-------------
1  | aa@yandex.ru
2  | bb@yandex.ru

Если id гарантированно уникален, запрос можно написать проще:

WITH duplicates AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY id
        ) AS rn
    FROM emails
)
DELETE FROM emails
WHERE id IN (
    SELECT id
    FROM duplicates
    WHERE rn > 1
);

После удаления дублей имеет смысл добавить ограничение, чтобы они больше не появлялись:

ALTER TABLE emails
ADD CONSTRAINT uq_emails_email UNIQUE (email);