Re: Seral field value after INSERT !

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!