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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.