背景数据示例:
[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一下,搞定.