Skip site navigation (1) Skip section navigation (2)

Re: Insert problem

From: Steve Midgley <public(at)misuse(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Insert problem
Date: 2008-03-10 18:09:04
Message-ID: 20080310180919.EEA662E2DF5@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
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


pgsql-sql by date

Next:From: Steve MidgleyDate: 2008-03-10 22:03:57
Subject: Re: works but ...
Previous:From: Jamie TufnellDate: 2008-03-10 00:14:12
Subject: Re: Insert problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group