Re: pgsql and adodb's genID() for unique id sequence...

On Wed, 2 Feb 2005 12:21:51 -0800 (PST), operationsengineer1(at)yahoo(dot)com
<operationsengineer1(at)yahoo(dot)com> wrote:
> does anybody use this technique? it is supported by
> pgsql, however, setting it up isn't intuitive to this
> newbie.

Perhaps we should start this off with a very simple example:

CREATE TABLE bands (
id serial NOT NULL PRIMARY KEY,
band_name varchar(32) NOT NULL UNIQUE
)

When you create this table, it basially takes care of the sequence
with the 'serial' datatype. This is analogous to MySQL's
AUTO_INCREMENT feature.

If you use this philosophy for your datamodel, then you won't need to
create sequences as they will be done automatically for you when the
tables are created.

Now, how do you get access to them? Well just like MySQL'sa
AUTO_INCREMENT, they are automatic (default values) for inserts into
that table. So the following:

INSERT INTO bands (band_name) VALUES ('green day');

Would give you a record with an id of 1, and a band_name of 'green
day'. If you were to repeat with different bands, they also would get
increasing id numbers as well. This is all driven by the sequence that
was automatically created for you when you defined the id column as a
serial datatype (which in essence is an integer with a sequence
created and set as the default value).

If you ever need to know the new value of that id right after you
insert a record, you can also get it by using ADOdb's Insert_ID()
function, documented here:

http://phplens.com/lens/adodb/docs-adodb.htm#inserted_id

One other thing - I would suggest setting up PgAdminIII or phpPgAdmin
for visual access to the database, as that will let you explore the
schema and see the sequences and their values. Much easier than on the
CLI for most newcomers.

HTH,

-- Mitch