Come eliminare le voci duplicate?

Devo aggiungere un vincolo univoco a una tabella esistente. Questo va bene, tranne che la tabella ha già milioni di righe e molte delle righe violano il vincolo univoco che devo aggiungere.

Qual è l’approccio più veloce per rimuovere le righe offensive? Ho una dichiarazione SQL che trova i duplicati e li cancella, ma ci vuole tempo per essere eseguito. C’è un altro modo per risolvere questo problema? Forse il backup del tavolo, quindi il ripristino dopo l’aggiunta del vincolo?

Ad esempio potresti:

CREATE TABLE tmp ... INSERT INTO tmp SELECT DISTINCT * FROM t; DROP TABLE t; ALTER TABLE tmp RENAME TO t; 

Alcuni di questi approcci sembrano un po ‘complicati e generalmente lo faccio come:

Dato tavolo da table , vuoi renderlo unico (campo1, campo2) mantenendo la riga con il campo massimo3:

 DELETE FROM table USING table alias WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND table.max_field < alias.max_field 

Ad esempio, ho una tabella, user_accounts e voglio aggiungere un vincolo univoco alla posta elettronica, ma ho alcuni duplicati. Di 'anche che voglio mantenere l'ultimo creato (max id tra i duplicati).

 DELETE FROM user_accounts USING user_accounts ua2 WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id; 
  • Nota - USING non è SQL standard, è un'estensione PostgreSQL (ma molto utile), ma la domanda originale menziona specificamente PostgreSQL.

Invece di creare una nuova tabella, puoi anche reinserire le righe univoche nella stessa tabella dopo averla troncata. Fai tutto in un’unica transazione . Facoltativamente, è ansible rilasciare automaticamente la tabella temporanea alla fine della transazione con ON COMMIT DROP . Vedi sotto.

Questo approccio è utile solo quando ci sono molte righe da cancellare da tutta la tabella. Per pochi duplicati, usa un DELETE .

Hai menzionato milioni di righe. Per velocizzare l’operazione, è necessario allocare abbastanza buffer temporanei per la sessione. L’impostazione deve essere regolata prima di utilizzare qualsiasi buffer temporaneo nella sessione corrente. Scopri le dimensioni del tuo tavolo:

 SELECT pg_size_pretty(pg_relation_size('tbl')); 

Imposta temp_buffers conseguenza. Arrotondare generosamente perché la rappresentazione in memoria richiede un po ‘più di RAM.

 SET temp_buffers = 200MB; -- example value BEGIN; -- CREATE TEMPORARY TABLE t_tmp ON COMMIT DROP AS -- drop temp table at commit CREATE TEMPORARY TABLE t_tmp AS -- retain temp table after commit SELECT DISTINCT * FROM tbl; -- DISTINCT folds duplicates TRUNCATE tbl; INSERT INTO tbl SELECT * FROM t_tmp; -- ORDER BY id; -- optionally "cluster" data while being at it. COMMIT; 

Questo metodo può essere superiore alla creazione di una nuova tabella se esistono oggetti dipendenti. Visualizzazioni, indici, chiavi esterne o altri oggetti che fanno riferimento alla tabella. TRUNCATE ti fa iniziare con una lavagna pulita comunque (nuovo file in background) ed è molto più veloce di DELETE FROM tbl con tabelle grandi ( DELETE può essere effettivamente più veloce con tabelle piccole).

Per le grandi tabelle, è regolarmente più veloce rilasciare indici e chiavi esterne, riempire la tabella e ricreare questi oggetti. Per quanto riguarda i vincoli di fk, devi essere certo che i nuovi dati siano validi, naturalmente o ti imbatterai in un’eccezione nel tentativo di creare il fk.

Nota che TRUNCATE richiede un blocco più aggressivo di DELETE . Questo potrebbe essere un problema per le tabelle con un carico pesante e simultaneo.

Se TRUNCATE non è un’opzione o in genere per i tavoli di piccole e medie dimensioni esiste una tecnica simile con un CTE che modifica i dati (Postgres 9.1 +):

 WITH del AS (DELETE FROM tbl RETURNING *) INSERT INTO tbl SELECT DISTINCT * FROM del; -- ORDER BY id; -- optionally "cluster" data while being at it. 

Più lento per i grandi tavoli, perché TRUNCATE è più veloce lì. Ma potrebbe essere più veloce (e più semplice!) Per piccoli tavoli.

Se non hai affatto oggetti dipendenti, potresti creare una nuova tabella ed eliminare quella vecchia, ma difficilmente guadagni nulla rispetto a questo approccio universale.

Per tabelle molto grandi che non si adattano alla RAM disponibile , la creazione di una nuova tabella sarà notevolmente più veloce. Dovrai pesare questo contro possibili problemi / sovraccarico con oggetti dipendenti.

Puoi usare oid o ctid, che normalmente è una colonna “non visibile” nella tabella:

 DELETE FROM table WHERE ctid NOT IN (SELECT MAX(s.ctid) FROM table s GROUP BY s.column_has_be_distinct); 

La funzione finestra PostgreSQL è utile per questo problema.

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

Vedi Eliminazione di duplicati .

Query generalizzata per eliminare i duplicati:

 DELETE FROM table_name WHERE ctid NOT IN ( SELECT max(ctid) FROM table_name GROUP BY column1, [column 2, ...] ); 

La colonna ctid è una colonna speciale disponibile per ogni tabella ma non visibile a meno che non sia specificatamente menzionata. Il valore della colonna ctid è considerato univoco per ogni riga in una tabella.

Da una vecchia mailing list postgresql.org :

 create table test ( a text, b text ); 

Valori unici

 insert into test values ( 'x', 'y'); insert into test values ( 'x', 'x'); insert into test values ( 'y', 'y' ); insert into test values ( 'y', 'x' ); 

