如何在PostgreSQL中查找重复记录 [英] How to find duplicate records in PostgreSQL

问题描述

我有一个名为 user_links的PostgreSQL数据库表，该表当前允许以下重复字段：

I have a PostgreSQL database table called "user_links" which currently allows the following duplicate fields:

year, user_id, sid, cid

唯一约束当前是第一个名为 id的字段，但是我现在正在寻找添加约束以确保年， user_id ， sid 和 cid 都是唯一的，但我无法应用该约束，因为已经存在违反此约束的重复值。

The unique constraint is currently the first field called "id", however I am now looking to add a constraint to make sure the year, user_id, sid and cid are all unique but I cannot apply the constraint because duplicate values already exist which violate this constraint.

有找到所有重复项的方法吗？

Is there a way to find all duplicates?

推荐答案

基本思想是使用带有计数聚合的嵌套查询：

The basic idea will be using a nested query with count aggregation:

select * from yourTable ou
where (select count(*) from yourTable inr
where inr.sid = ou.sid) > 1

您可以调整内部查询中的where子句以缩小搜索范围。

You can adjust the where clause in the inner query to narrow the search.

对于注释中提到的问题，还有一个很好的解决方案（但并非所有人都读过）：

There is another good solution for that mentioned in the comments, (but not everyone reads them):

select Column1, Column2, count(*)
from yourTable
group by Column1, Column2
HAVING count(*) > 1

或更短：

SELECT (yourTable.*)::text, count(*)
FROM yourTable
GROUP BY yourTable.*
HAVING count(*) > 1

这篇关于如何在PostgreSQL中查找重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！