Postgresql删除重复数据

来源：互联网发布：windows下的dig工具编辑：程序博客网时间：2024/06/07 22:15

环境

Postgresql 9.4.4

方法1：中间表

1 查看已有数据

cqdb=> select * from test order by id; id | name----+-------  1 | henry  2 | susan  2 | susan  3 | lily  4 | jerry  5 | stephen  5 | stephen

2 创建中间表并插入数据

cqdb=> create table test_tmp as select distinct id,name from test;SELECT 5cqdb=> select * from test_tmp; id |  name----+---------  4 | jerry  3 | lily  5 | stephen  1 | henry  2 | susan

3 删除原表并把中间表作为原始表

cqdb=> drop table test;DROP TABLEcqdb=> alter table test_tmp rename to test;ALTER TABLEcqdb=> select * from test order by id; id | name----+-------  1 | henry  2 | susan  3 | lily  4 | jerry  5 | stephen

方法2：行唯一标识ctid

1 查看ctid

cqdb=> select ctid,* from test ; ctid  | id | name-------+----+------- (0,8) |  1 | henry (0,9) |  2 | susan (0,10) |  3 | lily (0,11) |  4 | jerry (0,12) |  5 | stephen (0,13) |  5 | stephen (0,14) |  2 | susan

2 删除重复数据

cqdb=> delete from test as a where ctid > (select min(ctid) from test as b where a.id = b.id);DELETE 2cqdb=> select ctid,* from test ; ctid  | id | name-------+----+------- (0,8) |  1 | henry (0,9) |  2 | susan (0,10) |  3 | lily (0,11) |  4 | jerry (0,12) |  5 | stephen

0 0