如何删除重复条目？

Question

我需要在现有的表中添加唯一约束。但这个表已经有数百万行数据，并且其中许多行违反了我需要添加的唯一约束。

如何最快速地删除这些问题行？我有一个SQL语句可以查找并删除重复行，但运行时间非常长。是否有其他解决方法？例如在添加约束后备份表，然后恢复数据？

- gjrwebber

Answer 1

这些方法中有一些看起来有点复杂，我通常会按照以下方式进行操作：

给定表格 table，想要在 (field1, field2) 上去重并保留 field3 最大的行：

DELETE FROM table USING table alias 
  WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND
    table.max_field < alias.max_field

例如，我有一个名为user_accounts的表，我想在电子邮件上添加唯一约束，但是有一些重复项。假设我想保留最近创建的一个（在重复项中具有最大的id值）。

DELETE FROM user_accounts USING user_accounts ua2
  WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;

• 注意 - USING 不是标准的 SQL，它是 PostgreSQL 的一个扩展（但非常有用），但原问题明确提到了 PostgreSQL。

(Note - USING 不是标准的 SQL，它是 PostgreSQL 的一个扩展（但非常有用），但原问题明确提到了 PostgreSQL。)

- Tim

---

第二种方法在PostgreSQL上非常快速！谢谢。 - Eric Bowman - abstracto -

@Tim，您能更好地解释一下在PostgreSQL中USING是做什么的吗？ - Fopa Léon Constantin

这绝对是最好的答案。即使您的表中没有序列列可用于ID比较，暂时添加一个进行此简单处理也是值得的。 - Shane

我刚刚检查了一下。答案是肯定的，它会这样做。使用小于号（<）只会留下最大的ID，而大于号（>）只会留下最小的ID，并删除其余的。 - André C. Andersen

@Shane 可以使用：WHERE table1.ctid<table2.ctid - 无需添加序列列。 - alexkovelsky

Answer 2

例如，您可以：

CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

- just somebody

---

你能让一组列变得不同吗？也许可以使用 "SELECT DISTINCT (t.a, t.b, t.c), * FROM t" ？ - gjrwebber

DISTINCT ON (a, b, c)：在 PostgreSQL 数据库中，用于指定查询结果按照给定的一组列（a、b 和 c）进行去重。它会保留每组唯一的值，并返回其中的第一行。具体用法和示例请参考 PostgreSQL 8.2 文档中的 SQL-SELECT 章节。 - just somebody

更易于输入的方式是：CREATE TABLE tmp AS SELECT ...;，这样您甚至不需要弄清楚tmp的布局。 :) - Randal Schwartz

这个答案实际上有几个问题。@Randal指出了其中一个。在大多数情况下，特别是当你有依赖对象（如索引、约束、视图等）时，更好的方法是使用实际的临时表，截断原始表并重新插入数据。 - Erwin Brandstetter

关于索引您是正确的。删除并重新创建会更快。但是，其他相关对象会破坏或完全阻止删除表格 - 这是OP在复制后才能发现的，因此“最快的方法”就不成立了。尽管如此，关于downvote您仍是正确的。它是没有根据的，因为它不是一个坏答案。只是没有那么好。您本可以添加一些有关索引或相关对象的指针或者像您在评论中做的那样提供一个链接或者任何解释。我想我对人们投票的方式感到沮丧。已经取消了downvote。 - Erwin Brandstetter

Answer 3

不需要创建新表，您也可以在将其截断后重新插入唯一行到同一张表中。在一个事务中完成所有操作。

这种方法仅适用于需要从整个表中删除大量行的情况。对于只有少量重复项的情况，请使用普通的DELETE。

您提到了数百万行。为了使操作更加快速，您需要为会话分配足够的临时缓冲区。该设置必须在当前会话中的任何临时缓冲区被使用之前进行调整。找出您的表的大小：

SELECT pg_size_pretty(pg_relation_size('tbl'));

将temp_buffers设置为比这个值稍高一点。

