在PostgreSQL中使用多列删除重复行

Question

我有一个名为 "votes" 的表格，其中包含以下列: voter, election_year, election_type, party。我需要删除所有重复的行，这些行是由 voter 和 election_year 组合而成的，但我不知道该如何操作。

我运行了以下命令:

WITH CTE AS(
SELECT voter, 
       election_year,
       ROW_NUMBER()OVER(PARTITION BY voter, election_year ORDER BY voter) as RN

FROM votes
)
DELETE
FROM CTE where RN>1

基于另一个StackOverflow答案，但似乎这是特定于SQL Server的。我已经看到过使用唯一ID的方法，但是这个特定表格没有这种奢侈。我怎样可以采用上面的脚本来去除我需要的重复项？谢谢！

编辑：根据请求，创建了一个带有一些示例数据的表：

CREATE TABLE public.votes
(
    voter varchar(10),
    election_year smallint,
    election_type varchar(2),
    party varchar(3)
);

INSERT INTO votes
    (voter, election_year, election_type, party)
VALUES
    ('2435871347', 2018, 'PO', 'EV'),
    ('2435871347', 2018, 'RU', 'EV'),
    ('2435871347', 2018, 'GE', 'EV'),
    ('2435871347', 2016, 'PO', 'EV'),
    ('2435871347', 2016, 'GE', 'EV'),
    ('10215121/8', 2016, 'GE', 'ED')
;

- JGrindal

---

根据另一个StackOverflow答案，但似乎这只适用于SQL Server。在我看来，这个查询语句完全符合PostgreSQL语法。PostgreSQL也很好地支持WITH .. AS（公共表达式）和ROW_NUMBER() OVER (....)。 "如何采用上述脚本以删除我需要的重复项？谢谢！"很难在没有表结构和示例数据的情况下进行判断。请查看https://stackoverflow.com/help/how-to-ask部分“**帮助其他人重现问题**”。 - Raymond Nijland

抱歉，出现了错误："[42P01] ERROR: relation "cte" does not exist Position: 157"。 - JGrindal

Answer 1

这里有一个选项

DELETE FROM votes T1
    USING   votes T2
WHERE   T1.ctid < T2.ctid 
    AND T1.voter = T2.voter 
    AND T1.election_year  = T2.election_year;

请查看http://sqlfiddle.com/#!15/4d45d/5

- mankowitz

Answer 2

在Postgres中，从公共表达式（CTE）中删除或更新数据是无效的。请参见"PostgreSQL with-delete “relation does not exists”"的被接受的答案。

由于您没有主键，因此可以（滥用）使用ctid伪列来标识要删除的行。

WITH
cte
AS
(
SELECT ctid,
       row_number() OVER (PARTITION BY voter,
                                       election_year
                          ORDER BY voter) rn
       FROM votes
)
DELETE FROM votes
       USING cte
       WHERE cte.rn > 1
             AND cte.ctid = votes.ctid;

db<>fiddle

也许考虑引入主键。

- sticky bit

---

我尝试了这个，但是出现了[42703]错误：列cte.ctid不存在。 - JGrindal

@JGrindal：你确定完全复制了语句吗？还是只编辑了自己的语句？如果是后者，在CTE中你也添加了 ctid 到 SELECT 中了吗？ - sticky bit

嗯，我在我的CTE中忘记了ctid。谢谢！ - JGrindal

dbfiddle再加个加号:D - Chalist

Answer 3

ctid字段是每个PostgreSQL表中存在的字段，对于表中的每条记录都是唯一的，并表示元组的位置。您做得几乎正确，只需要ctid作为您没有每行唯一标识符。

;WITH CTE AS(
SELECT ctid,voter, 
       election_year,
       ROW_NUMBER()OVER(PARTITION BY voter, election_year ORDER BY voter) as RN

FROM votes
)
delete  FROM votes v where v.ctid in (select CTE.ctid from  CTE where CTE.RN>1)

http://sqlfiddle.com/#!17/4d45d/14

- Zaynul Abadin Tuhin