事有一个需求，要求对一张小表的重复数据进行更新，数据量大概10W。  **背景数据示例：** ~~~ [postgres@localhost ~]$ psql psql (9.2.3) Type "help" for help. postgres=# create table t_kenyon(id int,regguid text); CREATE TABLE postgres=# insert into t_kenyon values(1,'a'),(1,'a'); INSERT 0 2 postgres=# insert into t_kenyon values(2,'bb'),(2,'bb'),(2,'bb'); INSERT 0 3 postgres=# insert into t_kenyon values(3,'cc'),(3,'cc'),(3,'cc'),(4,'dd'),(5,'ee'); INSERT 0 5 postgres=# insert into t_kenyon values(1,'xx'); INSERT 0 1 postgres=# select * from t_kenyon order by id; id | regguid ----+--------- 1 | a 1 | a 1 | xx 2 | bb 2 | bb 2 | bb 3 | cc 3 | cc 3 | cc 4 | dd 5 | ee (11 rows) ~~~ **需求：**  要求对regguid有重复的数据和相同的ID，更新regguid，仅保留其中一条，其他置为0，如结果应类似 ~~~ 1 a 1 0 1 x 2 bb 2 0 2 0 ~~~ 可以用该表的主键字段来实现，没有主键字段可选择ctid来做。SQL如下： ~~~ postgres=# update t_kenyon a set regguid = '0' where ctid != (select min(ctid) from t_kenyon b where a.id=b.id group by id having count(1)>1); UPDATE 5 postgres=# select * from t_kenyon order by id; id | regguid ----+--------- 1 | a 1 | xx 1 | 0 2 | bb 2 | 0 2 | 0 3 | cc 3 | 0 3 | 0 4 | dd 5 | ee (11 rows) postgres=# vacuum  full  analyze t_kenyon; VACUUM ~~~ 大数据的更新最后vacuum一下，搞定.