Comment supprimer les entrées dupliquées ?

Question

Je dois ajouter une contrainte unique à une table existante. Tout va bien, sauf que la table compte déjà des millions de lignes et que nombre d'entre elles violent la contrainte unique que je dois ajouter.

Quelle est l'approche la plus rapide pour supprimer les lignes incriminées ? J'ai une instruction SQL qui trouve les doublons et les supprime, mais son exécution prend une éternité. Existe-t-il un autre moyen de résoudre ce problème ? Peut-être en sauvegardant la table, puis en la restaurant après l'ajout de la contrainte ?

Answer 1

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

Tableau donné table Je veux le rendre unique sur (field1, field2) en gardant la ligne avec le maximum 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 un tableau, user_accounts et je veux ajouter une contrainte unique sur l'email, mais j'ai quelques doublons. Disons aussi que je veux garder le plus récemment créé (id max 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 du SQL standard, c'est une extension PostgreSQL (mais une extension très utile), mais la question originale mentionne spécifiquement PostgreSQL.

Answer 2

Par exemple, vous pourriez :

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

Answer 3

Au lieu de créer une nouvelle table, vous pouvez également réinsérer des lignes uniques dans la même table après l'avoir tronquée. Tout faire en une seule transaction .

Cette approche n'est utile que lorsqu'il y a beaucoup de lignes à supprimer dans toute la table. Pour quelques doublons seulement, utilisez une simple commande DELETE .

Vous avez mentionné des millions de rangs. Pour que l'opération rapide vous voulez allouer suffisamment tampons temporaires pour la session. Le réglage doit être ajusté antes de tout tampon temporaire est utilisé dans votre session actuelle. Déterminez la taille de votre table :

SELECT pg_size_pretty(pg_relation_size('tbl'));

Définir temp_buffers au moins un peu plus que ça.

SET temp_buffers = 200MB;   -- example value

BEGIN;

CREATE TEMP TABLE t_tmp AS  -- retains temp for duration of session
SELECT DISTINCT * FROM tbl  -- DISTINCT folds duplicates
ORDER  BY id;               -- optionally "cluster" data

TRUNCATE tbl;

INSERT INTO tbl
SELECT * FROM t_tmp;        -- retains order (implementation detail)

COMMIT;

Cette méthode peut être supérieure à la création d'une nouvelle table. si les objets dépendants existent. Vues, index, clés étrangères ou autres objets faisant référence à la table. TRUNCATE vous fait de toute façon commencer avec une ardoise propre (nouveau fichier en arrière-plan) et est beaucoup plus vite 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 supprimer les index et les clés étrangères (FK), remplir à nouveau 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 rencontrerez des exceptions en essayant de créer la FK.

Notez que TRUNCATE nécessite un verrouillage plus agressif que DELETE . Cela peut poser un problème pour les tables soumises à une charge importante et simultanée. Mais c'est toujours moins perturbant que d'abandonner et de remplacer complètement la table.

Si TRUNCATE n'est pas une option ou généralement pour tables petites à moyennes il existe une technique similaire avec un CTE de modification des données (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, car TRUNCATE est plus rapide là-bas. Mais il peut être plus rapide (et plus simple !) pour les petites tables.

Si vous n'avez aucun objet dépendant, vous pouvez créer une nouvelle table et supprimer l'ancienne, mais vous ne gagnez pratiquement rien par rapport à cette approche universelle.

Pour les très grandes tables qui ne rentreraient pas dans RAM disponible en créant un nouveau sera considérablement plus rapide. Vous devrez peser le pour et le contre par rapport à d'éventuels problèmes ou frais généraux liés à des objets dépendants.

Answer 4

Vous pouvez utiliser l'oid ou le ctid, qui est normalement une colonne "non visible" dans la table :

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

Answer 5

La fonction fenêtre de 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);

Ver _Suppression des doublons_ .