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

    Unanswered: Auto Increment On Existing Table

    Is it possible to configure an existing table to have auto increment of a field?

    Code:
    xbox=# \d games
                 Table "public.games"
      Column   |         Type          | Modifiers
    -----------+-----------------------+-----------
     id        | integer               | not null
     title     | character varying(30) | not null
     developer | character varying(20) | not null
     publisher | character varying(20) | not null
     release   | date                  | not null
     genre     | character varying(20) | not null
     price     | numeric               | not null
    Indexes:
        "games_pkey" PRIMARY KEY, btree (id)
        "games_title_key" UNIQUE, btree (title)
    Check constraints:
        "games_price_check" CHECK (price > 0::numeric)
    I have the table above and would like to create a sequence 'games_seq' so that it will auto increment the 'id' field (primary key) but don't know if this is something I can do since the table already exists and holds data. Before I break anything, can someone tell me if this looks correct:

    Code:
    CREATE SEQUENCE games_seq_id;
    SELECT setval('games_seq_id', max(id)) FROM games;
    ALTER TABLE games ALTER COLUMN id SET DEFAULT
    nextval('games_seq_id');
    Anyone?

  2. #2
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Decided to test this out on a generic test database and it worked great. Just in case others are interested.

    -Carlos

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You might want to add

    Code:
    ALTER SEQUENCE games_seq_id OWNED BY games.id
    Then the sequence will automatically be dropped if you drop the table (or the id).

    Then the definition is the same as if you had defined the column as serial right from the start

  4. #4
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Oh that's good to know. I hate lingering or zombie entries once I delete fields or items.

    Thank you!

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    You might want to add

    Code:
    ALTER SEQUENCE games_seq_id OWNED BY games.id
    Then the sequence will automatically be dropped if you drop the table (or the id).

    Then the definition is the same as if you had defined the column as serial right from the start
    I ran the following command:

    Code:
    xbox=# ALTER SEQUENCE games_seq_id OWNED BY games.id;
    ALTER SEQUENCE
    However when I check, I show the sequence is actually still owned by myself. Am I getting ownership confused here?

    Code:
    xbox=# \d
                  List of relations
     Schema |     Name     |   Type   |  Owner
    --------+--------------+----------+----------
     public | games        | table    | cmennens
     public | games_seq_id | sequence | cmennens
    (2 rows)

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    However when I check, I show the sequence is actually still owned by myself. Am I getting ownership confused here?
    Yes

    A sequence can have two "owners". The first one is the traditional one: whoever created the object owns it. The other is a mere relationship that is established between the sequence and the column it provides its values for. "belongs to" would probably been a better term.

  7. #7
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    How can I see a sequence relationship? Is there a \? for that? I couldn't find it

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    How can I see a sequence relationship? Is there a \? for that? I couldn't find it
    Sorry I don't know. I usually don't use psql

  9. #9
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Interesting. You're always so helpful here. Just out of curiosity, what database engine do you normally use?

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    Interesting. You're always so helpful here. Just out of curiosity, what database engine do you normally use?
    I do use PostgreSQL, but I don't use psql as the SQL client.

  11. #11
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I created a sequence as one user when the table is owned by another and for some reason I can't change the ownership or drop the sequence now for some reason:

    Code:
    zoo=# \d
                  List of relations
     Schema |     Name     |   Type   |  Owner
    --------+--------------+----------+----------
     public | cisco        | table    | it
     public | domain       | table    | it
     public | oracle       | table    | it
     public | tickets      | table    | it
     public | users        | table    | it
     public | users_seq_id | sequence | cmennens
    (6 rows)
    Trying to change it:

    Code:
    zoo=# ALTER SEQUENCE users_seq_id OWNED BY it;
    ERROR:  invalid OWNED BY option
    HINT:  Specify OWNED BY table.column or OWNED BY NONE.
    I then tried it as suggested in the hint:

    Code:
    zoo=# ALTER SEQUENCE users_seq_id OWNED BY users.id;
    ERROR:  sequence must have same owner as table it is linked to
    I can't drop the sequence now because it's associated with the table now:

    Code:
    zoo=# DROP SEQUENCE users_seq_id;
    ERROR:  cannot drop sequence users_seq_id because other objects depend on it
    DETAIL:  default for table users column id depends on sequence users_seq_id
    HINT:  Use DROP ... CASCADE to drop the dependent objects too.

  12. #12
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    One last thing. I know in order to have a specific column in PostgreSQL to auto increment, I would need to create a sequence and attribute it to the column id but what if I have 10 tables, do I need 10 unique sequences created to all do the same thing? Seems like a mess when I show my tables with \d. In MySQL I simply associate is as:

    Code:
    CREATE TABLE test 
    (
    id INT PRIMARY KEY AUTO_INCREMENT
    );
    If all my tables in every database have a PRIMARY KEY column named 'id' and I want them to auto increment, can't I not use one single sequence or must I have one for every table?

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use the serial datatype:
    Code:
    CREATE TABLE test 
    (
       id serial PRIMARY KEY NOT NULL
    );
    It's a shorthand that will generate the sequence and then associating it with the column.

    See the manual for details:
    http://www.postgresql.org/docs/curre...ATATYPE-SERIAL

  14. #14
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Is the 'SERIAL' data type only available in 9.0 because on my 8.4 production server, I can't seem to find it:

    Code:
    ide=# ALTER TABLE users ALTER COLUMN id TYPE SERIAL;
    ERROR:  type "serial" does not exist
    Either my SQL syntax is wrong of 'serial' isn't supported in 8.4.

    **EDIT**

    It appears 8.4.x does support 'serial' data type as it's at the bottom of this page.

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Numeric Types

    I'm then left to conclude my command for converting my id column from INT to SERIAL is wrong...

  15. #15
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You cannot "alter" to serial, that is only valid during CREATE TABLE as far as I know. If you need to do it after creating the table, the only way is creating the sequence manually (basically doing everything in the little box "is equivalent to specifying:" from the manual

Posting Permissions

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