Thread: pgsql and adodb's genID() for unique id sequence...
does anybody use this technique? it is supported by pgsql, however, setting it up isn't intuitive to this newbie. ------------------------- http://phplens.com/lens/adodb/tips_portable_sql.htm When you create records, you need to generate unique id's for each record. There are two common techniques: (1) auto-incrementing columns and (2) sequences. Auto-incrementing columns are supported by MySQL, Sybase and Microsoft Access and SQL Server. However most other databases do not support this feature. So for portability, you have little choice but to use sequences. Sequences are special functions that return a unique incrementing number every time you call it, suitable to be used as database keys. In ADOdb, we use the GenID( ) function. It has takes a parameter, the sequence name. Different tables can have different sequences. $id = $connection->GenID('sequence_name'); $connection->Execute("insert into table (id, firstname, lastname) values ($id, $firstname, $lastname)"); For databases that do not support sequences natively, ADOdb emulates sequences by creating a table for every sequence. ----------------------- i'm left wondering if i need to create a sequence table with name 'sequence_name' before this technique will work. or, rather, is it just a name i need to plug in to uniquely identify a particular sequence controlled by the function? i'm sorry this is 50% off topic (adodb) and 50% on topic (pqsql). __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--- operationsengineer1@yahoo.com wrote: > does anybody use this technique? it is supported by > pgsql, however, setting it up isn't intuitive to > this > newbie. > > ------------------------- > > http://phplens.com/lens/adodb/tips_portable_sql.htm > > When you create records, you need to generate unique > id's for each record. There are two common > techniques: > (1) auto-incrementing columns and (2) sequences. > > Auto-incrementing columns are supported by MySQL, > Sybase and Microsoft Access and SQL Server. However > most other databases do not support this feature. So > for portability, you have little choice but to use > sequences. Sequences are special functions that > return > a unique incrementing number every time you call it, > suitable to be used as database keys. In ADOdb, we > use > the GenID( ) function. It has takes a parameter, the > sequence name. Different tables can have different > sequences. > > $id = $connection->GenID('sequence_name'); > $connection->Execute("insert into table (id, > firstname, lastname) values ($id, $firstname, > $lastname)"); > > For databases that do not support sequences > natively, > ADOdb emulates sequences by creating a table for > every > sequence. > > ----------------------- > > i'm left wondering if i need to create a sequence > table with name 'sequence_name' before this > technique > will work. or, rather, is it just a name i need to > plug in to uniquely identify a particular sequence > controlled by the function? > > i'm sorry this is 50% off topic (adodb) and 50% on > topic (pqsql). > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > i found out how to create a pgsql sequence in pgsql 7.4... http://www.postgresql.org/docs/7.4/static/sql-createsequence.html i guess my question now becomes - what are the relative merits of using a serial within each table or using a sequence that supports all tables (or several sequences to support tables of my choosing)? __________________________________ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com
On Wed, 2 Feb 2005 12:21:51 -0800 (PST), operationsengineer1@yahoo.com <operationsengineer1@yahoo.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