PostgreSQL中删除表内完全重复行并保留一行的方案咨询

先确认你的表结构和测试数据：

-- Create the table
CREATE TABLE colour (
col1 VARCHAR,
col2 VARCHAR,
col3 VARCHAR
);

-- Insert sample data
INSERT INTO colour (col1, col2, col3) VALUES
('red', 'blue', 'black'),
('grey', 'red', 'white'),
('pink', NULL, 'blue'),
('red', 'blue', 'black'),
('grey', 'red', 'white'),
('pink', NULL, 'blue');

针对删除完全重复行、仅保留每组一行的需求，提供两种可行方案：

方案一：使用窗口函数精准删除重复行 #

适合需要保留特定行（比如最早插入的，若有主键可调整排序规则）的场景，同时处理NULL值的比较问题：

WITH duplicate_rows AS (
    SELECT 
        col1, col2, col3,
        ROW_NUMBER() OVER (
            PARTITION BY col1, col2, col3 
            ORDER BY (SELECT NULL) -- 无需指定排序时用此，若有主键可替换为主键字段（如id ASC）
        ) AS rn
    FROM colour
)
DELETE FROM colour
USING duplicate_rows
WHERE 
    (colour.col1, colour.col2, colour.col3) IS NOT DISTINCT FROM (duplicate_rows.col1, duplicate_rows.col2, duplicate_rows.col3)
    AND duplicate_rows.rn > 1;

说明：IS NOT DISTINCT FROM会自动处理NULL值的相等判断，避免因NULL = NULL不成立导致的漏删。

方案二：用DISTINCT ON创建去重表替换原表 #

适合数据量不大的场景，操作更简洁：

-- 创建临时表存储去重后的数据
CREATE TABLE colour_temp AS
SELECT DISTINCT ON (col1, col2, col3) col1, col2, col3
FROM colour
ORDER BY col1, col2, col3; -- 排序字段需与DISTINCT ON的分组字段一致，确保保留固定行

-- 替换原表
DROP TABLE colour;
ALTER TABLE colour_temp RENAME TO colour;

注意：如果原表有主键、索引或约束，需要在新表上重新创建。

关于你之前CTE失败的可能原因 #

大概率是没处理NULL值的比较逻辑，或者窗口函数的PARTITION BY未正确覆盖所有字段，导致重复行未被正确分组。使用上述方案中的IS NOT DISTINCT FROM可以避免NULL带来的判断问题。

内容的提问来源于stack exchange，提问作者Mattudi