Postgres 删除复合重复项

Question

在我的填充数据库中，我有一个最初具有此架构的表：

CREATE TABLE tale_references (
id SERIAL PRIMARY KEY,
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
tale_id TEXT REFERENCES tale_catalog(tale_id) ON DELETE CASCADE,
reference_id INT REFERENCES FT_References(id) ON DELETE CASCADE,
tale_catalog_id INT REFERENCES tale_catalog(id) ON DELETE CASCADE,
other_info JSONB);

我需要添加以下行：

CONSTRAINT no_duplicate_tale UNIQUE (tale_catalog_id, reference_id))

但是，数据库中已有的数据具有违反此约束的条目。如何找到这些条目以便删除它们？

Answer 1

我会这样做：

delete from tale_references t
where exists (select 1
from tale_references t2
where t2.tale_catalog_id = t.tale_catalog_id and
t2.reference_id = t.reference_id and
t2.id < t.id
);

这将删除tale_catalog_id/reference_id对具有较小值的任何行。 换句话说，它保留每对的最小值。

Answer 2

非常简单的方法是使用EXISTS子句。这是我为您制定的查询：

DELETE
FROM tale_references
WHERE id IN
( SELECT t1.id
FROM tale_references t1
WHERE EXISTS
(SELECT 1
FROM tale_references t2
WHERE t1.id           <> t2.id
AND t1.tale_catalog_id = t2.tale_catalog_id
AND t1.reference_id    = t2.reference_id
)
);