as the title says, there is a table that i want to add a unique constraint to it based on 2 fields user_id and topic_id, but it might be that there are duplicates, or records where these 2 match, so the index creation would fail.
How could i check if there are duplicates and if so only keep the newest one, and delete the potential older ones?
to me querying the whole table and then doing it in the code is unappealing, so i’d be looking for something with more ecto or sql action
I’d do a subquery to select all user_id/topic_id combinations with more then 1 appearance and use that together with window functions to select all but the most recent entry with such a combination. Delete everything still in the resultset.