I've got a table in Postgres that is chock full of articles. The articles have a url slug associated with them, which are used to display them as
example.com/pretty_name as opposed to
Unfortunately, when I started out, I enforced a unique constraint on urls, but neglected to do so on a case insensitive basis, and I'd like to right that wrong and start requiring urls be unique without regards to case.
As a first step to that, I need to fix all the duplicate urls already present in my database. How can I search the table for rows with duplicate urls on a case insensitive basis, and leave one row as is, while for the rest of the duplicates append something like '_2' to the end?
It's especially tricky, because I'm not 100% sure there aren't urls duplicated more than one time. I.e., I might have 3 duplicates on one url, in which case ideally I'd want the first to be
pretty_name, the second to be
pretty_name_2 and the third to be