Search for duplicates in the database and rename

Question

I have a table in Postgres that is full of articles. Articles have an associated URL associated with them, which is used to display them as example.com/pretty_name, rather than example.com\2343.

Unfortunately, when I started, I applied a unique restriction on URLs, but neglected it on a case-insensitive basis, and I would like to fix this and start requiring URLs to be unique, regardless of the case.

As a first step to this, I need to fix all duplicate URLs already in my database. How can I search a table for rows with duplicate case-insensitive URLs and leave one row as is, and for the rest of the duplicates add something like "_2" to the end?

This is especially difficult because I am not 100% sure that the URLs are duplicated more than once. I could have 3 duplicates in one URL, in which case I would like the first one to be pretty_name, the second one pretty_name_2, and the third one pretty_name_3.

Answer 1

If you have some kind of unique identifier in the table:

UPDATE articles a1 set url = a1.url||'_2' 
WHERE a1.id not in (select max(a2.id) from articles a2 group by lower(a2.url));

:

UPDATE articles a1 set url = a1.url||'_2' 
WHERE a1.ctid not in (select max(a2.ctid) from articles a2 group by lower(a2.url));