Cannot insert a duplicate key into unique index

#1

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

#2

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....ATATYPE-SERIAL
and
http://www.postgresql.org/docs/7.4/s...-sequence.html

Hope that helps
Cheers
Noel

kynn (AT) panix (DOT) com wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#3

<kynn (AT) panix (DOT) com> writes:

Quote:

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?

You need to do something like

select setval('seq-name', (select max(col) + 1 from table));

regards, tom lane

---------------------------(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

#4

Quote:

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?

TL> You need to do something like

TL> select setval('seq-name', (select max(col) + 1 from table));

TL> regards, tom lane

The setval function updates the last_value field of the sequence table. You don't need the "+ 1".

select setval('seq-name', (select max(col) from table));

DAQ

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#5

Date: Thu, 19 Feb 2004 08:01:04 +0100
From: daq <daq (AT) ugyvitelszolgaltato (DOT) hu>

The setval function updates the last_value field of the sequence table. You don't need the "+ 1".

select setval('seq-name', (select max(col) from table));

Thank you all for the help. I'm almost there.

How can I list all the sequences in the database, with their
attributes (such as last_value)? (I'm having a hard time guessing
'seq-name'; the 'A_id_seq' formula did not work.)

For that matter (going beyond my original question) does PostgreSQL
have anything like a comprehensive "catalog" function that will list
all the tables, their fields, etc. defined in the database?

Thanks!

kj

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#6

kpc> Thank you all for the help. I'm almost there.

kpc> How can I list all the sequences in the database, with their
kpc> attributes (such as last_value)? (I'm having a hard time guessing
kpc> 'seq-name'; the 'A_id_seq' formula did not work.)

kpc> For that matter (going beyond my original question) does PostgreSQL
kpc> have anything like a comprehensive "catalog" function that will list
kpc> all the tables, their fields, etc. defined in the database?

You can list all the sequences:

select relname from pg_class where relkind='S';

and list all their atributes:

select * from sequence_name;

See "System Catalogs" in the documentation!

DAQ

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#7

It's amazing how much good information comes accross this list. Thanks for the
knowledge.

On Thursday 19 February 2004 09:12 am, daq saith:

Quote:

You can list all the sequences:

select relname from pg_class where relkind='S';

and list all their atributes:

select * from sequence_name;

See "System Catalogs" in the documentation!

DAQ

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

--
Quote: 3
"There is a rank due to the United States, among nations, which will be
withheld, if not absolutely lost, by the reputation of weakness. If we
desire to avoid insult, we must be able to repel it; if we desire to
secure peace, one of the most powerful instruments of our rising prosperity,
it must be known that we are at all times ready for war."

--George Washington

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#8

X-Original-To: kynn (AT) panix (DOT) com
Date: Thu, 19 Feb 2004 15:12:09 +0100
From: daq <daq (AT) ugyvitelszolgaltato (DOT) hu>

kpc> Thank you all for the help. I'm almost there.

kpc> How can I list all the sequences in the database, with their
kpc> attributes (such as last_value)? (I'm having a hard time guessing
kpc> 'seq-name'; the 'A_id_seq' formula did not work.)

kpc> For that matter (going beyond my original question) does PostgreSQL
kpc> have anything like a comprehensive "catalog" function that will list
kpc> all the tables, their fields, etc. defined in the database?

You can list all the sequences:

select relname from pg_class where relkind='S';

and list all their atributes:

select * from sequence_name;

See "System Catalogs" in the documentation!

Way cool. Thanks.

BTW, I was wrong when I said that the 'A_id_seq' formula did not work
(it turns out that it failed due to a typo of mine).

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

#9

Quote:

kpc> How can I list all the sequences in the database, with their
kpc> attributes (such as last_value)? (I'm having a hard time guessing
kpc> 'seq-name'; the 'A_id_seq' formula did not work.)

You can list all the sequences:

select relname from pg_class where relkind='S';

and list all their atributes:

select * from sequence_name;

select last_value from something_seq

And I got the sequence name from \ds at the psql prompt! but that's only for the current database.

joe
--
speigle
www.sirfsup.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#10

Dear Adelaide ,

Quote:

Warning: PostgreSQL query failed: ERROR: Cannot insert a duplicate key
into unique index fee_uk

Check the max value in data base for the same index key and then check if

the max value agree with the sequence .
do some thing like
<code>
select max(fees.fee_id);
then
select nextval('general_seq'::text);
</code>
on psql command prompt
Now if the max value of fees.fee_id
is greater than what nextval returns
then this sequence has to be fixed

Have you recently done an dump restore ?

--
Best Regards,
Vishal Kashyap
Director / Lead Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com
Jabber IM: vishalkashyap (AT) jabber (DOT) org
ICQ : 264360076
-----------------------------------------------
You yourself, as much as anybody in the entire
universe, deserve your love and affection.
- Buddha
---------------
I am usually called by the name Vishal Kashyap
and my Girlfriend calls me Vishal CASH UP.
This is because everyone loves me as Vishal Kashyap
and my Girlfriend loves me as CASH.
___
//\\\
( 0_0 )
----------------o0o-----o0o---------------------

---------------------------(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