SET temp_buffers = 200MB;   -- example value

BEGIN;

CREATE TEMP TABLE t_tmp AS  -- retains temp for duration of session
SELECT DISTINCT * FROM tbl  -- DISTINCT folds duplicates
ORDER  BY id;               -- optionally "cluster" data

TRUNCATE tbl;

INSERT INTO tbl
SELECT * FROM t_tmp;        -- retains order (implementation detail)

COMMIT;

如果存在引用表的视图、索引、外键或其他对象，则此方法可能优于创建新表。TRUNCATE 无论如何都会让您从干净的板 slate 开始（在后台创建新文件），并且比使用 DELETE FROM tbl 快得多（对于大型表格，DELETE 实际上可能更快）。对于大型表格，删除索引和外键（FK），重新填充表格并重新创建这些对象通常更快。至于 FK 约束，当然，您必须确保新数据有效，否则在尝试创建 FK 时会遇到异常。请注意，TRUNCATE 需要比 DELETE 更积极地锁定。这可能是具有重度并发负载的表格的问题。但它仍然比完全删除和替换表格不那么破坏性。

如果无法使用TRUNCATE或者对于小到中等大小的表格，可以使用类似技巧，使用数据修改CTE（Postgres 9.1+）：

WITH del AS (DELETE FROM tbl RETURNING *)
INSERT INTO tbl
SELECT DISTINCT * FROM del;
ORDER  BY id; -- optionally "cluster" data while being at it.

对于大表而言，由于TRUNCATE速度更快，因此速度会变慢。但是对于小表而言，可能更快（也更简单）。

如果您完全没有依赖对象，您可以创建一个新表并删除旧表，但是与这种通用方法相比，您几乎不会获得任何好处。

对于无法适应可用内存的非常大的表格，创建一个新表将更快。您需要权衡这一点，以免出现依赖对象的麻烦/开销。

- Erwin Brandstetter

---

我也用过这种方法。然而，可能是个人的原因，我的临时表在截断后被删除，并且无法再访问... 如果临时表已经成功创建并可用，请小心执行这些步骤。 - xlash

警告：小心 +1 给 @xlash -- 我不得不重新导入我的数据，因为在 TRUNCATE 后临时表不存在。正如 Erwin 所说，一定要确保在截断表之前它存在。请参考 @codebykat 的答案。 - Jordan Arsenault

@JordanArseno：我切换到了一个没有 ON COMMIT DROP 的版本，这样那些错过我写的“在一个事务中”的人就不会丢失数据了。并且我添加了 BEGIN / COMMIT 来澄清“一个事务”。 - Erwin Brandstetter

@ErwinBrandstetter 我认为当原始表中没有太多重复项需要删除时，这种解决方案的效率会降低。而且当根本没有重复项时，情况会更糟。您能否提供一些改进措施，例如避免在t_tmp和原始表具有相同行数（=>没有重复项）时进行截断。在这种情况下，DELETE是否更适合？ - Fopa Léon Constantin

使用 USING 的解决方案在包含 1400 万条记录的表上花费了超过 3 小时。而使用 temp_buffers 的解决方案只需 13 分钟。谢谢。 - Sergey Tsibel

Answer 4

您可以使用oid或ctid，这些通常是表格中的“非可见”列：

DELETE FROM table
 WHERE ctid NOT IN
  (SELECT MAX(s.ctid)
    FROM table s
    GROUP BY s.column_has_be_distinct);

- Jan Marek

---

如果要进行原地删除，则NOT EXISTS应该更快：DELETE FROM tbl t WHERE EXISTS (SELECT 1 FROM tbl t1 WHERE t1.dist_col = t.dist_col AND t1.ctid > t.ctid)-- 或使用任何其他列或一组列进行排序以选择幸存者。 - Erwin Brandstetter

@ErwinBrandstetter，您提供的查询是否应该使用NOT EXISTS？ - John

