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

Re: works but ...

From: Steve Midgley <public(at)misuse(dot)org>
To: "A(dot) R(dot) Van Hook" <hook(at)lota(dot)us>,Jamie Tufnell <diesql(at)googlemail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: works but ...
Date: 2008-03-10 22:03:57
Message-ID: 20080310220416.74B892E028E@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
At 02:49 PM 3/10/2008, A. R. Van Hook wrote:
>The following code seems to work but it leads to the following 
>question(s):
>Is there a sequence for each scid,item or is there one sequence that 
>must be reset
>when changing scid?
>
>    $cmd = "select setval('schItem_item_seq', (select max(item)+1 from 
> schItem where scid=$newScid))";
>    $result = $conn->exec($cmd);
>    $OK +=  cmp_eq($conn,PGRES_TUPLES_OK, $result->resultStatus);
>
>    $cmd = "insert into schItem (scid, value, iflag, outalts, sidate, 
> istid)
>            select $newScid,  i.value, i.iflag, i.outalts, i.sidate, 
> i.istid
>              from schItem i, schItem s
>            where (i.scid=$wrongScid and i.item >= $searchItem)
>              and (s.scid=$newScid)
>            group by i.value, i.iflag, i.outalts, i.sidate, i.istid, 
> i.scid";
>    $result = $conn->exec($cmd);
>    $OK +=  cmp_eq($conn,PGRES_COMMAND_OK, $result->resultStatus);
>
>    $cmd = "delete from schItem where scid = $wrongScid and item >= 
> $searchItem";
>
>ps
>    the full perl script is attached
>
>thanks again
>art
>
>
>--
>Arthur R. Van Hook

Hi Arthur,

I'm not totally clear on your question but generally speaking there is 
one sequence per primary key, by default on each table. So if you reset 
that key, then your table will start issuing keys at that new number. 
Another way to be more safe is to "+5" your sequence, so that even if a 
few inserts slip in, you're still ahead of the game..

Steve




pgsql-sql by date

Next:From: Dirk JagdmannDate: 2008-03-11 19:03:43
Subject: Re: Composite UNIQUE across two tables?
Previous:From: Steve MidgleyDate: 2008-03-10 18:09:04
Subject: Re: Insert problem

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