Could not create unique index, table contains duplicated values

From: Martin Schievink <schievink(at)akyla(dot)nl>
To: pgsql-admin(at)postgresql(dot)org
Subject: Could not create unique index, table contains duplicated values
Date: 2005-11-08 09:07:02
Message-ID: 4CA3BE1415A8974CA9E265266825EAEC08B607@DARTHVADER.nar.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Andrew Watters 2005-11-08 11:35:15 Open connections details
Previous Message Joost Kraaijeveld 2005-11-08 08:56:59 Re: Moving pg_xlog problem