KingbaseESV7逻辑还原重复数据导致报错

Q: 不能建立唯一约束，还原报错

A:

错误现象（还原时报错）：

19465 sys_restore: [归档 (db)] Error from TOC entry 45235; 2606 5272721 CONSTRAINT DEV_STATE_WARN_KEY ALARM

19466 sys_restore: [归档 (db)] could not execute query: ERROR:  could not create unique index

19467 DETAIL:  Table contains duplicated values.

19468     Command was: ALTER TABLE ONLY "DEV_STATE_WARN"

19469     ADD CONSTRAINT "DEV_STATE_WARN_KEY" PRIMARY KEY ("DEV_ID", "OCCUR_TIME", "STATUS");

解决方法：

HISDB=# select "DEV_ID", "OCCUR_TIME", "STATUS" from ALARM.DEV_STATE_WARN group by  "DEV_ID", "OCCUR_TIME", "STATUS" having count(*)>1;

 115686215730069528 | 2015-04-05 19:48:33.000000 |      2

 115686215730070265 | 2015-04-05 19:11:25.000000 |      2

 116530640525983835 | 2015-04-06 01:09:38.000000 |      3

 116530640525983835 | 2015-04-06 02:11:13.000000 |      2

 117093590479405559 | 2015-04-05 19:28:54.000000 |      3

HISDB=# select ctid from ALARM.DEV_STATE_WARN where DEV_ID='115686215730069528 ' and OCCUR_TIME='2015-04-05 19:48:33.000000' and status=2;

      CTID     

----------------

 (23345747,86)

 (23345790,106)

HISDB=# delete from ALARM.DEV_STATE_WARN where ctid=' (23345747,86)';

DELETE 1

注:ctid 是指记录位置，因为记录位置不唯一所以建唯一约束失败！！！

通过上述方法把每个重复的ctid删除到只保留一组（本例删除23345747，保留23345790）。本例共有5个ctid重复，重复上述操作5次将所有重复的ctid都删除到只保留一组；