Valori duplicati

 insert into test values ( 'x', 'y'); insert into test values ( 'x', 'x'); insert into test values ( 'y', 'y' ); insert into test values ( 'y', 'x' ); 

Un altro doppio duplicato

 insert into test values ( 'x', 'y'); select oid, a, b from test; 

Seleziona le righe duplicate

 select o.oid, oa, ob from test o where exists ( select 'x' from test i where ia = oa and ib = ob and i.oid < o.oid ); 

Elimina le righe duplicate

Nota: PostgreSQL non supporta gli alias sulla tabella menzionata nella clausola from di una cancellazione.

 delete from test where exists ( select 'x' from test i where ia = test.a and ib = test.b and i.oid < test.oid ); 

Ho appena usato con successo la risposta di Erwin Brandstetter per rimuovere i duplicati in una tabella di join (un tavolo privo dei suoi ID principali), ma ho scoperto che c’è un avvertimento importante.

Includendo ON COMMIT DROP significa che la tabella temporanea verrà rilasciata alla fine della transazione. Per me, questo significava che il tavolo temporaneo non era più disponibile quando sono arrivato per inserirlo!

Ho appena fatto CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; e tutto ha funzionato bene.

La tabella temporanea non viene rilasciata alla fine della sessione.

Questa funzione rimuove i duplicati senza rimuovere gli indici e lo fa su qualsiasi tabella.

Utilizzo: select remove_duplicates('mytable');

 ---
 --- remove_duplicates (tablename) rimuove i record duplicati da una tabella (convertire da set a set univoco)
 ---
 CREA O SOSTITUISCI FUNZIONE remove_duplicates (testo) RETURNS vuoto AS $$
 DICHIARARE
   nome-tabl ALIAS PER $ 1;
 INIZIO
   ESEGUI 'CREA TABELLA TEMPORANEA _DISTINCT_' ||  tablename ||  'AS (SELECT DISTINCT * FROM' || tablename || ');';
   ESEGUI 'CANCELLA DA' ||  tablename ||  ';';
   ESEGUI "INSERISCI IN" ||  tablename ||  '(SELEZIONA * DA _DISTINCT_' || nometabella || ');';
   ESEGUI 'DROP TABLE _DISTINCT_' ||  tablename ||  ';';
   RITORNO;
 FINE;
 $$ LINGUA plpgsql;
 DELETE FROM table WHERE something NOT IN (SELECT MAX(s.something) FROM table As s GROUP BY s.this_thing, s.that_thing); 

Se hai solo una o poche voci duplicate, e sono effettivamente duplicate (cioè appaiono due volte), puoi usare la colonna ctid “nascosta”, come proposto sopra, insieme a LIMIT :

 DELETE FROM mytable WHERE ctid=(SELECT ctid FROM mytable WHERE […] LIMIT 1); 

Questo cancellerà solo la prima delle righe selezionate.

Per prima cosa, devi decidere quali dei tuoi “duplicati” manterrai. Se tutte le colonne sono uguali, OK, puoi eliminarle tutte … Ma forse vuoi mantenere solo il più recente, o qualche altro criterio?

Il modo più veloce dipende dalla tua risposta alla domanda di cui sopra e anche dalla% di duplicati sul tavolo. Se butti via il 50% delle tue file, è meglio fare CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ; e se elimini l’1% delle righe, usare DELETE è meglio.

Inoltre, per operazioni di manutenzione come questa, è generalmente consigliabile impostare work_mem su una buona parte della RAM: eseguire EXPLAIN, controllare il numero N di sort / hashes e impostare work_mem sulla RAM / 2 / N. Utilizzare molta RAM; è buono per la velocità. Finché hai una sola connessione simultanea …

Sto lavorando con PostgreSQL 8.4. Quando ho eseguito il codice proposto, ho scoperto che non stava effettivamente rimuovendo i duplicati. Nell’esecuzione di alcuni test, ho scoperto che aggiungendo “DISTINCT ON (duplicate_column_name)” e “ORDER BY duplicate_column_name” ha funzionato. Non sono un guru SQL, ho trovato questo nel PostgreSQL 8.4 SELECT … DISTINCT doc.

 CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$ DECLARE tablename ALIAS FOR $1; duplicate_column ALIAS FOR $2; BEGIN EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT ON (' || duplicate_column || ') * FROM ' || tablename || ' ORDER BY ' || duplicate_column || ' ASC);'; EXECUTE 'DELETE FROM ' || tablename || ';'; EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');'; EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';'; RETURN; END; $$ LANGUAGE plpgsql; 

Funziona molto bene ed è molto veloce:

 CREATE INDEX otherTable_idx ON otherTable( colName ); CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable; 
 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); 

Elimina i duplicati per colonna / i e mantieni la riga con l’id più basso. Il pattern è tratto dal wiki di postgres

Usando le CTE puoi ottenere una versione più leggibile di quanto sopra attraverso questo

 WITH duplicate_ids as ( SELECT id, rnum FROM num_of_rows WHERE rnum > 1 ), num_of_rows as ( SELECT id, ROW_NUMBER() over (partition BY column1, column2, column3 ORDER BY id) AS rnum FROM tablename ) DELETE FROM tablename WHERE id IN (SELECT id from duplicate_ids) 
 CREATE TABLE test (col text); INSERT INTO test VALUES ('1'), ('2'), ('2'), ('3'), ('4'), ('4'), ('5'), ('6'), ('6'); DELETE FROM test WHERE ctid in ( SELECT t.ctid FROM ( SELECT row_number() over ( partition BY col ORDER BY col ) AS rnum, ctid FROM test ORDER BY col ) t WHERE t.rnum >1);