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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx
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/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
