Обсуждение: Could not create unique index, table contains duplicated values

Поиск
Список
Период
Сортировка

Could not create unique index, table contains duplicated values

От
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.

 

Martin Schievink

Re: Could not create unique index, table contains duplicated values

От
"Thomas F. O'Connell"
Дата:

On Nov 8, 2005, at 3:07 AM, Martin Schievink wrote:

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.


Can you post some more information relevant to the issue? The relevant schemas for the table and any related index defintions (including primary keys).

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)