在数据库中查找重复项并重命名一个

Question

我在Postgres中有一张表，里面充满了文章。这些文章都有一个与之相关的url slug，用于将它们显示为example.com/pretty_name而不是example.com\2343。

可惜的是，当我刚开始时，在urls上实施了唯一约束，但忽略了不区分大小写的情况，并且我希望能够纠正这个错误，并开始要求urls无论大小写都是唯一的。

作为第一步，我需要修复数据库中已存在的所有重复urls。如何在不区分大小写的情况下搜索该表以查找具有重复urls的行，并将其中的一行保持不变，同时在其余重复项的末尾添加类似“_2”的内容？

特别棘手的是，我不能百分之百确定是否存在多次重复的urls。 也就是说，我可能会有一个url的3个副本，如果是这种情况，我希望第一个是pretty_name，第二个是pretty_name_2，第三个是pretty_name_3。

- William Jones

---

拥有一些模式将是提供高质量答案所必需的。 - µBio

架构非常简单。有一个名为Articles的表格，其中有一列名为url。还有其他相关信息需要了解吗？ - William Jones

哈哈，你上次问完这个问题后，我就有点预料到会有这个问题了 - 你的文章表上有一个ID列吗？ - rfusca

Answer 1

如果在表格上有某种唯一标识：

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));

- rfusca

---

请问您能否解释一下这些语句是如何工作的？这是在说要更新不具有最大ID但与另一条记录共享不区分大小写URL的_articles_记录吗？如果是这样，那么如果有多个匹配项会发生什么？它会将除具有最大ID的记录之外的所有记录的URL转换吗？ - seh

更新每组大小写不敏感的URL中不是最大ID的所有行。是的，它会将除了具有最大ID的记录之外的所有记录的URL转换。 - rfusca

再次感谢！我的个人数据库救星。为了捕捉多个重复项，也许我会一遍又一遍地运行相同的东西，同时递增数字，直到没有重复项为止。在后续运行中，我想我需要想出一种方法，告诉数据库首先削掉 _2 并将其替换为 _3。 - William Jones

你可以通过将“_2”替换为“_”||round(random()*100)来大大减少运行次数。虽然不是完美的，但如果只有少量变化，你很难得到重复结果。 - rfusca