Deleting Duplicate values in PostgreSQL
pawan=# select ctid,* from test;
ctid | id | name
-------+----+------
(0,1) | 1 | ABC
(0,2) | 2 | ABC
(0,3) | 1 | ABC
(0,4) | 2 | ABC
(4 rows)
pawan=# select ctid,* from test where ctid not in ( select min(ctid) from test group by id,name);
ctid | id | name
-------+----+------
(0,3) | 1 | ABC
(0,4) | 2 | ABC
(2 rows)
pawan=# delete from test where ctid not in ( select min(ctid) from test group by id,name);
DELETE 2
pawan=# select ctid,* from test;
ctid | id | name
-------+----+------
(0,1) | 1 | ABC
(0,2) | 2 | ABC
(2 rows)