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

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

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)