Re: Cannot insert a duplicate key into unique index

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

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(at)panix(dot)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

<kynn(at)panix(dot)com> writes:
> 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

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

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

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

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

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

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

hi kj,

select nextval('shipments_ship_id_seq');
then add one to that, say it's now equal 1010 ...
SELECT setval('shipments_ship_id_seq', 1010);

source =

http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x14316%2ehtm#SETTINGASEQUENCEVALUE

but, sometimes there are deeper problems, as you alluded to.

On Thu, Feb 19, 2004 at 03:18:45PM +1100, Noel wrote:
> 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
>
> Hope that helps
> Cheers
> Noel
>
> kynn(at)panix(dot)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
--
joe speigle
www.sirfsup.com