Re: Duplicate Unique Key constraint error - Mailing list pgsql-general

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.pgsql@shaw.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