'auto_increment' in PostgreSQL

Hi

I'm working on converting a PHP/MySQL application to a PHP/PostgreSQL app.

I need help with the auto_increment.

I've read through the source code of a few scripts that support MySQL, and PostgreSQL, and noticed that they overcame the problem using Sequences.

The problem is that when I insert a row, from a dump, that has all the values present(including the id), the sequence isn't incremented, so then when I try to insert a row, without stating the id, it trys to use the first of the sequence which is the same as a value already in the db, which results in an error, in havig two values for the same primary key.

Is there any way for me to set a default of the highest number already in the database + 1?

While I'm here, and this isn't strictly part of the above question, but I'd like to know if this will be a problem with other DB systems, such as Oracle, Access, SQL Server, etc

I think this is a fairly simple question. If that proves not to be the case I'll up the point value.
LVL 6
aolXFTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tncbbthositgCommented:
Alter the column to be the PRIMARY KEY (this is the same as UNIQUE NOT NULL).  

You also do not use auto_increment in PostgreSQL, you use serial, serial4 or serial8.

Another alternative is to define the column as:
 yourcol integer DEFAULT nextval('yourtable_yourcol_seq') NOT NULL


You might also check out:
http://www.cat.org.au/catskills/begin4/tutorial/webdatabases.html
http://www.postgresql.org/docs/pdf/7.3/tutorial-7.3.2-A4.pdf
http://www.postgresql.org/docs/7.3/static/functions-sequence.html
http://www.postgresql.org/docs/7.3/static/sql-createsequence.html
http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-SERIAL

Good luck,
TNC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aolXFTAuthor Commented:
I think that Serial is just what I need.

What exactly are serial, serial4 and serial8.

I'll on a very slow internet connection at the minute,

I'll check them out, and get back in about half an hour.
0
aolXFTAuthor Commented:
I really think from looking at the docs that serial is what I need, although I haven't had a chance to test it.

I'll post back here if it's not.

If it's not, then what I do need will surely be very similar, so I'll give you your points now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.