如何在没有唯一标识的情况下从PostgreSQL中删除重复项

Question

我在删除重复行方面遇到了一些困难。我认为user_id和time_id一起作为标识符，但是即使对于这些标识符也有重复。

user_id（文本），time_id（大整数），value1（数字）

user_id; time_id; value1| 
aaa;1;3|
aaa;1;3|
aaa;2;4|
baa;3;1|

在这种情况下，我该如何删除重复项？ 由于time_id中有16个不同的值，user_id中有15,000个不同的值，因此我尝试了类似于以下的方法，但是我没有唯一的id..

    DELETE FROM tablename a
     USING tablename b
    WHERE a.unique_id < b.unique_id
    AND   a.user_id = b.user_id
    time_id = 1       (repeat till time_id 16)

- no_name

---

几乎总是，您的Postgres版本是一个重要细节。 - Erwin Brandstetter

Answer 1

Postgres中的每个表都有一些隐藏的系统列。其中之一(ctid)根据定义是唯一的，并且可以在缺少主键时使用。

DELETE FROM tablename a
USING tablename b
WHERE a.ctid < b.ctid
AND a.user_id = b.user_id
AND a.time_id = b.time_id;

问题是由于缺乏主键引起的。使用隐藏列不应该是一种系统性的方法（请参见下面的评论）。一旦您删除重复项，您应该在(user_id, time_id)上创建一个主键，或者为此目的创建一个新的唯一列。

- klin

---

可以这样做，但通常不应该。对于新手来说，这可能不是最好的建议，尽管它完全有效。 - Craig Ringer

它简单、清晰且完全有效，为什么不应该使用它呢？ - klin

因为ctid并不是真正的公共接口，它是非标准的实现细节。而且没有什么能阻止PostgreSQL在未来的版本中以一种破坏这个查询的方式进行更改，无论是直接还是更糟糕的是隐晦地。 - Craig Ringer

我的建议很随意，这张表当然应该有一个主键。我记得你过去曾经给出类似的建议。 - klin

是的，我已经这样做了，并且我应该在那里添加一个适当的警告。谢谢。这并不是“坏”的，只是我认为新用户最好理解它不应该是首选解决方案。 - Craig Ringer

Answer 2

请谨慎使用删除建议，确保有必要时可以“撤销”。我认为您需要添加一个自动编号列来协助完成这项工作。

alter table tablename add column is_uniq serial

我建议使用row_number()函数来帮助识别您想保留的行（其中rn=1）和需要删除的行（其中rn>1）。请按照以下指南进行操作：

select *
   , ROW_NUMBER()over(partition by user_id, time_id, value1 order by is_uniq) as rn from tablename

我不确定是否还有其他列可用于排序，但如果有的话，您也可以将其包含在over子句中。一旦您拥有“is_uniq”列和rn>1行，就可以安全地删除不需要的行。

- Paul Maxwell

---

并且首先备份表格。在psql中使用\copy tablename to 'tablename-before-delete.csv'是一种简单的方法。 - Craig Ringer

Answer 3

如果你不想依赖于ctid（个人而言，我也不想），你可以添加一个唯一的列（例如serial）并将其用于标识目的。

---

CREATE TABLE lutser
        ( user_id text not null
        ,  time_i integer not null
        , value integer not null
        );
INSERT INTO lutser(user_id,time_i,value) VALUES
('aaa', 1, 3)
,('aaa', 1, 3)
,('aaa', 2, 4)
,('baa', 3, 1)
        ;

SELECT*FROM lutser;

ALTER TABLE lutser
        ADD COLUMN seq serial NOT NULL UNIQUE
        ;
SELECT*FROM lutser;

DELETE FROM lutser del
WHERE EXISTS(
        SELECT*FROM lutser x
        WHERE x.user_id=del.user_id
        AND x.time_i=del.time_i
        AND x.seq < del.seq
        );

ALTER TABLE lutser
        ADD PRIMARY KEY (user_id,time_i)
        ;

SELECT*FROM lutser;

- wildplasser