PostgreSQL 使用citd删除重复行

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

2. 使用ctid删除重复行

数据

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

• 简单方式

postgres=# delete from tb20 a where ctid<>(select min(ctid) from tb20 b where a.id=b.id);DELETE 6postgres=# 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>1 ; 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>1))) ;DELETE 6postgres=# 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记录比较多的情况下，效率比较差，第二种方法更高效。