Удаление дубликатов строк в PostgreSQL: SQL-команда

Быстрый ответ

Для эффективного удаления дубликатов в малых SQL-таблицах можно применять общие табличные выражения (CTE) с функцией ROW_NUMBER(). Данный подход присваивает числовой ранг каждой строке в пределах групп дубликатов, после чего избыточные записи можно без труда исключить:

-- Эффективное решение для маленьких таблиц, подобно точному броску ниндзя-сюрикена
WITH RankedDuplicates AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY unique_column ORDER BY id) AS rn
  FROM table_name
)
DELETE FROM RankedDuplicates WHERE rn > 1;  -- Оставляем только одну строку из каждой группы дубликатов

Вместо unique_column подставьте столбец, дубликаты из которого нужно устранить, а table_name — имя вашей таблицы. Таким образом, сохраняется первая строка, а все остальные удаляются.

Разбор подходов: руководство для различных ситуаций

Профилактика появления дубликатов путём применения ограничений UNIQUE

Чтобы предотвратить появление новых дубликатов, можно добавить ограничения UNIQUE, которые позволят поддерживать порядок в данных:

-- Ограничение UNIQUE обеспечивает порядок данных, как учитель за порядком в классе
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(unique_column1, unique_column2);

Ограничения UNIQUE — это эффективный инструмент для предотвращения повторов в таблицах.

Подробнее об этом расскажет наш спикер на видео

Применение CTID в PostgreSQL для исключения дубликатов

В PostgreSQL можно использовать CTID, позволяющий легко удалить лишние записи:

-- CTID – это уникальный идентификатор строки в PostgreSQL для удобного исключения дубликатов
DELETE FROM table_name
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM table_name
  GROUP BY unique_column
);

CTID помогает легко находить и исключать дублирующиеся записи.

Работа с большими таблицами

Для преобразования больших таблиц можно создать новую таблицу с уникальными записями:

-- Создание новой таблицы с уникальными записями исключает все дубликаты, делая их невидимыми
CREATE TABLE new_table AS
SELECT DISTINCT ON (unique_column) *
FROM table_name;

После этого старую таблицу можно заменить на вновь созданную. Этот подход особенно эффективен, если первоначальная таблица переполнена дубликатами.

Визуализация

Процесс удаления дубликатов можно представить как сортировку коробки с яблоками:

До: [🍎, 🍎, 🍎🍏, 🍏, 🍎]

Убираем избыточные элементы... 🌀 Удаляем дубликаты

После: [🍎, 🍏]

Результатом становятся только уникальные яблоки, допуская прохождение только им, исключая повторы.

Сложный вопрос: решение задачи при отсутствии ключей

Обработка таблицы без ключевых полей

Если в вашей таблице изначально не было ключей, примените операторы EXISTS и NOT EXISTS для удаления дубликатов:

-- Поиск и удаление дубликатов, словно поиск металла в песке с помощью детектора
DELETE FROM table_name t1
WHERE EXISTS (
  SELECT 1
  FROM table_name t2
  WHERE t2.unique_column = t1.unique_column
  AND t2.id < t1.id
);

Добавление ключевых полей после создания таблицы

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

-- Добавление первичного ключа — это стремление к порядку и важная процедура, как и важно дать имя новому питомцу
ALTER TABLE table_name ADD COLUMN id SERIAL PRIMARY KEY;

Ресурсы для самостоятельного изучения

Вашим надёжным направлением в изучении будут вики PostgreSQL и использование DB Fiddle:

• Здесь вы найдёте множество практических примеров.
• Будут доступны поэтапные руководства для реализации SQL-решений на все случаи жизни.
• Изучение этих ресурсов поможет вам развивать навыки управления и эффективного контроля данных.

Полезные материалы

1. WITH common_table_expression (Transact-SQL) – SQL Server | Microsoft Learn — Основные аспекты документации Microsoft по CTE, которые способствуют развитию мастерства SQL-запросов.
2. Видеоурок: Как убрать дублирующиеся записи в SQL – YouTube — Видеоурок о том, как эффективно и просто удалять дубликаты в SQL.
3. Лучшие практики и производительность при удалении дубликатов — Сборник лучших практик для удаления данных из больших таблиц SQL.