@John：这里必须使用EXISTS。理解为：“删除所有存在具有相同值但ctid更大的dist_col行的任何其他行”。每个重复组中唯一幸存者将是具有最大ctid的那个。 - Erwin Brandstetter

如果只有少量重复的行，则最简单的解决方案是使用 LIMIT（如果您知道重复项的数量）。 - Skippy le Grand Gourou

Answer 5

PostgreSQL窗口函数在解决这个问题时非常方便。

DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             row_number() over (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);

请参见删除重复数据。

- shekwi

---

而使用“ctid”代替“id”，这实际上适用于完全重复的行。 - bradw2k

很棒的解决方案。我必须为一个拥有十亿条记录的表执行此操作。我在内部SELECT中添加了WHERE以分块处理。 - Jan

Answer 6

通用查询以删除重复项：

DELETE FROM table_name
WHERE ctid NOT IN (
  SELECT max(ctid) FROM table_name
  GROUP BY column1, [column 2, ...]
);

ctid列是每个表都有的特殊列，但除非特别提到，否则不可见。在表中，ctid列值被认为是每一行唯一的。请参阅PostgreSQL系统列以了解更多关于ctid的信息。

- naXa stands with Ukraine

---

唯一通用的答案！无需使用自我/笛卡尔连接即可运行。值得补充的是，正确指定“GROUP BY”子句非常重要 - 这应该是现在被违反的“唯一性标准”，或者如果您想检测重复项的关键。如果指定错误，则不会正常工作。 - msciwoj

Answer 7

来自postgresql.org旧邮件列表：

create table test ( a text, b text );

独特值

insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );

重复值

insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );

再来一个双倍复制

insert into test values ( 'x', 'y');

select oid, a, b from test;

选择重复行

select o.oid, o.a, o.b from test o
    where exists ( select 'x'
                   from test i
                   where     i.a = o.a
                         and i.b = o.b
                         and i.oid < o.oid
                 );

删除重复行

注意：PostgreSQL不支持在from子句中提到的表上使用别名进行删除。

delete from test
    where exists ( select 'x'
                   from test i
                   where     i.a = test.a
                         and i.b = test.b
                         and i.oid < test.oid
             );

- Bhavik Ambani

---

你的解释非常聪明，但是你漏掉了一个点，在创建表时必须指定oid，否则会显示错误消息。 - Kalanidhi

@Kalanidhi 感谢您对答案改进的评论，我会考虑这一点。 - Bhavik Ambani

这个信息真的来自于http://www.postgresql.org/message-id/[e-mail]。 - Martin F

如果 'oid' 出现错误，您可以使用系统列 'ctid'。 - sul4bh

Answer 8

我刚刚成功地使用Erwin Brandstetter's answer来删除连接表中的重复项（一张缺少自己主键的表），但发现有一个重要的注意事项。

包括ON COMMIT DROP意味着临时表将在事务结束时被删除。对我来说，这意味着在我插入它之前，临时表已经不再可用！

我刚刚执行了CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl;，一切都运行良好。

临时表会在会话结束时被删除。

- codebykat

Answer 9

这个函数可以在不删除索引的情况下，从任何表中去除重复记录。

用法: select remove_duplicates('mytable');

---
--- remove_duplicates(tablename) 从表中删除重复记录（从集合转换为唯一集合）
---
CREATE OR REPLACE FUNCTION remove_duplicates(text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR $1;
BEGIN
  EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT * FROM ' || tablename || ');';
  EXECUTE 'DELETE FROM ' || tablename || ';';
  EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
  EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
  RETURN;
END;
$$ LANGUAGE plpgsql;

- Ole Tange

Answer 10

DELETE FROM table
  WHERE something NOT IN
    (SELECT     MAX(s.something)
      FROM      table As s
      GROUP BY  s.this_thing, s.that_thing);

- Secko

---

这就是我目前正在做的事情，但运行时间非常长。 - gjrwebber

如果表中多行具有相同的“something”列值，那么这个方法会失败吗？ - shreedhar