Re: Insert problem

At 09:20 AM 3/10/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Mon, 10 Mar 2008 00:14:12 +0000
>From: "Jamie Tufnell" <diesql(at)googlemail(dot)com>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: Re: Insert problem
>Message-ID:
><b0a4f3350803091714n2b89425ev48410eb86451b973(at)mail(dot)gmail(dot)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