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]

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.

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 '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.

ObPerl: You can also use a wrapper like (plug plug) SPOPS module to do this for you -- there is example code at this node.

Chris
M-x auto-bs-mode

Who said it had to be consecutive? This is sequential, unique (unless you can create more than one row per microsecond in a single process), and portable.

use Time::HiRes qw(gettimeofday);
my ($secut,$musec) = gettimeofday;
my ($uniqueid) = sprintf("%010d%06d%05d", $secut, $musec, $$);
[download]

Notes:

• The lead 0s are there to pad the digits to the proper resolution. If your system supports >16-bit process-IDs, that last 5 may have to change.
• This can also scale across multiple machines (eg. multiple webservers writing to the same database), but to do so, each machine should have a unique numeric machine identifier as part of the id as well; add an extra 2 or 3 digit (or bigger, if you have to!) integer to the expansion to account for it.
• Keeping the first two variables in that order will assure that the records have linearity -- ie. sorting on this ID will put things in proper chronological order, with one-microsecond granularity.

Spud Zeppelin * spud@spudzeppelin.com

If this unique ID does not have to be any thing in particular, you might as well use the oid column. oid is unique for every single row in the database, and is created when you insert a row.

It's sort of a "hidden" field, so when you query, you have to do

     SELECT oid,* FROM table;
 
     # if you already know your oid..
     SELECT * FROM table WHERE oid = x;
[download]

This is so much easier than maintaining a sequence.... and is universal for Postgres.

This is originally how i thought would be a good way to handle this. I'll give it a shot...thank you ;)

So how would i do a delete statement WHERE oid = "$oid" ?

That is correct, sir

Well, since you are using "$oid", I should make sure and point out that you really should use place holders...

     $dbh->do( "DELETE FROM table WHERE oid = ?", undef, $oid );

     or

      $sth = $dbh->prepare( "DELETe FROM table WHERE oid = ?" );
      $sth->execute( $oid );
[download]