Удаление дубликатов записей в PostgreSQL

Question

Удаление дубликатов записей в PostgreSQL

У меня есть таблица в базе данных PostgreSQL 8.3.8, которая не имеет ключей/ограничений на нее и имеет несколько строк с точно такими же значениями.

Я хотел бы удалить все дубликаты и сохранить только 1 копию каждой строки.

существует один столбец, в частности (с именем "ключ"), который может быть использован для идентификации дубликатов (т. е. должна существовать только одна запись для каждого отдельного"ключа").

Как я могу это сделать? (в идеале с одной командой SQL )
Скорость не проблема в этом случае (есть только несколько строк).

1095 8

postgresql sql duplicates

8 ответов:

Comments

Ничего не найдено.

a_horse_with_no_name · Accepted Answer · 2011-07-05 17:15:42

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

rapimo · Accepted Answer · 2012-10-18 23:29:03

более быстрое решение

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

isapir · Accepted Answer · 2018-04-13 21:09:23

это быстро и лаконично:
DELETE FROM dupes T1
    USING   dupes T2
WHERE   T1.ctid < T2.ctid  -- delete the older versions
    AND T1.key  = T2.key;  -- add more columns if needed
см. Также мой ответ на как удалить повторяющиеся строки без уникального идентификатора который включает в себя дополнительную информацию.

Radu Gabriel · Accepted Answer · 2016-11-16 17:01:09

Я попытался это:
DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);
предоставлено Postgres wiki:

https://wiki.postgresql.org/wiki/Deleting_duplicates

expert · Accepted Answer · 2016-10-06 01:48:49

Я должен создать свою собственную версию. Версия, написанная @a_horse_with_no_name, слишком медленная на моей таблице (21M строк). И @rapimo просто не удалить дублированные файлы.

вот что я использую на PostgreSQL 9.5
DELETE FROM your_table
WHERE ctid IN (
  SELECT unnest(array_remove(all_ctids, actid))
  FROM (
         SELECT
           min(b.ctid)     AS actid,
           array_agg(ctid) AS all_ctids
         FROM your_table b
         GROUP BY key1, key2, key3, key4
         HAVING count(*) > 1) c);

Pablo Santa Cruz · Accepted Answer · 2011-07-05 17:04:43

Я бы использовал временную таблицу:
create table tab_temp as
select distinct f1, f2, f3, fn
  from tab;
затем удалить tab переименовать tab_temp на tab.

Beanwah · Accepted Answer · 2016-11-27 21:20:49

Это хорошо работало для меня. У меня была таблица, термины, которые содержали повторяющиеся значения. Выполнил запрос для заполнения временной таблицы всеми повторяющимися строками. Затем я запустил инструкцию a delete с этими идентификаторами во временной таблице. значение-это столбец, содержащий дубликаты.
        CREATE TEMP TABLE dupids AS
        select id from (
                    select value, id, row_number() 
over (partition by value order by value) 
    as rownum from terms
                  ) tmp
                  where rownum >= 2;

delete from [table] where id in (select id from dupids)

Golokesh Patra · Accepted Answer · 2017-08-10 09:28:59

РАБОТАЕТ КАК ДЛЯ ОБЫЧНОГО SQL, ТАК И ДЛЯ POSTGRESQL (ТАКЖЕ РАБОТАЕТ В AWS REDSHIFT)
DROP TABLE IF EXISTS backupOfTheTableContainingDuplicates;

CREATE TABLE aNewEmptyTemporaryOrBackupTable 
AS SELECT DISTINCT * FROM originalTableContainingDuplicates;

TRUNCATE TABLE originalTableContainingDuplicates;

INSERT INTO originalTableContainingDuplicates SELECT * FROM 
aNewEmptyTemporaryOrBackupTable ;

DROP TABLE aNewEmptyTemporaryOrBackupTable ;
ОБЪЯСНЕНИЕ ПРИВЕДЕННОГО ВЫШЕ СЦЕНАРИЯ SQL

и

первый запрос гарантирует, что если у вас есть резервная/временная таблица исходной таблицы, содержащей дубликаты, то сначала удалите эту таблицу.

2-й запрос, создает новую таблицу (временную / резервную) таблицу с уникальными записями в исходной таблице, содержащей дубликат, поэтому новый временная таблица совпадает с исходной таблицей за вычетом повторяющихся записей.

3-й запрос усекает или опустошает исходную таблицу.

4-й запрос вставляет или копирует все уникальные записи во временной таблице в исходную таблицу, которая была недавно усечена (поэтому не имеет данных). После выполнения этого запроса исходная таблица будет заполнена уникальными данными, которые были во временной таблице.

5-й запрос, удаляет/удаляет ненужные временная таблица.

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