Comment supprimer les doublons?

certaines de ces approches semblent un peu compliquées, et je le fais généralement comme:

donnée table table , je veux l'unique sur (field1, field2) garder la ligne avec le max field3:

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

Par exemple, j'ai une table user_accounts , et je veux ajouter une contrainte unique sur la messagerie électronique, mais j'ai quelques doublons. Dites aussi que je veux garder la plus récente créée (max id parmi les doublons).

DELETE FROM user_accounts USING user_accounts ua2
  WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;

• Note - USING n'est pas un SQL standard, c'est une extension PostgreSQL (mais très utile), mais la question originale mentionne spécifiquement PostgreSQL.

au lieu de créer un nouveau tableau, vous pouvez également réinsérer des lignes uniques dans le même tableau après l'avoir tronqué. Faites tout dans une transaction . En option, vous pouvez laisser tomber la table temporaire à la fin de la transaction automatiquement avec ON COMMIT DROP . Voir ci-dessous.

cette approche n'est utile que lorsqu'il y a beaucoup de lignes à supprimer de tout le tableau. Pour quelques copies, utilisez un simple DELETE .

vous avez mentionné des millions de lignes. Pour faire l'opération rapide vous voulez allouer assez tampons temporaires pour la session. Le paramètre doit être ajusté avant n'importe quel tampon temp est utilisé dans votre session actuelle. Découvrez la taille de votre table:

SELECT pg_size_pretty(pg_relation_size('tbl'));

Set temp_buffers en conséquence. Arrondir généreusement parce que la représentation en mémoire a besoin d'un peu plus de 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;

cette méthode peut être supérieure à la création d'une nouvelle table si selon les objets existent. Vues, index, clés étrangères ou autres objets se référant à la table. TRUNCATE vous fait commencer avec une ardoise propre de toute façon (nouveau fichier en arrière-plan) et est beaucoup plus rapide que DELETE FROM tbl avec de grandes tables ( DELETE peut en fait être plus rapide avec de petites tables).

pour les grandes tables, il est régulièrement plus rapide pour déposer des index et des clés étrangères, remplir la table et recréer ces objets. En ce qui concerne les contraintes fk, vous devez être certain que les nouvelles données sont valides, bien sûr, ou vous allez rencontrer une exception en essayant de créer le fk.

noter que TRUNCATE nécessite un verrouillage plus agressif que DELETE . Cela peut poser un problème pour les tables dont la charge est lourde et simultanée.

Si TRUNCATE n'est pas une option, ou, plus généralement, pour les petites et moyennes tables il y a une technique similaire avec un données-modification de la CTE (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.

plus lent pour les grandes tables, parce que TRUNCATE est plus rapide là. Mais peut-être plus rapide (et plus simple!) pour les petites tables.

si vous n'avez aucun objet dépendant du tout, vous pourriez créer une nouvelle table et supprimer l'ancienne, mais vous gagnez à peine rien au-dessus de cette approche universelle.

pour les très grandes tables qui ne rentreraient pas dans RAM disponible , la création d'une nouvelle table sera considérablement plus rapide. Vous devrez soupeser ceci par rapport à d'éventuels problèmes / au-dessus avec des objets dépendant.

vous pouvez utiliser oid ou ctid, qui est normalement une colonne" non visible "dans le tableau:

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

la fonction de fenêtre PostgreSQL est pratique pour ce problème.

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

voir supprimer les doublons .

requête généralisée pour supprimer les doublons:

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

la colonne ctid est une colonne spéciale disponible pour chaque tableau mais non visible sauf mention expresse. La valeur de la colonne ctid est considérée comme unique pour chaque ligne d'un tableau.

à Partir de un vieux postgresql.org liste de diffusion :

create table test ( a text, b text );

valeurs uniques

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

Doublons

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 de plus du double de double

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

select oid, a, b from test;

sélectionner les lignes dupliquées

select o.oid, o.a, o.b from test o
    where exists ( select 'x'
                   from test i
                   where     i.a = o.a
                         and i.b = o.b
                         and i.oid < o.oid
                 );

supprimer les lignes doubles

Remarque: PostgreSQL dosn pas de support des alias sur le tableau mentionné dans la clause from de un supprimer.

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

je viens d'utiliser réponse D'Erwin Brandstetter avec succès pour supprimer les doublons dans une table de jointure (une table sans ses propres identifiants primaires), mais a constaté qu'il ya une mise en garde importante.

y compris ON COMMIT DROP signifie que la table temporaire sera supprimée à la fin de la transaction. Pour moi, cela signifiait que la table temporaire était plus disponible au moment où je suis allé l'insérer!

je viens de fait CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; et tout a bien fonctionné.

La table temporaire est abandonné à la fin de la session.

cette fonction supprime les doublons sans enlever les index et le fait à n'importe quelle table.

Utilisation: select remove_duplicates('mytable');

---
--- remove_duplicates(tablename) removes duplicate records from a table (convert from set to unique set)
---
CREATE OR REPLACE FUNCTION remove_duplicates(text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR ;
BEGIN
  EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT * FROM ' || tablename || ');';
  EXECUTE 'DELETE FROM ' || tablename || ';';
  EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
  EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
  RETURN;
END;
$$ LANGUAGE plpgsql;

DELETE FROM table
  WHERE something NOT IN
    (SELECT     MAX(s.something)
      FROM      table As s
      GROUP BY  s.this_thing, s.that_thing);

si vous n'avez qu'une ou quelques entrées dupliquées, et qu'elles sont en effet dupliquées (c'est-à-dire qu'elles apparaissent deux fois), vous pouvez utiliser la colonne "hidden " ctid , comme proposé ci-dessus, ainsi que LIMIT :

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

ceci supprimera seulement la première des lignes sélectionnées.

tout d'Abord, vous devez décider de votre "doublons", vous garder. Si toutes les colonnes sont égales, OK, vous pouvez supprimer l'un d'eux... Mais peut-être voulez-vous garder seulement le plus récent, ou un autre critère?

le chemin le plus rapide dépend de votre réponse à la question ci-dessus, et aussi du pourcentage de doublons sur le tableau. Si vous jetez 50% de vos lignes, vous êtes mieux de faire CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ; , et si vous supprimez 1% des lignes, en utilisant supprimer est mieux.

aussi pour des opérations de maintenance comme celle-ci, il est généralement bon de régler work_mem sur une bonne partie de votre RAM: run EXPLAIN, check the number N of sorts/hashes, and set work_mem to your RAM / 2 / N. utilisez beaucoup de RAM; c'est bon pour la vitesse. Tant que vous n'avez qu'une connexion concurrente...

je travaille avec PostgreSQL 8.4. Quand j'ai lancé le code proposé, j'ai découvert que ce n'était pas supprimer les doublons. En faisant quelques tests, j'ai trouvé que l'ajout de la "DISTINCT ON (duplicate_column_name)" et "ordre par duplicate_column_name" ont fait l'affaire. Je ne suis pas un gourou SQL, j'ai trouvé ça dans la sélection PostgreSQL 8.4...DISTINCTES doc.

CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR ;
  duplicate_column ALIAS FOR ;
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;

Cela fonctionne très bien et est très rapide:

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

supprimer les doublons par colonne (s) et garder la ligne avec le numéro d'identification le plus bas. Le modèle est tiré du Postgres wiki

en utilisant CTEs vous pouvez obtenir une version plus lisible de ce qui précède à travers ce

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