Thread: Seral field value after INSERT !

Hi all !

I have been inserting records into a table using the SQL insert statement.
One of the field types is serial, and I have been
trying to figure out how to get the value that was assigned in the field as
a result of the insert. The serial typed field is the only one guaranteed
to be unique, so I can't really do a search, and there are several people
adding data at once, so I can't reliable guess. Can anyone help?

Thanks for all help !

ps : Sorry my bad english.

---------------
Gabor Berenyi

> I have been inserting records into a table using the SQL insert statement.
> One of the field types is serial, and I have been
> trying to figure out how to get the value that was assigned in the field
as
> a result of the insert. The serial typed field is the only one guaranteed
> to be unique, so I can't really do a search, and there are several people
> adding data at once, so I can't reliable guess. Can anyone help?

You can get the serial value by doing a SELECT currval('SeqName'); after
performing the insert. That value will be correct for the last INSERT
performed *by that backend*. This means that if each of those people are
adding data at once, one particular person will see only the last value that
that person inserted regardless of what the actual last value inserted was.

The SeqName for a serial is usually just <TableName>_<SerialField>_SEQ.
There is a character limit, so if you have really long field table/field
names, you might have to look at the system catalogs.

Greg

On Wed, Apr 25, 2001 at 09:32:07AM +0200, Berényi Gábor wrote:
> I have been inserting records into a table using the SQL insert statement.
> One of the field types is serial, and I have been
> trying to figure out how to get the value that was assigned in the field as
> a result of the insert. The serial typed field is the only one guaranteed
> to be unique, so I can't really do a search, and there are several people
> adding data at once, so I can't reliable guess. Can anyone help?

Perfect question (partly because i know the answer). "currval()"!

    create table mytab (
        id serial,
        t text,
        v varchar(93),
        f float8,
        ...etc...
    );
    \d mytab
                           Table "mytab"
 Attribute |  Type   |                   Modifier
-----------+---------+-----------------------------------------------
 id        | integer | not null default nextval('mytab_id_seq'::text)
...etc...

The 'sequence' is named
    <tablename> underscore <fieldname> underscore "seq"
so in this case (as you can see from \d above) it's
    mytab_id_seq

here's how to use it--

    insert into mytab (t,v,f) values (
        'some text just for fun',
        'variable character string here',
        22.0/7.0
    );
    --we don't specify a value for "id" since the
    --'default' value will take care of it for us

    select currval('mytab_id_seq');

Now here's the not-quite-what-you-at-first-expect part:

Until you run "nextval" (i.e. 'bump' your sequence counter by
inserting a new row of data) you will not be able to see the
"currval" at all. In fact, you'll get an error.

This seemed odd at first -- but there's no value in knowing what
another user's "currval" might be at the moment, right? You're
only concerned about your own, and you don't really have one to
use until you insert a new row. THEN you can use that value to
create rows in other tables that link to your new 'tuple'.

> Thanks for all help !
>
> ps : Sorry my bad english.

Your english is better than that of most american college
graduates. (Not that we're proud, but you should be.)

--
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!