PostgreSQL 使用citd删除重复行

1. ctid的简单介绍：

　　ctid是PostgreSQL表中的系统字段，表示数据行在它所在表内的物理位置。ctid的字段类型是oid。但是VACUUM FULL操作之后，经过回收数据块内的空闲空间，数据行在块内的物理位置会发生移动，即ctid会发生变化。

2. 使用ctid删除重复行

数据

postgres=# select ctid,* from tb20;
  ctid  | id 
--------+----
 (,)  |  1
 (,)  |  1
 (,)  |  1
 (,)  |  1
 (,)  |  2
 (,)  |  2
 (,)  |  2
 (,)  |  3
 (,)  |  3
 (,) |  4
( rows
           

• 简单方式

postgres=# delete from tb20 a where ctid<>(select min(ctid) from tb20 b where a.id=b.id);
DELETE 
postgres=# select ctid,* from tb20;
  ctid  | id 
--------+----
 (0,1)  |  1
 (0,5)  |  2
 (0,8)  |  3
 (0,10) |  4
(4 rows)
           

• 稍复杂的方式

postgres=# select ctid,row_number() over(partition by id) from tb20 ;
  ctid  | row_number 
--------+------------
 (0,1)  |          1
 (0,2)  |          2
 (0,3)  |          3
 (0,4)  |          4
 (0,5)  |          1
 (0,6)  |          2
 (0,7)  |          3
 (0,8)  |          1
 (0,9)  |          2
 (0,10) |          1
(10 rows)

postgres=# select * from(select ctid,row_number() over(partition by id) from tb20)sn where sn.row_number> ;
 ctid  | row_number 
-------+------------
 (0,2) |          2
 (0,3) |          3
 (0,4) |          4
 (0,6) |          2
 (0,7) |          3
 (0,9) |          2
(6 rows)

postgres=# delete from tb20 where ctid =any(array((select ctid from(select ctid,row_number() over(partition by id) from tb20)sn where sn.row_number>))) ;
DELETE 
postgres=# select ctid,row_number() over(partition by id) from tb20 ;
  ctid  | row_number 
--------+------------
 (0,1)  |          1
 (0,5)  |          1
 (0,8)  |          1
 (0,10) |          1
(4 rows)
           

　　第一种方法在表tb20记录比较多的情况下，效率比较差，第二种方法更高效。