从表中删除没有唯一键的重复行

Question

如何删除Postgres 9表中的重复行，行在每个字段上都是完全重复的，并且没有单个字段可用作唯一键，所以我不能只GROUP BY使用列并使用NOTIN语句。

我正在寻找一个SQL语句，而不是需要我创建临时表并将记录插入其中的解决方案。我知道该怎么做，但是需要更多工作来适应我的自动化流程。

表定义：

jthinksearch=> \d releases_labels;
Unlogged table "discogs.releases_labels"
   Column   |  Type   | Modifiers
------------+---------+-----------
 label      | text    |
 release_id | integer |
 catno      | text    |
Indexes:
    "releases_labels_catno_idx" btree (catno)
    "releases_labels_name_idx" btree (label)
Foreign-key constraints:
    "foreign_did" FOREIGN KEY (release_id) REFERENCES release(id)

样本数据：

jthinksearch=> select * from releases_labels  where release_id=6155;
    label     | release_id |   catno
--------------+------------+------------
 Warp Records |       6155 | WAP 39 CDR
 Warp Records |       6155 | WAP 39 CDR

Answer 1

如果您有能力重写整个表，则这可能是最简单的方法：

WITH Deleted AS (
  DELETE FROM discogs.releases_labels
  RETURNING *
)
INSERT INTO discogs.releases_labels
SELECT DISTINCT * FROM Deleted

如果您需要专门针对重复的记录，则可以使用内部ctid字段，该字段唯一地标识一行：

DELETE FROM discogs.releases_labels
WHERE ctid NOT IN (
  SELECT MIN(ctid)
  FROM discogs.releases_labels
  GROUP BY label, release_id, catno
)

要非常小心ctid; 它会随着时间而变化。但是您可以依靠它在单个语句的范围内保持不变。