Обсуждение: Could not create unique index, table contains duplicated values
We’re having problems restoring a database, we dumped and tried to restore on the same databaseserver., and used the command:
pg_dump -Ft -b {dbname} > {filename}
to dump the database and
pg_restore -d {dbname} {filename}
to restore the database. At some point we’re getting these error messages:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3005; 16386 1728253 CONSTRAINT _afdeling_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was:
ALTER TABLE ONLY _afdeling_id
ADD CONSTRAINT _afdeling_id_pkey PRIMARY KEY (id);
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public._afdeling_id_pkey" does not exist
Command was: ALTER INDEX public._afdeling_id_pkey OWNER TO win;
pg_restore: [archiver (db)] Error from TOC entry 3107; 16386 1728349 CONSTRAINT _bezitting_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY _bezitting_id
ADD CONSTRAINT _bezitting_id_pkey PRIMARY KEY (id);
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public._bezitting_id_pkey" does not exist
Command was: ALTER INDEX public._bezitting_id_pkey OWNER TO win;
pg_restore: [archiver (db)] Error from TOC entry 3095; 16386 1728337 CONSTRAINT _bezittingtype_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY _bezittingtype_id
ADD CONSTRAINT _bezittingtype_id_pkey PRIMARY KEY (id);
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public._bezittingtype_id_pkey" does not exist
Command was: ALTER INDEX public._bezittingtype_id_pkey OWNER TO win;
pg_restore: [archiver (db)] Error from TOC entry 3101; 16386 1728343 CONSTRAINT _bezittingtypeoptie_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY _bezittingtypeoptie_id
ADD CONSTRAINT _bezittingtypeoptie_id_pkey PRIMARY KEY (id);
We focused on the first error first, we hoped by fixing it to be able to fix the rest to (there are lots more of these errors, I just showed the first few). We tried to look for duplicates with:
select id from _afdeling_id group by id having count(id) > 1;
This returned 0 rows. We tried reindexing and the reindexing worked fine, which shouldn’t work if there are duplicated values, right?. We searched for an answer and found something about the lc_ctype, we checked it and ours is on “C”. That seems to be right, or do we need to check the strcoll() lc_ctype?
Now we’re actually out of ideas how to solve this issue, any help solving this issue would be greatly appreciated.
Martin Schievink
We’re having problems restoring a database, we dumped and tried to restore on the same databaseserver., and used the command:
pg_dump -Ft -b {dbname} > {filename}
to dump the database and
pg_restore -d {dbname} {filename}
to restore the database. At some point we’re getting these error messages:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3005; 16386 1728253 CONSTRAINT _afdeling_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was:
ALTER TABLE ONLY _afdeling_id
ADD CONSTRAINT _afdeling_id_pkey PRIMARY KEY (id);
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public._afdeling_id_pkey" does not exist
Command was: ALTER INDEX public._afdeling_id_pkey OWNER TO win;
pg_restore: [archiver (db)] Error from TOC entry 3107; 16386 1728349 CONSTRAINT _bezitting_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY _bezitting_id
ADD CONSTRAINT _bezitting_id_pkey PRIMARY KEY (id);
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public._bezitting_id_pkey" does not exist
Command was: ALTER INDEX public._bezitting_id_pkey OWNER TO win;
pg_restore: [archiver (db)] Error from TOC entry 3095; 16386 1728337 CONSTRAINT _bezittingtype_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY _bezittingtype_id
ADD CONSTRAINT _bezittingtype_id_pkey PRIMARY KEY (id);
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public._bezittingtype_id_pkey" does not exist
Command was: ALTER INDEX public._bezittingtype_id_pkey OWNER TO win;
pg_restore: [archiver (db)] Error from TOC entry 3101; 16386 1728343 CONSTRAINT _bezittingtypeoptie_id_pkey win
pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY _bezittingtypeoptie_id
ADD CONSTRAINT _bezittingtypeoptie_id_pkey PRIMARY KEY (id);
We focused on the first error first, we hoped by fixing it to be able to fix the rest to (there are lots more of these errors, I just showed the first few). We tried to look for duplicates with:
select id from _afdeling_id group by id having count(id) > 1;
This returned 0 rows. We tried reindexing and the reindexing worked fine, which shouldn’t work if there are duplicated values, right?. We searched for an answer and found something about the lc_ctype, we checked it and ours is on “C”. That seems to be right, or do we need to check the strcoll() lc_ctype?
Now we’re actually out of ideas how to solve this issue, any help solving this issue would be greatly appreciated.