Thread: Auto Increment Questions
I am currently working on a project that may be used throughout the United States. I have created the project database in MySQL but have been looking at Postgres due to it being completely free. The application will be web based and the language with primarily be PHP. In MySQL there is the availability of an auto increment field. I need this type of field in several of the tables. Looking in the archives, I see that there is a serial 8 filed that basically does the same thing. In MySQL, auto increment is just that - automatically incremented for each new record. In Postgres I see that it is not so automatic and is not truly serial/sequential. Reviewing my objectives, I don't think not being truly serial/sequential will be a problem. However, I prefer the automated part versus my having to code additional lines and hits against the database. Questions: - Is there ever going to be a truly auto increment feature like MySQL has? If so, any ideas when? - What is the easiest, shortest, and safest way to code for serial? - Are there any pitfalls, gotchas that I need to know about using serial? Thank you for your help. Mike -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.1 - Release Date: 6/3/2005
Attachment
How is SERIAL not automatic? Have you read the FAQ? --------------------------------------------------------------------------- Michael Avila wrote: > I am currently working on a project that may be used throughout the United > States. I have created the project database in MySQL but have been looking > at Postgres due to it being completely free. The application will be web > based and the language with primarily be PHP. > > In MySQL there is the availability of an auto increment field. I need this > type of field in several of the tables. Looking in the archives, I see that > there is a serial 8 filed that basically does the same thing. In MySQL, auto > increment is just that - automatically incremented for each new record. > > In Postgres I see that it is not so automatic and is not truly > serial/sequential. Reviewing my objectives, I don't think not being truly > serial/sequential will be a problem. However, I prefer the automated part > versus my having to code additional lines and hits against the database. > > Questions: > > - Is there ever going to be a truly auto increment feature like MySQL has? > If so, any ideas when? > > - What is the easiest, shortest, and safest way to code for serial? > > - Are there any pitfalls, gotchas that I need to know about using serial? > > Thank you for your help. > > Mike > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.323 / Virus Database: 267.6.1 - Release Date: 6/3/2005 > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Jun 4, 2005, at 2:35 PM, Michael Avila wrote: > In Postgres I see that it is not so automatic and is not truly > serial/sequential. Reviewing my objectives, I don't think not being > truly > serial/sequential will be a problem. However, I prefer the automated > part > versus my having to code additional lines and hits against the > database. > > Questions: > > - Is there ever going to be a truly auto increment feature like MySQL > has? > If so, any ideas when? > > - What is the easiest, shortest, and safest way to code for serial? > > - Are there any pitfalls, gotchas that I need to know about using > serial? Serial fields are a suitable replacement for auto increment. All you need to do is declare your column type to be serial or bigserial. If you have any concerns about having more rows than the serial or bigserial can represent, declare the column as unique. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Michael Avila wrote: >In Postgres I see that it is not so automatic and is not truly >serial/sequential. Reviewing my objectives, I don't think not being truly >serial/sequential will be a problem. > Are you saying that since serial isn't guaranteed to be 1,2,3,4,5 without gaps there will be a problem? You can't just do something like "select max(val) + 1 from table" to get the next serial value. You need to do "select nextval(sequence)" and then use "currval(sequence)" to get the current value. If you don't care what the inserted value is, just use the DEFAULT keyword in your inserts. More information can be found in the manual. Jeff