pg中删除表中重复记录的SQL

highgo=# select * from test;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
  3 | 
(4 rows)


highgo=# delete from test a where a.ctid <> (select min(b.ctid) from test b where a.id=b.id);
DELETE 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)


上面的语句在表test的记录比较多时，效率会比较差，这时可以使用下面更高效的语句删除重复的记录：
highgo=# insert into test values (3);
INSERT 0 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
  3 | 
(4 rows)


highgo=# delete from test where ctid = any(array(select ctid from (select row_number() over (partition by id),ctid from test) x where x.row_number > 1));
DELETE 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)