Re: Serials jumping - Mailing list pgsql-general

Matt A. wrote:
> I did not know that about every call to nextval, but
> that does make sense.
>
> I need a way to return the new ID of the SERIAL column
> on INSERT to add the related rows into the proper
> tables explictly. Similiar to how I could use
> @@identity to retrive the value in sql2000.

Once you insert into the table that has the serial you
can use currval('sequencename') to return the value of the
id just inserted.

The caveat is that you must do it in order e.g.,

insert
currval
insert
currval

But this will work as long as you are within the same transaction.

Sincerely,

Joshua D. Drake

>
> The only ways I saw was nextval or currval? Is there a
> best practice for returning the new identities value?
> I'd prefer it in one call if possible.



>
> Thank you.
>
>
> --- Alban Hertroys <alban@magproductions.nl> wrote:
>
>
>>Matt A. wrote:
>>
>>>I have a serial column on a test box DB. I'm
>>>using "select nextval('some_seq') as id" to insert
>>
>>a
>>
>>>id in sequence. It doesn't return the next value
>>
>>but
>>
>>>at least 4 or so (random) ahead of the current
>>>value. Is this to be expected?
>>
>>You probably did some extra selects on the sequence
>>somewhere, or the
>>sequence is set to increment by a number >1.
>>
>>
>>>Is this the most efficient way to retreive an id
>>
>>value
>>
>>>for insert a row then insert the resulting row
>>
>>into
>>
>>>the related tables?
>>
>>Why don't you just use the default value of the
>>serial type? You can do
>>that by leaving the column out of the insert query.
>>
>>
>>>Is there a more efficient/proper way? I would like
>>
>>all
>>
>>>my records to increment by 1 if possible. I'd be
>>
>>very
>>
>>>grateful to read your opinion.
>>
>>Sequences increment at every call of nextval.
>>Doesn't matter whether you
>>used that value for anything or not, it always
>>increments. Even if you
>>rollback the transaction or interupt the query.
>>
>>Sequences ensure that multiple concurrent inserts
>>never get the same
>>number back from them, so that you don't get "unique
>>constraint
>>violation"'s.
>>They're not designed to always increment by 1.
>>
>>Regards,
>>
>>--
>>Alban Hertroys
>>alban@magproductions.nl
>>
>>magproductions b.v.
>>
>>T: ++31(0)534346874
>>F: ++31(0)534346876
>>M:
>>I: www.magproductions.nl
>>A: Postbus 416
>>    7500 AK Enschede
>>
>>//Showing your Vision to the World//
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>
>
>
>
> ____________________________________________________
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/