Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    28

    Unanswered: getting the just inserted value from a sequence

    hi,
    i am using postgres 7.3.2 .
    The problem is how can get the just inserted value in a serial field after executing an Insert query. ie i have to insert the same serial value in another table as foreign key .

    pls help...

    thanks in advance

    jinujose

  2. #2
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Cool

    select nextval('row')

    example :

    select nextval('public.depenses_id_dep_seq') from depenses;

    You can clearly see that the sequence name contains the name of the table in front and the seq at the end. At my case as VB developper (Yes , I know the shame) I'm insertting the serial by getting the max value of the id row to avoid jumpin. that's a little more efficient
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  3. #3
    Join Date
    May 2003
    Posts
    28
    thanks...karim2k .

    i got another method. ie insert the row and call the currval('seq_name') function. this returns the just inserted value in the sequence. this returns correctly even if another session changed the current value of sequence by calling another nextval(). i am also using VB with postgres.
    in vb this two queries can be run at the same time with an ADO command object as bellow.
    -----------------
    dim cmd as new ADODB.command
    dim rs as ADODB.Recordset
    dim lngkey as long

    cmd.commandtext="insert into sales(item,qty) values('test',10);
    select currval('sales_id_seq')"
    set rs=cmd.execute
    set rs=rs.nextrecordset
    lngkey=rs.fields(0).value

    ---------------

    lngkey will contain the just inserted sequence value..

    jinujose

  4. #4
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up Wow

    Cool, that's cool , Idon't know it
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •