沒有 Primary Key 或 Unique Key 的 PGSQL 表格，怎麼去除重複的資料

有時候可能某些表格沒有建立 Primary Key 或 Unique Key（真糟糕～），這時候可能有完全重複的資料，這時候要怎麼抓出來？或是只保留一筆？



Postgres 有一格隱藏欄位 ctid，紀錄 MVCC 的版本資訊，我們可以利用這個資訊，挑出重複資料

這裡只選出最舊的保留下來

DELETE FROM test 
WHERE ctid NOT IN ( 
SELECT min(ctid) 
FROM test 
GROUP BY x); 
--(where 'x' is the unique column list)

雖然 ctid 欄位是標記每一「版本」資料位置的系統欄位，但是這個值會因為 VACUUM FULL 動作把表格重寫的關係而異動，所以切記不要拿來當成 Row Identifier。

另外，PGSQL 可以對個別表格啟用 oid 系統欄位，幫每一筆資料標上系統物件值，但是 oid 只有 232 次方個值，因此有機會發生計數重投開始的問題，有使用需求的話要注意這個潛在限制。

最後，在 Postgres 的延伸專案 Greenplum，這個 SQL 需要針對它的分散式架構微調，多查一兩個系統表才行：以下從參考資料借過來作個紀錄（還沒測試過）～

DELETE FROM test USING 
(select gp_segment_id, ctid from 
(select gp_segment_id, ctid, rank() over (partition by x order by gp_segment_id, ctid) as rk from test ) foo 
WHERE rk <> 1) rows_to_delete 
WHERE test.gp_segment_id=rows_to_delete.gp_segment_id 
AND test.ctid=rows_to_delete.ctid;

參考資料

What is the most efficient way of deleting duplicate records from a table? – All Help & Support | Pivotal Knowledge Base

mysql - The most efficient way to delete all duplicate rows from table? - Stack Overflow

sql - How to delete duplicate entries? - Stack Overflow

Removing duplicate rows in PostgreSQL

PostgreSQL: Documentation: 9.6: System Columns

Removing duplicates in a PostgreSQL array – The Falconry – Medium

找出並移除重複資料列 @ 瑞奇哥's技術扎記 :: 痞客邦 PIXNET ::

postgresql之ctid的浅谈 - li0924 - 博客园