无法使用 PostgreSQL 删除重复的行

Question

我的查询删除整个表而不是重复的行。 视频作为证明：https://streamable.com/3s843

create table customer_info (
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone_number VARCHAR(50)
);
insert into customer_info (id, first_name, last_name, phone_number) values
(1, 'Kevin', 'Binley', '600-449-1059'),
(1, 'Kevin', 'Binley', '600-449-1059'),
(2, 'Skippy', 'Lam', '779-278-0889');

我的查询：

with t1 as (
select *, row_number() over(partition by id order by id) as rn
from customer_info)
delete
from customer_info 
where id in (select id from t1 where rn > 1);

Answer 1

您的查询将删除每组重复的所有行(因为所有行共享您选择的相同id- 这就是@wildplasser微妙的注释所暗示的(，并且只有最初唯一的行才能存活。因此，如果它"删除整个表">，则意味着根本没有唯一的行。

在查询中，重复仅由(id)定义，而不是按标题建议的整行定义。

无论哪种方式，都有一个非常简单的解决方案：

DELETE FROM customer_info c
WHERE  EXISTS (
SELECT FROM customer_info c1
WHERE  ctid < c.ctid
AND    c1 = c  -- comparing whole rows
);

由于您处理的是完全相同的行，因此区分它们的其余方法是内部元组 IDctid。

我的查询删除了存在具有较小ctid的相同行的所有行。因此，只有每组复制中的"第一"行幸存下来。

值得注意的是，在这种情况下，NULL值比较相等- 这很可能是所需的。手册：

SQL 规范要求按行比较以返回 NULL，如果 结果取决于比较两个 NULL 值或 NULL 和 非空。PostgreSQL仅在比较两个的结果时才这样做 行构造函数(如第 9.23.5 节(或比较行构造函数 到子查询的输出(如第 9.22 节(。在其他情况下 其中比较两个复合类型值，两个 NULL 字段值 被认为是平等的，[...]

如果重复仅由id定义(如查询所示(，那么这将起作用：

DELETE FROM customer_info c
WHERE  EXISTS (
SELECT FROM customer_info c1
WHERE  ctid < c.ctid
AND    id = c.id
);

但是，可能有一种更好的方法来决定保留哪些行，而不是ctid作为最后的手段！

显然，您将添加一个PRIMARY KEY以避免最初的困境再次出现。对于第二种解释，id是候选人。

相关：

• 如何(或我可以(在多列上选择不同？

关于ctid：

• 如何将 ctid 分解为页号和行号？

Answer 2

如果表没有键，则不能。

表具有唯一标识每一行的"键"。如果您的表没有任何键，那么您将无法区分一行和另一行。

我能想到的删除重复行的唯一解决方法是：

1. 在表上添加密钥。
2. 使用该键删除多余的行。

例如：

create sequence seq1;
alter table customer_info add column k1 int;
update customer_info set k1 = nextval('seq1');
delete from customer_info where k1 in (
select k1 
from (
select
k1,
row_number() over(partition by id, first_name, last_name, phone_number) as rn
from customer_info
) x
where rn > 1
) 

现在您只有两行。