Tuesday 15 July 2014

Deleting Duplicate values in PostgreSQL

dev=# select ctid,* from test;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | RC
 (0,2) |  2 | RC
 (0,3) |  1 | RC
 (0,4) |  2 | RC
(4 rows)

dev=# select ctid,* from test where ctid not in ( select min(ctid) from test group by id,name);
 ctid  | id | name
-------+----+------
 (0,3) |  1 | RC
 (0,4) |  2 | RC
(2 rows)

dev=# delete from test where ctid not in ( select min(ctid) from test group by id,name);
DELETE 2
dev=# select ctid,* from test;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | RC
 (0,2) |  2 | RC
(2 rows)