删除PostgreSQL中的重复记录 [英] Delete Duplicate Records in PostgreSQL

问题描述

我在PostgreSQL 8.3.8数据库中有一个表，该表上没有键/约束，并且有多行具有完全相同的值.

I have a table in a PostgreSQL 8.3.8 database, which has no keys/constraints on it, and has multiple rows with exactly the same values.

我想删除所有重复项，并且每行仅保留1个副本.

I would like to remove all duplicates and keep only 1 copy of each row.

特别是有一列(称为密钥")可用于标识重复项(即，每个不同的密钥"应该只存在一个条目).

There is one column in particular (named "key") which may be used to identify duplicates (i.e. there should only exist one entry for each distinct "key").

我该怎么做? (理想情况下，使用单个SQL命令) 在这种情况下，速度不是问题(只有几行).

How can I do this? (ideally with a single SQL command) Speed is not a problem in this case (there are only a few rows).

推荐答案

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

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