Хабр SQL — удалить дубликаты строк, оставив одну

Условие

Таблица Users(id int, email varchar) без PRIMARY KEY или уникальных индексов. Накопились дубликаты — одинаковые (id, email) встречаются несколько раз. Нужно оставить ровно одну запись для каждого email, удалив остальные.

Решение

Подход 1 — ROW_NUMBER + DELETE по подзапросу (Postgres)

В Postgres каждой физической строке доступен системный ctid — идеальный «уникализатор», даже если бизнес-ID одинаковый.

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

Подход 2 — пересоздание через CTAS

Безопаснее для больших таблиц:

CREATE TABLE Users_dedup AS
SELECT DISTINCT ON (email) *
FROM Users
ORDER BY email, id;       -- "первая" по id
 
TRUNCATE Users;
INSERT INTO Users SELECT * FROM Users_dedup;
DROP TABLE Users_dedup;

DISTINCT ON — Postgres-расширение, оставляет одну строку из каждой группы по ключу.

Подход 3 — MySQL

DELETE u1
FROM Users u1
JOIN Users u2
  ON u1.email = u2.email
 AND u1.id    > u2.id;

Оставляет запись с минимальным id для каждого email.

Подводные камни

1. Без ORDER BY в ROW_NUMBER. Получите случайную «победившую» строку.
2. NULL в email. В стандарте NULL <> NULL, и каждая NULL-строка будет «уникальной». Используйте COALESCE(email, '__null__') для дедупа.
3. DELETE на больших таблицах. Лучше пересоздать или удалять батчами по ctid (Postgres) с LIMIT в подзапросе.
4. Foreign keys, ссылающиеся на старые id. При пересоздании таблицы можно сломать ссылки.
5. TRUNCATE сбрасывает sequence. Если на колонке serial, после TRUNCATE следующий id начнётся с 1 — закладывайте это или используйте DELETE.

Эталонный ответ

DELETE ... WHERE ctid IN (SELECT ctid FROM (... ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) > 1)) — универсальный паттерн дедупа в Postgres. В MySQL — self-join с >.