Re: duplicate key errors when restoring 8.4.0 database dump into 9.1.2

On 11-12-30 10:49 AM, Culley Harrelson wrote:
> They are just your standard sql errors seen in the output of psql mydb
> < backup.sql
>
>
> ALTER TABLE
> ERROR:  could not create unique index "ht_user_pkey"
> DETAIL:  Key (user_id)=(653009) is duplicated.
>
> There is a unique index on user_id in the 8..4.0 system and, of
> course, only one record for 653009.
>
>

Are you sure there is just one record? I had this same problem and it
was because there were a few rows that violated the primary key even
though the constraint existed since table creation. The db had a hard
crash once which might explain the bad data though.
run this to check your whole table for duplicates:

select user_id, count(*)
from ht_user
group by user_id
having count(*) > 1;

-nigel.