Re: Duplicate Unique Key constraint error

my primary key is neither SERIAL nor a SEQUENCE.

CONSTRAINT pk_dig PRIMARY KEY (dig_id)

This is the clause that I have for my primary key in the create table
script.

thanks,
~Harpreet

On 7/10/07, Ron St-Pierre <ron(dot)pgsql(at)shaw(dot)ca> wrote:
>
> Harpreet Dhaliwal wrote:
> > Hi,
> >
> > I keep getting this duplicate unique key constraint error for my
> > primary key even
> > though I'm not inserting anything duplicate. It even inserts the
> > records properly
> > but my console throws this error that I'm sure of what it is all about.
> >
> > Corruption of my Primary Key can be one of the possibilities but I'm
> > really not sure how
> > to get rid of this corruption and how to re-index the primary key.
> >
> > Also, I was wondering what could be the cause of this PK corruption,
> > if possible and what does can this corruption lead to.
> > I mean what are its cons.
> >
> > Thanks,
> > ~Harpreet
> You haven't really given any useful information about your primary key,
> but if you are using SERIAL as the column type (INT type with a
> sequence) you may just be having a problem with its current value (but
> then inserts shouldn't work).
>
> If you are using a sequence here, see what it's current value is and
> compare it to the highest value in the column. If its value is less than
> the columns max() value, just reset the value in the sequence.
>
> imp=# CREATE TABLE dup_pkey (id SERIAL PRIMARY KEY, insert_order int);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (1);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (2);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (3);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (4);
>
> imp=# SELECT * FROM dup_pkey;
> id | insert_order
> ----+--------------
> 1 | 1
> 2 | 2
> 3 | 3
> 4 | 4
> (4 rows)
>
> Now, if you set the value below what the max() column value is, you will
> have a problem with inserts.
> imp=# SELECT setval('dup_pkey_id_seq',3);
> setval
> --------
> 3
> (1 row)
>
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (5);
> ERROR: duplicate key violates unique constraint "dup_pkey_pkey"
>
>
> If this is the case, use setval() to update the value of the sequence to
> the max() value of your primary key. You can use \d to get information
> about your table, including the sequence name. However if, as you say,
> it IS inserting records properly, then this ISN'T going to help.
>
> hth
>
> Ron
>