Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re: Unique ID

by lhoward (Vicar)
on Jul 19, 2001 at 20:46 UTC ( #98141=note: print w/replies, xml ) Need Help??

in reply to Unique ID

The two easy ways to do this with Postgres. One is to specify the ID col. as serial type, which will cause it to be auto-incrementing (i.e. when you insert a row, it will automagically get a new, unique val in the serial col).

Additionally, you can use a postgres sequence to keep track of the ID, and query it with postgres's nextval function whenever you need a new value:

my $sth=$dbh->prepare("select nextval(?)"); $sth->execute("seq_name"); my $id=$sth->fetchrow(); $sth->finish();
Of coruse, you can also do this the old-school way too (but its less efficient to do it this way):

Have a table to store the last assigned ID. When you need an ID, lock that table, read the value out of it, write the value+1 back to the table, and then unlock it.

Replies are listed 'Best First'.
Re: Re: Unique ID
by nlafferty (Scribe) on Jul 19, 2001 at 21:05 UTC
    So if i use a serial type then i do not have to use the sequence as well. Or do i have to use a combination of both?
      The serial type creates and uses a sequence behing the scenes, but its operation is transparent to you. You do not need to create an additional sequence to use it. See... (using psql, Postgres's command line tool):
      test=# create table foo(ID serial NOT NULL,bar text, constraint foo_pk primary key (ID));
      NOTICE:  CREATE TABLE will create implicit sequence 'foo_id_seq' for SERIAL column ''
      NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pk' for table 'foo'
      test=# insert into foo (bar) values ('abc');
      INSERT 27135 1
      test=# insert into foo (bar) values ('def');
      INSERT 27136 1
      test=# select * from foo;
       id | bar
        1 | abc
        2 | def
      (2 rows)
      I find postgres sequences to be most useful when you want to share one unique ID across tables and when you don't have a master table to store it in (or don't have a table that will always be inserted first). Or when you want to assign something outside of the DB (at least at the time of assignment) a unique ID.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://98141]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (2)
As of 2019-08-24 20:39 GMT
Find Nodes?
    Voting Booth?

    No recent polls found