SQL: как найти и удалить дубликаты строк (PostgreSQL, MySQL)

Чтобы найти дубликаты в SQL, сгруппируйте таблицу по нужному полю и оставьте группы, где COUNT(*) > 1. Удалить лишние строки, оставив по одной, поможет ROW_NUMBER() или самосоединение.

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

CREATE TABLE users (
    id    serial PRIMARY KEY,
    email text NOT NULL,
    name  text
);

INSERT INTO users (email, name) VALUES
    ('anna@example.com',  'Анна'),
    ('boris@example.com', 'Борис'),
    ('anna@example.com',  'Анна (копия)'),
    ('anna@example.com',  'Анна (ещё копия)'),
    ('vera@example.com',  'Вера');

Найти дубликаты по полю

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Результат: anna@example.com встречается 3 раза. HAVING фильтрует уже сгруппированные строки, а WHERE работает до группировки.

Показать все строки-дубликаты целиком

Группировка скрывает остальные поля. Чтобы увидеть полные строки, используйте оконную функцию:

SELECT *
FROM (
    SELECT u.*, COUNT(*) OVER (PARTITION BY email) AS cnt
    FROM users u
) t
WHERE cnt > 1
ORDER BY email, id;

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

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

Вариант 1: ROW_NUMBER (PostgreSQL, MySQL 8+, SQL Server)

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

PARTITION BY email нумерует строки внутри каждой группы одинаковых email, а ORDER BY id определяет, какая запись получит номер 1 и останется.

Вариант 2: самосоединение (PostgreSQL)

DELETE FROM users a
USING users b
WHERE a.email = b.email
  AND a.id > b.id;

Вариант 3: самосоединение (MySQL и MariaDB)

DELETE a
FROM users a
JOIN users b
  ON a.email = b.email
 AND a.id > b.id;

Если в таблице нет первичного ключа

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

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

Дубликаты с разным регистром и пробелами

Адреса Anna@Example.com и anna@example.com считаются разными строками. Нормализуйте значение в PARTITION BY или GROUP BY:

SELECT lower(trim(email)) AS email_norm, COUNT(*)
FROM users
GROUP BY lower(trim(email))
HAVING COUNT(*) > 1;

Как не допустить дубликаты в будущем

После очистки добавьте ограничение уникальности. Для PostgreSQL учитывайте регистр:

-- точное совпадение
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- без учёта регистра (PostgreSQL)
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));

Частые вопросы

Как удалить дубликаты по нескольким полям?

Перечислите поля через запятую: GROUP BY email, name или PARTITION BY email, name. Дубликатом считается строка, у которой совпали все указанные значения.

Как найти дубликаты, если значение NULL?

В GROUP BY все NULL попадают в одну группу и считаются дубликатами друг друга. Уникальные индексы, наоборот, разрешают несколько NULL. Если это не нужно, исключите их условием WHERE email IS NOT NULL.

Как оставить самую свежую запись, а не самую старую?

Измените порядок в оконной функции: ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC). Либо упорядочивайте по дате обновления.

Что быстрее: DELETE USING или ROW_NUMBER?

Это зависит от СУБД, индексов и объёма данных. Проверьте оба варианта через EXPLAIN ANALYZE на копии таблицы. На больших таблицах обязательно нужен индекс по полю, по которому ищутся дубликаты.