postgres删除重复数据

环境

postgresql-14 订正数据，删除重复数据常用sql

需求

1. 删除id之外其他数据都一致的相同记录，只保留一条记录

DELETE FROM your_table
WHERE id NOT IN (
  SELECT MIN(id)
  FROM your_table
  GROUP BY column1, column2, column3
);

2. 删除所有数据都相同，没主键的数据，只保留一条记录

DELETE FROM your_table t1
WHERE EXISTS (
  SELECT 1
  FROM your_table t2
  WHERE <condition>
  AND t1.id = t2.id
  AND t1.ctid > t2.ctid
);