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();

[download]
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?
[reply]
      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 'foo.id'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pk' for table 'foo'
CREATE
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.
[reply]

In Section Seekers of Perl Wisdom