Re: Cannot insert a duplicate key into unique index

Hi KJ,

For each SERIAL primary key, there is a sequence table for that table,
which is where the primary key is generated. You need to check to see
that the current counter in the sequence table is passed the MAX primary
key in your table.
eg:
TABLE A
id (pkey)

Will have a sequence table
A_id_seq

For further information look at:
http://www.au.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL
and
http://www.postgresql.org/docs/7.4/static/functions-sequence.html

Hope that helps
Cheers
Noel

kynn@panix.com wrote:

>I'm trying to debug some problem in my database that is resulting in
>an error of the form "Cannot insert a duplicate key into unique
>index".  The insert statement that is producing this error does not
>include a value for the pkey field in question (this field is of type
>SERIAL).  I imagine that somehow the counter associated with this
>field got messed up, so that it is mistakenly generating a value that
>has been used already.  How can I straighten it out?
>
>Thanks!
>
>kj
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>
>

--
Noel Faux
Department of Biochemistry and Molecular Biology
Monash University
Clayton 3168
Victoria
Australia