1

I have a table in PostgreSQL.And I have imported 5 Million records there .And accidentally imported some duplicate records there no I need to remove those duplicate records from the table.Here Table's primary key is the combination of four columns..

Kraj
  • 37
  • 4
  • 1
    If the table has a primary key, it wouldn't let you add duplicates. Are you saying that you didn't declare the primary key and that's how they got in there? – drquicksilver Apr 10 '13 at 13:14

2 Answers2

3
create table t2 as
select distinct on (col1, col2, col3, col4) *
from t;

drop table t;
alter table t2 rename to t;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0
DELETE FROM the_table a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM  the_table b
                 WHERE a.col_1 = b.col_1
                   AND a.col_2 = b.col_2
                   AND a.col_3 = b.col_3
                   AND a.col_4 = b.col_4);

This will keep one of the duplicates and delete the rest.