Re: [GENERAL] please help me recover from duplicate key in unique index

> > Please help me recover our database from what I think
> > is a duplicate key in unique index problem.
>
> This may not help, and forgive my asking the obvious, but have
> you done a SELECT on the table and actually *seen* duplicate 'id'
> values in the SERIAL column? That would surprise me because I
> didn't think it was possible to create duplicates in the scenario
> you describe. The SERIAL type is really just a sequence, and its
> values are unique across all transactions (I hope!). IIRC there
> is some opportunity for wierdness if the sequence cache setting
> has been "adjusted" incorrectly
> (http://www.postgresql.org/docs/postgres/sql-createsequence.htm).
>
> > When I try to vacuum, I get this:
> >
> > ERROR: Cannot insert a duplicate key into a unique
> > index
> >

Try dropping all indexes on the table, do a vacuum (if it will let you).
Dump the table out with pg_dump -t <t_name> if it will let you, sort it
in emacs or with perl. Easiest thing would then be to write a little
perl script that puts all duplicate rows into a separate file. Dropt the
table and re-create it. Load the first lot up (with given sequence
number!), fix your sequences (drop, create ..start <max-seq>) and then
handle the duplicate rows (i.e. insert them with perl/DBI or something
so that they get new sequence numbers assigned).

I think you should be able to dump once you have dropped all indexes
(probably one of them is <tab-name>_pkey). Your sequence may be called
<tab-name>_<col-name>_seq if memeory serves me right. It is still a
normal sequences and you can drop and recreate it safely.

Good luck,

Adriaan