FAQ
How can I get Last ID inserted ???
The problem is for a multiuser(symultans) database. Exist a statment SQL
witch get the last id inserted for a session ?

Thanks !

Search Discussions

  • Brett W. McCoy at Mar 2, 2001 at 4:31 pm

    On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:

    How can I get Last ID inserted ???
    The problem is for a multiuser(symultans) database. Exist a statment SQL
    witch get the last id inserted for a session ?
    currval('<seq name>') is the way to get the value that was last inserted
    into the database. However, there is no guarantee, in a multiuser
    environment, that the value you got was the value you actually used. You
    can also user last_value in an SQL statement, but you still have the
    problem of having multiple backends generating sequence values.

    A more reliable way is to explicitly call nextval('<seq name>') and use
    the value returned to insert into the database.

    Please see the CREATE SEQUENCE documentation at
    http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createsequence.htm

    -- Brett
    http://www.chapelperilous.net/~bmccoy/
    ---------------------------------------------------------------------------
    While my BRAINPAN is being refused service in BURGER KING, Jesuit
    priests are DATING CAREER DIPLOMATS!!
  • Catalin CIOCOIU at Mar 2, 2001 at 5:20 pm

    "Brett W. McCoy" wrote:
    On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:

    How can I get Last ID inserted ???
    The problem is for a multiuser(symultans) database. Exist a statment SQL
    witch get the last id inserted for a session ?
    currval('<seq name>') is the way to get the value that was last inserted
    into the database. However, there is no guarantee, in a multiuser
    environment, that the value you got was the value you actually used. You
    can also user last_value in an SQL statement, but you still have the
    problem of having multiple backends generating sequence values.

    A more reliable way is to explicitly call nextval('<seq name>') and use
    the value returned to insert into the database.
    This method don't encourage using serial data type. Practically, in the
    joined table I can't use "serial" data type for primary key. I nead to
    use sequences by hand.
    The last solution work fine, but I need a variable for store the
    nextval('<seq name>').

    There is not a other solution ?



    Please see the CREATE SEQUENCE documentation at
    http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createsequence.htm

    -- Brett
    http://www.chapelperilous.net/~bmccoy/
    ---------------------------------------------------------------------------
    While my BRAINPAN is being refused service in BURGER KING, Jesuit
    priests are DATING CAREER DIPLOMATS!!
  • Brett W. McCoy at Mar 2, 2001 at 6:50 pm

    On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:

    This method don't encourage using serial data type. Practically, in the
    joined table I can't use "serial" data type for primary key. I nead to
    use sequences by hand.
    The last solution work fine, but I need a variable for store the
    nextval('<seq name>').

    There is not a other solution ?
    It turns out I was wrong -- using currval is session-based, as Tom Lane
    has pointed out. However, still see the notes in the docs on caching
    more than one values for each backend. This may or not be pertinent.

    -- Brett
    http://www.chapelperilous.net/~bmccoy/
    ---------------------------------------------------------------------------
    While money doesn't buy love, it puts you in a great bargaining position.
  • Tom Lane at Mar 2, 2001 at 5:48 pm

    "Brett W. McCoy" <bmccoy@chapelperilous.net> writes:
    On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:
    How can I get Last ID inserted ???
    currval('<seq name>') is the way to get the value that was last inserted
    into the database. However, there is no guarantee, in a multiuser
    environment, that the value you got was the value you actually used.
    Quite a few people don't seem to understand how currval() and nextval()
    work.

    1. nextval() advances the sequence object, generating a new value that
    will not be the same as any other nextval() call returns, in either
    this backend or any other one.

    2. currval() gives the last value generated by a nextval() *IN THIS
    BACKEND*. It is undefined until the current backend has done at
    least one nextval() on the sequence object.

    There is no "multiuser risk" from either one: in particular, currval()
    will give you the value you last generated, regardless of what other
    backends may be doing.
    You can also user last_value in an SQL statement, but you still have
    the problem of having multiple backends generating sequence values.
    Yes, looking directly at the sequence's last_value does open up race
    conditions.

    regards, tom lane
  • Brett W. McCoy at Mar 2, 2001 at 6:20 pm

    On Fri, 2 Mar 2001, Tom Lane wrote:

    Quite a few people don't seem to understand how currval() and nextval()
    work.

    1. nextval() advances the sequence object, generating a new value that
    will not be the same as any other nextval() call returns, in either
    this backend or any other one.

    2. currval() gives the last value generated by a nextval() *IN THIS
    BACKEND*. It is undefined until the current backend has done at
    least one nextval() on the sequence object.

    There is no "multiuser risk" from either one: in particular, currval()
    will give you the value you last generated, regardless of what other
    backends may be doing.
    How ironic... I got into an argument not too long ago with someone on the
    Perl-DBI list who insisted that currval could not be reliably used in a
    multi-user environment and I argued what you say above. I eventually
    conceded the argument, but am glad to know that I was right all along.
    But now I feel bad for passing on wrong information...

    -- Brett
    http://www.chapelperilous.net/~bmccoy/
    ---------------------------------------------------------------------------
    "The chain which can be yanked is not the eternal chain."
    -- G. Fitch
  • Chuck Kimber at Mar 2, 2001 at 6:34 pm

    How can I get Last ID inserted ???
    The problem is for a multiuser(symultans) database. Exist a statment SQL
    witch get the last id inserted for a session ?
    Ignoring the usefulness you may find using something like nextval, when I
    insert something into a database I already know something about how to
    locate what makes the inserted row unique. Either I have something like a
    firstname, lastname, address or some combination of values I've just
    inserted that are fairly unique when combined together.

    So I probably did something like:

    Insert Into MyTable
    (firstname, lastname, address, city, state)
    Values
    ('$MyFirstName', '$MyLastName', '$MyAddress', '$MyCity', '$MyState');

    So after I do that, I just query the table again with something like:

    Select max(UniqueID)
    From MyTable
    Where
    firstname = '$MyFirstName'
    AND
    lastname = '$MyLastName'
    AND
    address = '$MyAddress'

    And I just add anything else in the "Where" clause that will give me more
    uniqueness. And I obviously know these things because I just inserted them.
    Using "max()" provides the newest UniqueID where those variables are true.

    This only works though if you have some arrangement of variables that when
    combined with max have a very high likely-hood of producing the unique id
    you are looking for. If your variables are not very unique, and in a
    multi-user environment they may or may not be, this won't be very effective
    for you. If they were all numeric fields with a high chance of duplication,
    and your users were all inserting them with rapid fire, using max may give
    you something someone has inserted since your data insertion... Game Over.

    -Chuck

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 2, '01 at 3:51p
activeMar 2, '01 at 6:50p
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase