Postgresql删除重复数据

环境

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 5
cqdb=> select * from test_tmp;
 id |  name
----+---------
  4 | jerry
  3 | lily
  5 | stephen
  1 | henry
  2 | susan

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

cqdb=> drop table test;
DROP TABLE
cqdb=> alter table test_tmp rename to test;
ALTER TABLE
cqdb=> 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 2
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