Thread: 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@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
<kynn@panix.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@ugyvitelszolgaltato.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@postgresql.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@esc1.com
X-Original-To: kynn@panix.com Date: Thu, 19 Feb 2004 15:12:09 +0100 From: daq <daq@ugyvitelszolgaltato.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@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
--
joe speigle
www.sirfsup.com