如何使用psql中的row_number()从表中删除重复项

Question

我已经编写了以下查询，我正在使用greenplum db和dbeaver来实现。

with cte as 
(select 
*, 
row_number() over(partition by first_name order by roll_num) row_num 
from table_name 
where roll_num in ('0011')) 
delete from cte where row_num>1;

上述查询返回错误。有人能在这里帮我吗！

Answer 1

这个怎么样：

带有USING子句的PostgreSQL DELETE语句

参考：PostgreSQL Docs

Answer 2

请这样尝试，它对我有效：

select *  from tablename as t 
where exists 
( select * 
from tablename as d 
where d.ctid > t.ctid 
and d.* is not distinct from t.*
) ;
delete from tablename as t 
where exists 
( select * 
from tablename as d 
where d.ctid > t.ctid 
and d.* is not distinct from t.*
) ;