PostgreSQL 使用citd删除重复行

前端之家收集整理的这篇文章主要介绍了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 6 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>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 6 postgres=# select ctid,10) |          1
(4 rows)

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

猜你在找的Postgre SQL相关文章