Re: Insert problem

At 09:20 AM 3/10/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Mon, 10 Mar 2008 00:14:12 +0000
>From: "Jamie Tufnell" <diesql@googlemail.com>
>To: pgsql-sql@postgresql.org
>Subject: Re: Insert problem
>Message-ID: 
><b0a4f3350803091714n2b89425ev48410eb86451b973@mail.gmail.com>
>[snip]
> > table defination
> >
> > create sequence schItem_item_seq
> > create table schItem
> > (scid int NOT NULL references schedule ON DELETE CASCADE,
> > item int NOT NULL default nextval('schItem_item_seq'),
>
>[snip]
>
>It looks like there's already a row where scid=2072 and
>item=nextval('schItem_item_seq').
>
>Try:
>
>SELECT setval('schItem_item_seq', (SELECT max(item)+1 FROM schItem));
>
>And then run your query again.
>
>Cheers,
>J.

A friendly amendment to Jamie's (correct) advice. Be sure that no other 
tools are obtaining id's from the sequence or inserting rows into 
schItem when you run this.. (Safest way is to have the db offline when 
running this). I got some good info from the experts here about this 
while back and wrote up a little blog article detailing the issue (in 
short it's a big pain to do it while the db is online, with no obvious 
sure-fire solution):

http://www.misuse.org/science/2007/08/07/obtaining-a-block-of-ids-from-a-sequence-in-postgresql/

I hope this helps a little!

Steve