postgresql 删除重复行（不要删除所有重复行）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3777633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete duplicate rows (don't delete all duplicate)
提问by Avadhesh
I am using postgres. I want to delete Duplicate rows. The condition is that , 1 copy from the set of duplicate rows would not be deleted.
我正在使用 postgres。我想删除重复的行。条件是，不会删除重复行集中的 1 个副本。
i.e : if there are 5 duplicate records then 4 of them will be deleted.
即：如果有 5 个重复记录，则将删除其中的 4 个。
回答by Denis Valeev
Try the steps described in this article: Removing duplicates from a PostgreSQL database.
尝试本文中描述的步骤：从 PostgreSQL 数据库中删除重复项。
It describes a situation when you have to deal with huge amount of data which isn't possible to group by.
它描述了一种情况，当您必须处理无法处理的大量数据时group by。
A simple solution would be this:
一个简单的解决方案是这样的：
DELETE FROM foo
WHERE id NOT IN (SELECT min(id) --or max(id)
FROM foo
GROUP BY hash)
Where hashis something that gets duplicated.
哪里hash有重复的东西。
回答by adopilot
delete from table
where not id in
(select max(id) from table group by [duplicate row])
This is random (max Value) choice which row you need to keep. If you have aggre whit this please provide more details
这是您需要保留的随机（最大值）选择。如果您对此有所了解，请提供更多详细信息
回答by baklarz2048
The fastest is is join to the same table. http://www.postgresql.org/docs/8.1/interactive/sql-delete.html
最快的是加入同一个表。 http://www.postgresql.org/docs/8.1/interactive/sql-delete.html
CREATE TABLE test(id INT,id2 INT);
CREATE TABLE
mapy=# INSERT INTO test VALUES(1,2);
INSERT 0 1
mapy=# INSERT INTO test VALUES(1,3);
INSERT 0 1
mapy=# INSERT INTO test VALUES(1,4);
INSERT 0 1
DELETE FROM test t1 USING test t2 WHERE t1.id=t2.id AND t1.id2<t2.id2;
DELETE 2
mapy=# SELECT * FROM test;
id | id2
----+-----
1 | 4
(1 row)
回答by Sree Gottumukkala
delete from table t1
where rowid > (SELECT min(rowid) FROM table t2 group by
t2.id,t2.name );
