Usuń zduplikowane rekordy w PostgreSQL


113

Mam tabelę w bazie danych PostgreSQL 8.3.8, która nie ma kluczy / ograniczeń i ma wiele wierszy z dokładnie takimi samymi wartościami.

Chciałbym usunąć wszystkie duplikaty i zachować tylko 1 kopię każdego wiersza.

W szczególności istnieje jedna kolumna (zwana „kluczem”), której można użyć do identyfikacji duplikatów (tj. Powinien istnieć tylko jeden wpis dla każdego odrębnego „klucza”).

W jaki sposób mogę to zrobić? (najlepiej pojedynczym poleceniem SQL) Szybkość nie jest w tym przypadku problemem (jest tylko kilka wierszy).

Odpowiedzi:


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

20
Nie używaj go, jest za wolny!
Paweł Malisak

5
Chociaż to rozwiązanie zdecydowanie działa, poniższe rozwiązanie @rapimo wykonuje się znacznie szybciej. Uważam, że ma to związek z wykonaniem wewnętrznej instrukcji select tutaj N razy (dla wszystkich N wierszy w tabeli duplikatów), a nie z grupowaniem, które ma miejsce w innym rozwiązaniu.
David

W przypadku ogromnych tabel (kilka milionów rekordów) ten właściwie mieści się w pamięci, w przeciwieństwie do rozwiązania @ rapimo. W takich przypadkach jest to szybszy (bez zamiany).
Giel

1
Dodając wyjaśnienie: to działa, ponieważ ctid to specjalna kolumna postgres wskazująca fizyczne położenie wiersza. Możesz użyć tego jako unikalnego identyfikatora, nawet jeśli twój stół nie ma unikalnego identyfikatora. postgresql.org/docs/8.2/ddl-system-columns.html
Eric Burel,

194

Szybszym rozwiązaniem jest

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

20
Dlaczego jest szybszy niż rozwiązanie a_horse_with_no_name?
Roberto

3
Jest to szybsze, ponieważ uruchamia tylko 2 zapytania. Najpierw jeden, aby zaznaczyć wszystkie duplikaty, a następnie jeden, aby usunąć wszystkie elementy z tabeli. Zapytanie @a_horse_with_no_name wykonuje zapytanie, aby sprawdzić, czy pasuje do innego dla każdego elementu w tabeli.
Aeolun

5
co jest ctid?
techkuz

6
from docs: ctid. Fizyczna lokalizacja wersji wiersza w jego tabeli. Zauważ, że chociaż ctid może być użyty do bardzo szybkiego zlokalizowania wersji wiersza, ctid wiersza będzie się zmieniać za każdym razem, gdy zostanie zaktualizowany lub przeniesiony przez VACUUM FULL. Dlatego ctid jest bezużyteczny jako długoterminowy identyfikator wiersza.
Saim

1
Wydaje się, że to nie działa, gdy masz więcej niż 2 zduplikowane wiersze, ponieważ usuwa tylko jeden duplikat naraz.
Frankie Drake

74

To jest szybkie i zwięzłe:

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

Zobacz także moją odpowiedź w artykule Jak usunąć zduplikowane wiersze bez unikalnego identyfikatora, który zawiera więcej informacji.


co oznacza ct? liczyć?
techkuz

4
@trthhrtz ctidwskazuje na fizyczną lokalizację rekordu w tabeli. W przeciwieństwie do tego, co napisałem wtedy w komentarzu, użycie operatora less than niekoniecznie wskazuje na starszą wersję, ponieważ ct może się zawijać, a wartość z niższym ctid może być w rzeczywistości nowsza.
isapir

1
Po prostu do Twojej wiadomości, wypróbowałem to rozwiązanie i przerwałem je po odczekaniu 15 minut. Wypróbowałem rozwiązanie Rapimo i zakończyło się w około 10 sekund (usunięte ~ 700 000 wierszy).
Patrick

@Patrick nie może sobie wyobrazić, jeśli twoja baza danych nie ma unikalnego identyfikatora, ponieważ odpowiedź rapimo nie działa w tym przypadku.
stucash

@isapir Jestem po prostu ciekawy, powyższe odpowiedzi, czy zachowują starsze rekordy tak, jak wybrali min(ctid)? podczas gdy twoi zatrzymują nowsze? dzięki!
stucash

