Re: Database Features Questions,

On Wed, 20 Sep 2000 13:37:32 -0400, Joe Kislo wrote:
>
> I see that to create unique identifiers for a column, I can use the OID
> value or a sequence.  If I use a sequence, is there any way of having
> the sequence value returned after an insert, instead of the OID?  Or for
> every insert I do, do I need to do two requests -- One to insert, and
> one to do a lookup by OID to fetch the sequence number of the row I just
> inserted?
>

you should not use the OID value for application level work.  for one thing,
it's not portable, and if you rebuild the database it'll change.  you should
consider it a strictly internal value.

anyway, that's what sequences are for (as you recognize).  they're portable,
and under your control.  you can create one like so:

    CREATE SEQUENCE tablename_colname_seq;
    CREATE TABLE tablename
        (colname INT4 DEFAULT nextval('tablename_colname_seq');
    CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);

in the midst of a transaction you can get the current value of the sequence
by calling the funciton 'currval':

    SELECT currval('tablename_colname_seq');

This is the most portable way of accomplishing what you're after.

Postgres also offers a special data type called 'SERIAL':

    CREATE TABLE tablename (colname SERIAL);

is the equivalent of the above 3 create statements.

There's more info on this under 'Datatypes' in the User section of the
Postgres docs (from which i copied this example) that come with the distribution.

there are also additional sequence functions that could be
of use like nextval(), etc.  there's info on them in the docs
to.

regards
--e--