本文介绍了在PostgreSQL中删除完全重复的行(只保留1)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在PostgreSQL 8.3.8数据库中有一个表,它没有键/约束,并且具有完全相同值的多个行。
I想删除所有重复的内容,只保留每一行的一个副本。
有一列特别(命名为key),可用于标识重复项即每个不同的键应该只存在一个条目。
我该怎么做? (理想情况下使用单个SQL命令)在这种情况下速度不是问题(只有几行)。
解决方案DELETE FROM dupes a WHERE a.ctid<> (SELECT min(b.ctid) FROM dupes b WHERE a.key = b.key);
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.
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").
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中删除完全重复的行(只保留1)
发布评论