我在Postgres有一张桌子，里面装满了文章。 这些文章有一个与它们相关的url slug，用于将它们显示为example.com/pretty_name ，而不是example.com\2343 。
不幸的是，当我开始时，我对网址强制执行了一个独特的约束，但是在不区分大小写的基础上忽略了这一点，我想纠正错误并开始要求网址是唯一的而不考虑大小写。
作为第一步，我需要修复已存在于我的数据库中的所有重复URL。 如何在不区分大小写的情况下在表中搜索具有重复URL的行，并按原样保留一行，而对于其余的重复项，在末尾附加类似“_2”的内容？
这是特别棘手的，因为我不是100％确定没有多次重复的网址。 即，我可能在一个网址上有3个重复，在这种情况下，理想情况下我希望第一个是pretty_name ，第二个是pretty_name_2 ，第三个是pretty_name_3 。
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 example.com\2343.
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 pretty_name_3.
最满意答案
如果您在桌面上有某种唯一ID：
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));如果您没有唯一ID：
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));If you have some sort of unique id on 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));If you don't have an unique id:
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));更多推荐
发布评论