17

Próbowałem tego:

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);

dostarczone przez Postgres wiki:

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


Masz jakiś pomysł na wykonanie w porównaniu z odpowiedzią @ rapimo i zaakceptowaną (@a_horse_with_no_name)?
tuxayo

3
Ten nie zadziała, jeśli, podobnie jak w pytaniach, wszystkie kolumny są identyczne, idzawarte.
ibizaman

To zapytanie usunie zarówno oryginalną kopię, jak i duplikaty. pytanie dotyczy zachowania co najmniej jednego wiersza.
pyBomb

@pyBomb źle, zachowa pierwszą, w idktórej kolumna1 ... 3 jest zduplikowana
Jeff

Od postgresql 12 jest to ZDECYDOWANIE najszybsze rozwiązanie (wobec 300 milionów wierszy). Właśnie przetestowałem wszystko, co zaproponowałem w tym pytaniu, łącznie z zaakceptowaną odpowiedzią i to "oficjalne" rozwiązanie jest faktycznie najszybsze i spełnia wszystkie wymagania z OP (i moje)
Jeff

7

Użyłbym tabeli tymczasowej:

create table tab_temp as
select distinct f1, f2, f3, fn
  from tab;

Następnie usuń tabi zmień nazwę tab_tempna tab.


9
To podejście nie uwzględnia wyzwalaczy, indeksów i statystyk. Z pewnością możesz je dodać, ale to też dodaje dużo pracy.
Jordan,

1
Nie każdy tego potrzebuje. To podejście jest niezwykle szybkie i działało znacznie lepiej niż pozostałe w przypadku 200 000 e-maili (varchar 250) bez indeksów.
Sergey Telshevsky

1
Pełny kod:DROP TABLE IF EXISTS tmp; CREATE TABLE tmp as ( SELECT * from (SELECT DISTINCT * FROM your_table) as t ); DELETE from your_table; INSERT INTO your_table SELECT * from tmp; DROP TABLE tmp;
Eric Burel

7

Musiałem stworzyć własną wersję. Wersja napisana przez @a_horse_with_no_name jest zdecydowanie za wolna na mojej tabeli (21 mln wierszy). @Rapimo po prostu nie usuwa dupków.

Oto, czego używam w 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);

1

Inne podejście (działa tylko wtedy, gdy masz jakieś unikalne pole, takie jak idw tabeli), aby znaleźć wszystkie unikalne identyfikatory według kolumn i usunąć inne identyfikatory, które nie znajdują się na liście unikatowych

DELETE
FROM users
WHERE users.id NOT IN (SELECT DISTINCT ON (username, email) id FROM users);

Chodzi o to, że w moim pytaniu tabele nie miały unikalnych identyfikatorów; „duplikaty” to wiele wierszy z dokładnie takimi samymi wartościami we wszystkich kolumnach.
André Morujão,

Racja, dodałem kilka uwag
Zaytsev Dmitry

1

Co powiesz na:

Z
  u AS (SELECT DISTINCT * FROM your_table),
  x AS (USUŃ Z Twojej_tabeli)
INSERT INTO your_table SELECT * FROM u;

Martwiłem się o wykonanie zlecenia, czy DELETE nastąpi przed SELECT DISTINCT, ale dla mnie działa dobrze. Dodatkową zaletą jest brak konieczności posiadania wiedzy na temat struktury tabeli.


Jedyną wadą jest to, że jeśli masz typ danych, który nie obsługuje równości (np. json), To nie zadziała.
a_horse_with_no_name

0

To działało dobrze dla mnie. Miałem tabelę, terminy, które zawierały zduplikowane wartości. Uruchomiono zapytanie w celu wypełnienia tabeli tymczasowej wszystkimi zduplikowanymi wierszami. Następnie uruchomiłem instrukcję delete z tymi identyfikatorami w tabeli tymczasowej. wartość to kolumna zawierająca duplikaty.

        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)

0

Oto rozwiązanie wykorzystujące PARTITION BY:

DELETE FROM dups
USING (
  SELECT
    ctid,
    (ctid != min(ctid) OVER (PARTITION BY key_column1, key_column2 [...])) AS is_duplicate
  FROM dups 
) dups_find_duplicates
WHERE dups.ctid == dups_find_duplicates.ctid
AND dups_find_duplicates.is_duplicate
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.