sql - 如何使用 PostgreSQL 删除 2 列的重复行？

Question

表 DDL:

CREATE TABLE entities_patents (
    entity_id INTEGER REFERENCES entities(entity_id) NOT NULL,
    patent_id INTEGER REFERENCES patents (patent_id) NOT NULL,
    doc_number text NOT NULL,
    created_at timestamp with time zone DEFAULT current_timestamp,
    updated_at timestamp with time zone DEFAULT current_timestamp,
    deleted boolean,
    PRIMARY KEY (entity_id, patent_id)
);

不幸的是表中有重复项，我之前无法添加 pk 约束。现在尝试这样做会导致:

DETAIL:  Key (entity_id, patent_id)=(123123, 811231333) is duplicated.

有没有办法删除这些重复项？

Answer 1

你可以使用 ROW_NUMBER()，所以试试这个:(未经测试所以可能有一些语法问题)

DELETE FROM entities_patents t
WHERE EXISTS(SELECT * FROM (
                    SELECT p.entity_id,p.patent_id,p.created_at,p.updated_at,
                    ROW_NUMBER() OVER(PARTITION BY p.entity_id,p.patent_id ORDER BY p.created_at DESC,p.updated_at DESC) as rnk
                    FROM entities_patents p) s
              WHERE s.rnk > 1 and s.entity_id = t.entity_id and s.patent_id = t.patent_id
                  AND s.created_at = t.created_at and s.updated_at = t.updated_at)