My Notepad

Table RECORD has more than 4 millons records. There's a unique ID for each row - record_id. The two other attributes are node_id and recordtime_id. We know there are some duplicated rows in the table that have same node_id and recordtime_id. How to get rid of these duplicates?

in Postgresql.

Solution1:

DELETE
FROM  sometable
WHERE  someuniquekey NOT IN
(SELECT  MAX(dup.someuniquekey)
     FROM     sometable As dup
     GROUP BY  dup.dupcolumn1, dup.dupcolumn2, dup.dupcolum3)

Solution2:

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

But for large table, none of these solutions can be finished within sensible amount of time. I tried on a table with 4 m rows where about 0.1m duplicates need to be removed - no way to be done within 4 hours.

A "faster" way is to create a tmp table with distinct records only, remove all the rows from old table then copy rows back from distinct table:
1.create table recorddist as select distinct on ( node_id, recordtime_id) * from record;
2.  delete from record;
3. insert into record (record_id, node_id, recordtime_id) select record_id, node_id, recordtime_id from recorddist;

Step three can potentially take long time as well, depends on the number of rows after removing the duplications.




ref:
http://archives.postgresql.org/pgsql-sql/1999-03/msg00239.php
http://www.postgresonline.com/journal/index.php?/archives/22-Deleting-Duplicate-Records-in-a-Table.html
http://www.databasejournal.com/features/mssql/article.php/1438651/Removing-Duplicate-Records.htm