PostgreSQL使用CTE删除重复行时报关系错误无法删除怎么办

PostgreSQL 中的普通 CTE（通用表表达式）属于只读临时结果集，本质是临时视图而非物理存储的关系表，因此不支持直接对 CTE 执行 DELETE、UPDATE 等写操作。你当前的语法兼容 SQL Server 等支持可更新 CTE 的数据库，但 PostgreSQL 不支持该写法，执行 DELETE 时会因为无法将 CTE 识别为可写的物理关系抛出对应错误。

你可以通过将 CTE 中筛选出的重复行 ID 关联到原 housing 表执行删除，核心逻辑是通过唯一主键 unique_id 定位到需要删除的原表行，参考代码如下：

WITH RowNumCTE AS (
    SELECT unique_id, 
        ROW_NUMBER() OVER(
            PARTITION BY parcelid, property_address, saleprice, saledate, legal_ref
            ORDER BY unique_id
        ) AS row_num
    FROM housing
)
DELETE FROM housing
WHERE unique_id IN (
    SELECT unique_id FROM RowNumCTE WHERE row_num > 1
);

如果不想用 CTE 也可以用自关联写法实现相同去重逻辑：

DELETE FROM housing a
USING housing b
WHERE 
    a.parcelid = b.parcelid
    AND a.property_address = b.property_address
    AND a.saleprice = b.saleprice
    AND a.saledate = b.saledate
    AND a.legal_ref = b.legal_ref
    AND a.unique_id > b.unique_id;

注意：执行删除操作前建议先替换 DELETE 为 SELECT * 验证筛选出的待删除行符合预期，避免误删数据。

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