null

Удаление дублирующихся строк в PostgreSQL

4 October 2022 0 Marina Pashnina

Как удалить дублирующиеся строки в 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 |
+----------+-----------------+