如何在没有主键的情况下从Postgresql表中删除重复记录？

Question

我有一个像这样的表格

CREATE TABLE meta.fk_payment1
(
  id serial NOT NULL,
  settlement_ref_no character varying,
  order_type character varying,
  fulfilment_type character varying,
  seller_sku character varying,
  wsn character varying,
  order_id character varying,
  order_item_id bigint,
   ....
  );

我正在从CSV文件中插入数据，除了ID列以外，所有列都相同。

如果上传的CSV文件多次，则数据将重复。

但是ID不会重复，而且ID是主键。

因此，我想要在不使用主键的情况下删除所有重复行。

我必须在单个表上执行此操作。

- user3946530

---

创建复制表，将旧表中的不重复数据插入新表。SQL语句如下：create table newtab like oldtab; insert into newtab select distinct * from oldtab. - jarlh

我需要在单个表上执行此操作。 - user3946530

我对“id是主键”，但“不使用主键删除…”有点困惑。你的意思是csv中没有主键，但数据库中有一个主键吗？应该编辑问题以使其更清晰。 - Sami Kuhmonen

基于哪些列复制行？仅基于“id”列吗？您想保留哪些“重复项”？这有关系吗？是否有某种时间戳列，可以标识您想要保留的“最新”行？ - user330315

这个问题已经被问了很多次：http://stackoverflow.com/search?q=[postgresql]+delete+duplicates 具体来说是：https://dev59.com/5Ynca4cB1Zd3GeqP9VBX - user330315

Answer 1

您可以这样做 例如

DELETE FROM table_name
    WHERE   ctid NOT IN
    (SELECT     MAX(dt.ctid)
        FROM        table_name As dt
        GROUP BY    dt.*);

运行此查询

DELETE FROM meta.fk_payment1
    WHERE   ctid NOT IN
    (SELECT     MAX(dt.ctid)
        FROM        meta.fk_payment1 As dt
        GROUP BY    dt.*);

- Shubham Batra

Answer 2

将不同的数据复制到工作表fk_payment1_copy中。 最简单的方法是使用into。

SELECT max(id),settlement_ref_no ... 
INTO fk_payment1_copy
from fk_payment1
GROUP BY settlement_ref_no ... 

删除fk_payment1中的所有行。

delete from fk_payment1

将fk_payment1_copy表中的数据复制到fk_payment1表中

insert into fk_payment1
select id,settlement_ref_no ... 
from fk_payment1_copy

- Robert

---

@Shubhambatra 我不确定是否有最简单的方法。你不能创建新表，对吧？ - Robert

我可以创建，但是如果将来有任何列成为外键，则无法从fk_payment1中删除。是这样吗？ - user3946530

@Shubhambatra 对，这可能更加复杂。我建议你先将CSV导入工作表，然后再使用distinct将其复制到目标位置。 - Robert

@Shubhambatra ID已复制，因此无法更改。 - Robert

@Shubhambatra 现在我看到问题了，我修改了第一个查询，请现在尝试一下。 - Robert

Answer 3

如果表格不是很大，您可以这样做：

-- create temporary table and select distinct into it. 
CREATE TEMP TABLE tmp_table AS 
SELECT DISTINCT column_1, column_2 
FROM original_table ORDER BY column_1, column_2;

-- clear the original table 
TRUNCATE original_table;

-- copy data back in again 
INSERT INTO original_table(column_1, column_2) 
SELECT * FROM  tmp_table ORDER BY column_1, column_2;

-- clean up
DROP TABLE tmp_table

• 如果是较大的表格，可以从tmp_table创建中删除TEMP命令。
• 当使用JPA（Hibernate）生成无主键的@ElementCollection时，此解决方案非常有用。

- Ilan.K

Answer 4

关于问题中的主键部分有些不确定，但无论如何，id不需要是主键，只需要是唯一的即可。由于它是序列的，因此应该是唯一的。所以如果它具有唯一值，您可以这样做：

DELETE FROM fk_payment1 f WHERE EXISTS
  (SELECT * FROM fk_payment1 WHERE id<f.id
   AND settlement_ref_no=f.settlement_ref_no
   AND ...)

只需要在select查询中添加所有列。这样，具有相同值（除了id）并且在此行之后（按id排序）的所有行都将被删除。

（另外，使用fk_前缀命名表会使其看起来像是一个外键。）

- Sami Kuhmonen

---

我使用了这个，但它没有给出精确的输出。它漏掉了一些行。 - user3946530

Answer 5

在PG wiki中有一种简洁的方法。https://wiki.postgresql.org/wiki/Deleting_duplicates

此查询适用于具有相同column1、column2和column3的tablename的所有行。

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);

我正在测试这个去重功能，处理了60万行数据，最终得到20万个唯一的行。使用Group By和NOT IN语句的解决方案需要3小时以上，而这个解决方案只需要3秒钟。

- Ladas

---

如果我没记错的话，这将删除所有重复的副本，而不是留下一个副本。因此，如果您有两行具有相同的ID，则在运行此操作后，将不会剩余任何具有该ID的行。 - DragonBobZ