PostgreSQL删除重复行求助：CTE报错及去重方法验证

一、先解决你之前CTE报错的问题 #

你遇到的“relation cte does not exist”错误，大概率是CTE的语法写法有误。PostgreSQL中用CTE执行删除操作时，需要通过USING子句关联CTE，而非直接在WHERE子句中引用。下面是用CTE结合窗口函数的正确去重写法：

WITH duplicate_markers AS (
  SELECT 
    uniqueid,
    -- 按重复判定字段分组，此处用propertyaddress，可根据实际规则添加更多字段
    ROW_NUMBER() OVER (PARTITION BY propertyaddress ORDER BY uniqueid DESC) AS row_num
  FROM nashvillehousing1
)
DELETE FROM nashvillehousing1
USING duplicate_markers
WHERE nashvillehousing1.uniqueid = duplicate_markers.uniqueid
AND duplicate_markers.row_num > 1; -- 删除标记为重复的行（row_num>1即为重复项）

二、你的现有去重代码的正确性分析 #

你用EXCEPT结合DELETE的写法，核心逻辑是保留每个propertyaddress分组中uniqueid最大的行，删除其余行。如果你的“重复行”定义就是「同一propertyaddress的行视为重复」，那这个逻辑是正确的，但存在几个局限：

• 重复规则单一：如果实际场景中需要多个字段（比如propertyaddress、saleprice、saledate）都相同才算重复，仅按propertyaddress分组会误删非重复行；
• 性能劣势：嵌套子查询+IN的写法，在数据量较大时执行效率远低于窗口函数+CTE的写法；
• 可读性差：相比窗口函数的写法，EXCEPT的逻辑不够直观，他人理解代码需要额外梳理。

三、优化建议 #

1. 明确重复行定义：先确认哪些字段完全相同时才算重复行，比如若需propertyaddress, ownername, saleprice三者都相同才算重复，窗口函数的PARTITION BY需修改为：

ROW_NUMBER() OVER (PARTITION BY propertyaddress, ownername, saleprice ORDER BY uniqueid DESC) AS row_num

2. 优先用窗口函数去重：窗口函数的写法不仅高效，还能清晰控制保留哪一行（比如ORDER BY uniqueid DESC保留最大uniqueid的行，改为ASC则保留最小的）；
3. 删除前先验证：执行DELETE前，先运行CTE内的查询，确认标记为row_num>1的行确实是需要删除的重复行，避免误删：

WITH duplicate_markers AS (
  SELECT 
    uniqueid, propertyaddress,
    ROW_NUMBER() OVER (PARTITION BY propertyaddress ORDER BY uniqueid DESC) AS row_num
  FROM nashvillehousing1
)
SELECT * FROM duplicate_markers WHERE row_num > 1;

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