Results 1 to 6 of 6

Thread: autonumbering

  1. #1
    Join Date
    Apr 2003
    Posts
    137

    Unanswered: autonumbering

    Hi

    I am moving from SQL server to PostgreSQL and I don't know how can create an autonumber for my table to be as customer_id for my customers table and to use as primary key?

    Could you please guide

    Thanks
    Jassim

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Ref the Serial or BigSerial data types.

    (Serial is a shortcut; when you define a field type as serial, the system creates an integer field type, a sequence with the same base name as the field name, and sets the default value of the field to nextval(sequence))

    You would explicitly define the PK as the sequence field.
    sequences are much more flexible than identity fields in SQL server; the same sequence can be used my multiple tables, for instance, or you can define the number of values to be buffered when the sequence calculates the sequence values. (When you normally insert many records at a time, setting a large cache value results in more efficient sequence data generation; at the potential expense of 'losing' numbers when the connection closes. (the cache value of sequence numbers are generated at one time, and are assigned for the exclusive use of the connection which issued the NextVal function; any unused sequence values in the connection cache are lost when the connection is dropped.)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Apr 2003
    Posts
    137
    but why I don't have the serial datatype in the pgAdmin?

    Note: I have PostgreSQL 9 and pgAdmin 1.12.0

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jrahma View Post
    but why I don't have the serial datatype in the pgAdmin?
    I don't use pgAdmin but it took me about 10 seconds to locate the serial datatype in the "Data type" dropdown of the "New Column" dialog...

  5. #5
    Join Date
    Apr 2003
    Posts
    137
    ok here is the problem...

    yes it is there, sorry i didn't notice it but i was doing this:

    I already created he table and the column as integer and went to the column then choosed Properties of the column to chnage the data type... there I didn't find the serial data type.. only few data types are there...

    thanks anyhow...

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jrahma View Post
    I already created he table and the column as integer and went to the column then choosed Properties of the column to chnage the data type... there I didn't find the serial data type.. only few data types are there...
    As "serial" is a just shorthand for "integer default nextval('some_sequence')" you can apply that manually at any time.

    To change an existing column, just create the manually and then change the default value for the column:
    Code:
    CREATE SEQUENCE my_id_sequence
      START WITH 1234
      OWNED BY my_table.id_column;
    ALTER TABLE my_table 
      ALTER id_column SET DEFAULT nextval('my_id_sequence');
    The "owned by" part will make sure the sequence is dropped when the table is dropped.
    The "start with" makes sure the next generated value is bigger than the current max value in that column. If the table is empty, you can (and should) leave that out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •