Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Sequence Association w/ Table

    I've got a generic SQL table shown below in PostgreSQL 9.1:

    Code:
    zoo=# \d users
                   Table "public.users"
        Column    |          Type          | Modifiers 
    --------------+------------------------+-----------
     users_id     | integer                | not null
     users_name   | character varying(100) | not null
     users_email  | character varying(100) | not null
     users_dob    | date                   | 
     users_salary | numeric(8,2)           | not null
    Indexes:
        "users_pkey" PRIMARY KEY, btree (users_id)
        "users_users_email_key" UNIQUE CONSTRAINT, btree (users_email)
        "users_users_name_key" UNIQUE CONSTRAINT, btree (users_name)
    Now I need a sequence associated with the above table so it can auto increment by one digit the 'users_id' column. If I change the data type to 'serial' then I don't know how to configure start value and so on but if I create the sequence manually as I did, then I can control such attributes like min / max values and such:

    Code:
    zoo=# CREATE SEQUENCE users_users_id_seq
    INCREMENT 1
    START 1000
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    ;
    CREATE SEQUENCE
    So the above command worked and created a sequence but there's no associaton between the sequence created and the existing table / users_id primary key column.

    My question is what is the recommended / correct way to do what I'm trying to achieve?

    Do I ALTER TABLE and change 'users_id' data type to 'serial' or something else and how do I control the attributes of the sequence?

    Doing the following doesn't allow me to set 'START' value:

    Code:
    CREATE TABLE users
    (
       users_id serial primary key,
       ...
    )
    ;
    Last edited by CacheDrive; 10-03-12 at 17:01.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Once you have created the sequence, just change the default value for the column in question:
    Code:
    alter table users 
      alter column users_id set default nextval(users_users_id_seq);
    To make the "connection" between the sequence and the column, use an alter sequence.
    Code:
    alter sequence users_users_id_seq owned by users;
    This is all explained in the manual:
    http://www.postgresql.org/docs/9.1/s...ATATYPE-SERIAL
    http://www.postgresql.org/docs/9.1/s...rsequence.html
    http://www.postgresql.org/docs/9.1/s...ltertable.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    Once you have created the sequence, just change the default value for the column in question:
    Code:
    alter table users 
    alter column users_id set default nextval(users_users_id_seq);
    Sorry I'm still a little unclear on what the correct way is to create the sequence. Do I change an existing data type on 'users_id' to 'serial' and let PostgreSQL do the create sequence behind the scenes or do I manually 'CREATE SEQUENCE'. Also the command above I don't relate to changing a value. What exactly do you mean "just change the default value for the column in question"? I would expect this to be some kind of ALTER SEQUENCE command or something but it looks like you're doing the sequence to column association above, not changing a value on an existing sequence. Can you clarify the 1st SQL command above so I can relate to what's happening?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Sorry I'm still a little unclear on what the correct way is to create the sequence. Do I change an existing data type on 'users_id' to 'serial' and let PostgreSQL do the create sequence behind the scenes or do I manually 'CREATE SEQUENCE'
    Sorry you lost me on what you actually want to achieve.

    If you want to change the column definition after you have created the table to be populated by a sequence do as I posted (and as it is shown in the manual).

    If you want to make sure a column is automatically populated by a sequence when you define the table, the do use the serial type.

    What exactly do you mean "just change the default value for the column in question"
    Exactly what I said. And I posted the corresponding ALTER TABLE ... SET DEFAULT statement in case you didn't notice (and again: there is an example in the manual as well).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Thank you!

Posting Permissions

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