sql - 在删除语句postgresql中使用partition by子句

Question

我正在尝试调试以下代码。它给我一个错误，说 ERROR: syntax error at or near "(" 。

我的目标是删除表中的重复记录

 delete FROM (SELECT *,
                         ROW_NUMBER() OVER (partition BY snapshot,col1,col2,col3,col4,col5) AS rnum
                 FROM table where snapshot='2019-08-31')  as t
          WHERE t.rnum > 1;

标签： sqlpostgresqlsql-delete

Answer 1

尝试如下

DELETE FROM table a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   table b
                 WHERE  a.snapshot = b.snapshot
                          and a.col1=b.col1 and a.col2=b.col2);