Description
During an analysis, the following error seen in the synchronization log :
SQL Error: ERROR: could not create unique index "idx_cat".
SQL Error: DETAIL: Key (idcat)=(139512) is duplicated. (Severity 1, Msg No 1).
Observed in CAST AIP
Release | Yes/No |
---|---|
8.3.x | |
8.2.x | |
8.1.x | |
8.0.x | |
7.3.x | |
7.2.x | |
7.0.x |
Observed on RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS3 | |
CSS2 | |
CSS1 |
Step by Step Scenario
- Package the source code
- Run the analysis, analysis stops with errors in the log.
Action Plan
This issue occurs due to duplicates in the following tables in the local database:
- cat
- catattr
- catcat
We need to remove the duplicates from the above tables.
- For this First create a temporary table
- Insert distinct values from the table to this temporary table.
- Truncate the table and insert the values from the temporary table to this table,
- Drop the temporary table.
- Then run the analysis.
Below are the queries to remove the duplicate values from the tables.
CREATE TABLE tmp_cat ( idcat integer NOT NULL, catnam character varying(255) NOT NULL, catdsc character varying(255) NOT NULL, status character(10) NOT NULL ) WITH ( OIDS=FALSE ); ALTER TABLE tmp_cat OWNER TO operator; Insert into tmp_cat select distinct * from cat; truncate table cat; insert into cat select * from tmp_cat; drop table tmp_cat; create unique index idx_cat on cat using btree (idcat); CREATE TABLE tmp_catattr ( idcat integer NOT NULL, attrnam character varying(255), attrtyp integer NOT NULL, intval integer, strval character varying(255), status character(10) NOT NULL ) WITH ( OIDS=FALSE ); ALTER TABLE tmp_catattr OWNER TO operator; insert into tmp_catattr select a.* from catattr a, catattr b where a.idcat = b.idcat and a.attrnam = b.attrnam and a.ctid < b.ctid; truncate table catattr; insert into catattr select * from tmp_catattr; drop table tmp_catattr; CREATE TABLE tmp_catcat ( idcat integer NOT NULL, idcatparent integer NOT NULL, status character(10) NOT NULL ) WITH ( OIDS=FALSE ); ALTER TABLE tmp_catcat OWNER TO operator; insert into tmp_catcat select a.* from catcat a, catcat b where a.idcat = b.idcat and a.idcatparent = b.idcatparent and a.ctid < b.ctid; truncate table catcat; insert into catcat select * from tmp_catcat; drop table tmp_catcat;
Impact on Analysis Results and Dashboard
Notes/comments
Ticket # 4021
Related Pages