使用SQL窗口函数删除重复行报错求助（PostgreSQL环境）

问题场景 #

需要基于emp_name列删除employee表中的重复行，表结构及数据如下：

create table employee (emp_id integer, emp_name varchar(50), dept_id integer);
insert into employee values (1,'bob william',1);
insert into employee values (2,'scott tiger',1);
insert into employee values (3,'amit gupta',1);
insert into employee values (4,'vijay sinha',1);
insert into employee values (5,'miller jones',3);
insert into employee values (6,'adam clark',3);
insert into employee values (7,'smith stuart',3);
insert into employee values (8,'james ward',4);
insert into employee values (9,'adam black',4);
insert into employee values (7,'smith stuart',3);
insert into employee values (8,'james ward',4);
insert into employee values (9,'adam black',4);

尝试以下SQL语句时出现报错：

WITH CTE_Table AS 
(SELECT *, 
ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name) AS RN
FROM employee
)
DELETE FROM CTE_Table
WHERE RN > 1

报错信息：

ERROR: relation "cte_table" does not exist
LINE 6: DELETE FROM CTE_Table

报错原因 #

PostgreSQL中，普通SELECT生成的CTE是临时结果集，并非可直接执行删除操作的实体表，因此无法直接从CTE执行DELETE语句。

正确解决方案 #

需要通过CTE定位重复行，再关联原表执行删除操作。以下提供两种可行写法：

方法1：通过ctid关联删除 #

ctid是PostgreSQL中用于标识物理行的内置字段，可唯一定位表中的每一行，适合无主键或主键重复的场景：

WITH CTE_Table AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name) AS RN
    FROM employee
)
DELETE FROM employee
WHERE ctid IN (SELECT ctid FROM CTE_Table WHERE RN > 1);

方法2：使用DELETE ... USING语法 #

通过USING子句关联CTE与原表，匹配重复行后删除：

WITH CTE_Table AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY emp_name) AS RN
    FROM employee
)
DELETE FROM employee e
USING CTE_Table c
WHERE e.ctid = c.ctid AND c.RN > 1;

补充说明 #

如果表已设置主键（比如emp_id为主键，需先清理重复的主键数据），可将上述语句中的ctid替换为主键字段，逻辑更清晰。

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