如何在PostgreSQL中查找重复记录

Question

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

year, user_id, sid, cid

唯一约束当前是第一个名为“id”的字段，但是我现在希望添加一个约束，以确保年份、用户id、sid和cid都是唯一的，但我无法应用约束，因为已经存在违反此约束的重复值。

有没有找到所有重复的方法？

Answer 1

为了简化，我假设您希望仅对列year应用唯一约束，并且主键是名为id的列。

为了找到你应该运行的重复值，

SELECT year, COUNT(id)
FROM YOUR_TABLE
GROUP BY year
HAVING COUNT(id) > 1
ORDER BY COUNT(id);

使用上面的sql语句可以得到一个表，其中包含表中所有重复的年份。为了删除除最新的重复项之外的所有重复项，您应该使用上面的sql语句。

DELETE
FROM YOUR_TABLE A USING YOUR_TABLE_AGAIN B
WHERE A.year=B.year AND A.id<B.id;

Answer 2

从“使用PostgreSQL查找重复行”中可以看到以下智能解决方案：

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id asc) AS Row
  FROM tbl
) dups
where 
dups.Row > 1

Answer 3

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

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

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

对于评论中提到的问题，还有另一个很好的解决方案（但不是每个人都读）：

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