Re: Serials jumping

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.

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(at)magproductions(dot)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(at)magproductions(dot)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