I want to add a unique constraint on a table, eg
ALTER TABLE report ADD UNIQUE (date, scholar_id);
Each scholar should have only one report per day and the application checks whether there is an existing report for the scholar for the date but due to having a cluster of servers I occasionally do get duplicates.
Now I need to remove aproximately 700 items from the table.
I run a query like
SELECT date, scholar_id, count(*) FROM report GROUP BY date, scholar_id HAVING count(*) > 1;
And see about 548 rows. Some with counts = 3 or 4, most with count = 2.
These "duplicates" cause the ALTER TABLE to fail.
I am hoping to automate deleting these duplicates (all except 1 per scholar_id/date pair).
I imagine some way to get the pairs, and then per pair get all the rowids, and then DELETE FROM report WHERE id = any(...) AND NOT id = any(... limit 1);
Note: it is not important which one of the copies are removed. There are other columns and the other columns might not have identical values.
How to do this is the question...