Postgres重復數據的更新一例 · postgresql手冊

事有一個需求，要求對一張小表的重復數據進行更新，數據量大概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一下，搞定.