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

Voici une solution en utilisant PARTITION BY et le virtuel ctid colonne qui fonctionne comme une clé primaire, du moins au sein d'une même session :

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

Une sous-requête est utilisée pour marquer toutes les lignes comme dupliquées ou non, en fonction du fait qu'elles partagent les mêmes "colonnes clés", mais pas les mêmes ctid comme la "première" trouvée dans la "partition" des lignes partageant les mêmes clés.

En d'autres termes, le terme "premier" est défini comme suit :

• min(ctid) OVER (PARTITION BY key_column1, key_column2 [...])

Ensuite, toutes les lignes où is_duplicate est vrai sont supprimés par leur ctid .

Extrait de la documentation, ctid représente ( l'accent mine) :

Emplacement physique de la version de la ligne dans sa table. Notez que bien que le ctid puisse être utilisé pour localiser très rapidement la version de la ligne Le ctid d'une ligne changera si elle est mise à jour ou déplacée par VACUUM FULL. Par conséquent, le ctid est inutile en tant qu'identifiant de ligne à long terme. Une clé primaire doit être utilisée pour identifier les lignes logiques.

Answer 2

Voici une autre solution, qui a fonctionné pour moi.

delete from table_name a using table_name b
where a.id < b.id
  and a.column1 = b.column1;

Answer 3

Bien, aucune de ces solutions ne fonctionnerait si l'identifiant est dupliqué, ce qui est mon cas d'utilisation, alors la solution est simple :

myTable:
id  name
0   value
0   value
0   value
1   value1
1   value1

create dedupMyTable as select distinct * from myTable;
delete from myTable;
insert into myTable select * from dedupMyTable;

select * from myTable;
id  name
0   value
1   value1

Vous ne devriez pas avoir de doublons dans votre table à moins qu'elle n'ait pas de contraintes PK ou qu'elle ne les supporte tout simplement pas, comme les tables Hive/data lake.

Il est préférable de faire attention lors du chargement des données afin d'éviter les doublons sur les identifiants.

Answer 4

DELETE FROM tracking_order 
WHERE 
    mvd_id IN (---column you need to remove duplicate
        SELECT 
            mvd_id 
        FROM (
            SELECT                         
                mvd_id,thoi_gian_gui,
                ROW_NUMBER() OVER (
                    PARTITION BY mvd_id
                    ORDER BY thoi_gian_gui desc) AS row_num
            FROM 
                tracking_order
        ) s_alias
        WHERE row_num > 1)
    AND thoi_gian_gui in ( --column you used to compare to delete duplicates, eg last update time
        SELECT 
                thoi_gian_gui 
        FROM (
            SELECT                         
                thoi_gian_gui,
                ROW_NUMBER() OVER (
                    PARTITION BY mvd_id
                    ORDER BY thoi_gian_gui desc) AS row_num
            FROM 
                tracking_order
        ) s_alias
        WHERE row_num > 1)

Mon code, je supprime tous les doublons de la ligne 7800445 et ne garde qu'une copie de chaque ligne avec 7 min 28 secs. entrer la description de l'image ici

Answer 5

Cela a bien fonctionné pour moi. J'avais une table, terms, qui contenait des valeurs en double. J'ai lancé une requête pour remplir une table temporaire avec toutes les lignes en double. J'ai ensuite exécuté une instruction de suppression avec ces identifiants dans la table temporaire. value est la colonne qui contient les doublons.

        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)