Как удалить дублирующиеся строки в Postgres и оставить по одному уникальному значению.
Поставновка проблемы: существует таблица в которой необходимо удалить дублирующиеся строки, но так, чтобы при этом осталось по одному значению из дублей.
Пусть у нас будет простая таблица emails для примера:
+----+--------------------+
| id | user_email |
+----+--------------------+
| 1 | test1@test.test |
| 2 | test2@test.test |
| 3 | test3@test.test |
| 4 | test4@test.test |
| 5 | dplc1@test.test |
| 6 | dplc1@test.test |
| 7 | dplc2@test.test |
| 8 | dplc2@test.test |
| 9 | dplc2@test.test |
+----+--------------------+
Выведем дублирующиеся строки командой:
select email, count(*) from emails
group by user_email
having count(*) > 1;
- сгруппируем строки по email’ам и посчитаем, сколько раз встречается каждый из них. Выберем только те значения, которые встречаются чаще одного раза и выведем их количество.
Результат команды:
+-----------------+-------+
| user_email | count |
+-----------------+-------+
| dplc1@test.test | 2 |
| dplc2@test.test | 3 |
+-----------------+-------+
Удалить дублирующиеся строки можно при помощи DELETE USING.
Оператор USING объединяет таблицы, используя для объединения таблиц одинаковые имена столбцов. Для наших целей он необходим, чтобы среди значений в таблице найти дубли, путем попарного сравнения значений.
Таким образом, запрос будет выглядеть так:
delete from emails a
using emails b
where a.id < b.id
and a.email = b.email;
Эта команда позволит удалить только один дубль из пары.
Попробуем посмотреть, что осталось в таблице, после выполнения этой команды:
+----+-----------------+
| id | user_email |
+----+-----------------+
| 1 | test1@test.test |
| 2 | test2@test.test |
| 3 | test3@test.test |
| 4 | test4@test.test |
| 5 | dplc1@test.test |
| 7 | dplc2@test.test |
+----+-----------------+
Дублирующиеся записи удалены, оставив по одной уникальной копии. То, что нужно.
Что же делать, если в таблице нет столбца, который может помочь унифицировать строку? Допустим, у нас имеется таблица emails, которая хранит в себе соответствия конкретных username к email’ам:
+----------+-----------------+
| username | user_email |
+----------+-----------------+
| test1 | test1@test.test |
| test2 | test2@test.test |
| test3 | test3@test.test |
| test4 | test4@test.test |
| dplc1 | dplc1@test.test |
| dplc1 | dplc1@test.test |
| dplc2 | dplc2@test.test |
| dplc2 | dplc2@test.test |
| dplc2 | dplc2@test.test |
+----------+-----------------+
Выведем и посчитаем дублирующиеся строки тут:
select username, email, count(*) from emails
group by user_email
having count(*) > 1;
В результате видим, что в таблице имеются дублирующиеся строки:
+----------+-----------------+-------+
| username | user_email | count |
+----------+-----------------+-------+
| dplc1 | dplc1@test.test | 2 |
| dplc2 | dplc2@test.test | 3 |
+----------+-----------------+-------+
Что делать в этом случае, когда у нас нет явного идентификатора в строке? А все просто, каждая строка в PostgreSQL имеет скрытый неявный идентификатор, который мы можем также использовать в качестве отличительного признака между дублирующимися строками.
А именно поле CTID, который предназначен для расположения кортежа. CTID — это скрытая и уникальная запись для каждой таблицы в PostgreSQL. Имеет тип данных системного столбца CTID и представляет собой пару (номер блока, индекс кортежа внутри блока).
Таким образом, для удаления дублирующихся строк в таблице можно использовать такую команду:
delete
from emails a
using emails b
where a.CTID < b.CTID
and a.email = b.email
and a.username = b.username;
Посмотрим, что получится в результате:
+----------+-----------------+
| username | user_email |
+----------+-----------------+
| test1 | test1@test.test |
| test2 | test2@test.test |
| test3 | test3@test.test |
| test4 | test4@test.test |
| dplc1 | dplc1@test.test |
| dplc2 | dplc2@test.test |
+----------+-----------------+