删除PostgreSQL中的重复记录

DELETE FROM my_table
WHERE id NOT IN (
    SELECT DISTINCT ON (data) id
    FROM my_table
    ORDER BY data, id
);

WITH cte AS (
    SELECT DISTINCT ON (data) id, data
    FROM my_table
    ORDER BY data, id
)
DELETE FROM my_table
WHERE id NOT IN (SELECT id FROM cte);

CREATE TABLE new_table (LIKE my_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

INSERT INTO new_table
SELECT DISTINCT ON (data) *
FROM my_table
ORDER BY data, id;

DROP TABLE my_table;
ALTER TABLE new_table RENAME TO my_table;