postgresql表如何去重

david=# create table emp (

david(# id int,

david(# name varchar);

CREATE TABLE

david=#

david=# insert into emp values (1, 'david');

INSERT 0 1

david=# insert into emp values (1, 'david');

INSERT 0 1

david=# insert into emp values (1, 'david');

INSERT 0 1

david=# insert into emp values (2, 'sandy');

INSERT 0 1

david=# insert into emp values (2, 'sandy');

INSERT 0 1

david=# insert into emp values (3, 'renee');

INSERT 0 1

david=# insert into emp values (4, 'jack'); 

INSERT 0 1

david=# insert into emp values (5, 'rose');

INSERT 0 1

david=#

david=# select ctid, * from emp;

 ctid  | id | name 

-------+----+-------

 (0,1) |  1 | david

 (0,2) |  1 | david

 (0,3) |  1 | david

 (0,4) |  2 | sandy

 (0,5) |  2 | sandy

 (0,6) |  3 | renee

 (0,7) |  4 | jack

 (0,8) |  5 | rose

(8 rows)

david=#

david=# select distinct id, count(*) from emp group by id having count(*) > 1;

 id | count

----+-------

|     3

|     2

(2 rows)

david=#

david=# select ctid, * from emp where ctid in (select min(ctid) from emp group by id);

 ctid  | id | name 

-------+----+-------

 (0,1) |  1 | david

 (0,4) |  2 | sandy

 (0,6) |  3 | renee

 (0,7) |  4 | jack

 (0,8) |  5 | rose

(5 rows)

david=#

david=# delete from emp where ctid not in (select min(ctid) from emp group by id);

DELETE 3

david=#

david=# select ctid, * from emp;

 ctid  | id | name 

-------+----+-------

 (0,1) |  1 | david

 (0,4) |  2 | sandy

 (0,6) |  3 | renee

 (0,7) |  4 | jack

 (0,8) |  5 | rose

(5 rows)

david=#