Supprimer les lignes dupliquées d'une petite table

Question

J'ai une table dans une base de données PostgreSQL 8.3.8, qui n'a pas de clés/contraintes, et qui a plusieurs lignes avec exactement les mêmes valeurs.

J'aimerais supprimer tous les doublons et ne conserver qu'une seule copie de chaque ligne.

Une colonne en particulier (appelée "clé") peut être utilisée pour identifier les doublons, c'est-à-dire qu'il ne doit y avoir qu'une seule entrée pour chaque "clé" distincte.

Comment faire ? (Idéalement, avec une seule commande SQL).
La vitesse n'est pas un problème dans ce cas (il n'y a que quelques lignes).

Answer 1

Une solution plus rapide est

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

Answer 2

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

Answer 3

C'est rapide et concis :

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

Voir aussi ma réponse à l'adresse suivante Comment supprimer les lignes dupliquées sans identifiant unique ? qui contient plus d'informations.

Answer 4

EXISTS est simple et parmi les plus rapides pour la plupart des distributions de données :

DELETE FROM dupes d
WHERE  EXISTS (
   SELECT FROM dupes
   WHERE  key = d.key
   AND    ctid < d.ctid
   );

À partir de chaque ensemble de lignes dupliquées (définies par des key ), ce qui permet de conserver la ligne avec le minimum de ctid .

Le résultat est identique à celui de la réponse actuellement acceptée par a_horse . Juste plus rapide porque EXISTS peut cesser d'évaluer dès que la première ligne incriminée est trouvée, alors que l'alternative avec min() doit prendre en compte tous lignes par groupe pour calculer le minimum. La vitesse n'est pas une préoccupation pour les cette question, mais pourquoi ne pas l'accepter ?

Vous pouvez ajouter un UNIQUE contrainte après le nettoyage, afin d'éviter que les doublons ne réapparaissent :

ALTER TABLE dupes ADD CONSTRAINT constraint_name_here UNIQUE (key);

À propos de la colonne du système ctid :

• La colonne système "ctid" est-elle légitime pour identifier les lignes à supprimer ?

Si une autre colonne est définie UNIQUE NOT NULL dans le tableau (comme un PRIMARY KEY ), alors utilisez-le à la place de ctid .

Si key peut être NULL et que vous n'en voulez qu'un seul, utilisez IS NOT DISTINCT FROM au lieu de = . Voir :

• Comment puis-je (ou puis-je) effectuer un SELECT DISTINCT sur plusieurs colonnes ?

Comme c'est plus lent, vous pourriez plutôt exécuter la requête ci-dessus telle quelle, et ceci en plus :

DELETE FROM dupes d
WHERE  key IS NULL
AND    EXISTS (
   SELECT FROM dupes
   WHERE  key IS NULL
   AND    ctid < d.ctid
   );

Et réfléchissez :

• Créer une contrainte unique avec des colonnes nulles

Pour les petites tables, index n'améliorent généralement pas les performances. Il n'est pas nécessaire de chercher plus loin.

Para grandes tables y peu les doublons, un index existant sur (key) peut aider (beaucoup).

Para principalement des doublons Un index peut être plus coûteux que bénéfique, car il doit être mis à jour en même temps. La recherche de doublons sans index est de toute façon plus rapide, car il y en a beaucoup et les doublons sont plus nombreux. EXISTS Il suffit d'en trouver un. Mais considérez un une approche totalement différente si vous pouvez vous le permettre (c'est-à-dire si l'accès simultané le permet) : Écrire les quelques lignes survivantes dans une nouvelle table. Cela permet également d'éliminer le gonflement de la table (et de l'index) dans le processus. Voir :

• Comment supprimer les doublons ?

Answer 5

J'ai essayé ceci :

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

fourni par Postgres wiki :

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