As for deleting all but one row in a duplicated group, you're going to
have to get at them by the oid or ctid columns perhaps.
The other idea is to run CREATE TABLE newtable AS SELECT DISTINCT * FROM
oldtable;.
I believe getting oid and/or ctid is not possible since it would not display/get duplicate records
in a "HAVING CLAUSE" since their oid/ctid wouldn't be the same.
And creating a newtable and use SELECT DISTINCT isn't an acceptable idea to the audit team when you have millions of records in a table in production server when you only need to remove let say 70 records from that table.
I tried another approach where I queried and insert the duplicate record (35 records) into
a new/temporary table. Then I created a stored procedure something like this:
--START
DECLARE
foo table;
BEGIN
FOR foo IN
SELECT * FROM newtable
LOOP
DELETE FROM oldtable
where oldtable.field1 = foo.field1
and oldtable.field2 = foo.field2
and oldtable.field3 = foo.field3
and oldtable.field4 = foo.field4
...';
END LOOP;
END;
-- END
Problem with this approach I got 35 duplicate records (count = 2)
from the new table and delete only 20 records from the oldtable.
If I am not mistaken it should delete 70 records.
I wonder
