Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2611
  • Last Modified:

'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.
0
aolXFT
Asked:
aolXFT
  • 2
1 Solution
 
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
 
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now