15

I am using postgres. I want to delete Duplicate rows. The condition is that , 1 copy from the set of duplicate rows would not be deleted.

i.e : if there are 5 duplicate records then 4 of them will be deleted.

Oleg Svechkarenko
  • 2,508
  • 25
  • 30
Avadhesh
  • 4,519
  • 5
  • 33
  • 44

5 Answers5

23

Try the steps described in this article: Removing duplicates from a PostgreSQL database.

It describes a situation when you have to deal with huge amount of data which isn't possible to group by.

A simple solution would be this:

DELETE FROM foo
       WHERE id NOT IN (SELECT min(id) --or max(id)
                        FROM foo
                        GROUP BY hash)

Where hash is something that gets duplicated.

Denis Valeev
  • 5,975
  • 35
  • 41
  • 1
    doesn't this delete all the rows that don't have duplicates, too? – pomarc Feb 16 '12 at 07:52
  • @pomarc no, because there's this little equals sign (=) before 1 that tells us that we want to take min(id) of all possible groups even those that contain only one member; so, no worries, you won't delete data that is not duplicated – Denis Valeev Feb 16 '12 at 21:31
  • is the having count (*) >=1 neccesary ? i got the same result if I execute : DELETE FROM foo WHERE id NOT IN (SELECT min(id) FROM foo GROUP BY hash) – grteibo Feb 24 '12 at 13:26
  • @grteibo you are absolutely right, that's the way the deduplication is usually done; I don't know why I didn't notice that before; the idea of this answer is not so much the idea of deduplication itself but the fact that we calculate a hash for all the columns that we want to group by and then remove duplicates – Denis Valeev Feb 24 '12 at 21:14
2
delete from table
where not id in 
(select max(id) from table group by [duplicate row])

This is random (max Value) choice which row you need to keep. If you have aggre whit this please provide more details

adopilot
  • 4,340
  • 12
  • 65
  • 92
2

The fastest is is join to the same table. http://www.postgresql.org/docs/8.1/interactive/sql-delete.html

CREATE TABLE test(id INT,id2 INT);
CREATE TABLE
mapy=# INSERT INTO test VALUES(1,2);
INSERT 0 1
mapy=# INSERT INTO test VALUES(1,3);
INSERT 0 1
mapy=# INSERT INTO test VALUES(1,4);
INSERT 0 1

DELETE FROM test t1 USING test t2 WHERE t1.id=t2.id AND t1.id2<t2.id2;
DELETE 2
mapy=# SELECT * FROM test;
 id | id2 
----+-----
  1 |   4
(1 row)
baklarz2048
  • 10,699
  • 2
  • 31
  • 37
1
delete from table t1 
where rowid > (SELECT min(rowid) FROM table t2 group by 
               t2.id,t2.name );
Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Sree Gottumukkala
  • 21
  • 2
0
DELETE f1 from foo as f1, foo as f2 
       where f1.duplicate_column= f2.duplicate_column
             AND f1.id > f2.id;
Xpie
  • 43
  • 6