Could not create unique index, table contains duplicated values - Mailing list pgsql-admin

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