Auto Increment On Existing Table

CarlosinFL · #1 (**permalink**) 05-11-11, 13:39

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?

CarlosinFL · #2 (**permalink**) 05-11-11, 14:28

Decided to test this out on a generic test database and it worked great. Just in case others are interested.

-Carlos

shammat · #3 (**permalink**) 05-11-11, 14:55

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

CarlosinFL · #4 (**permalink**) 05-11-11, 15:17

Oh that's good to know. I hate lingering or zombie entries once I delete fields or items.

Thank you!

CarlosinFL · #5 (**permalink**) 05-12-11, 11:57

Quote:

Originally Posted by shammat

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)

shammat · #6 (**permalink**) 05-12-11, 14:53

Quote:

Originally Posted by CarlosinFL

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.

CarlosinFL · #7 (**permalink**) 05-12-11, 15:11

How can I see a sequence relationship? Is there a \? for that? I couldn't find it

shammat · #8 (**permalink**) 05-12-11, 15:37

Quote:

Originally Posted by CarlosinFL

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

CarlosinFL · #9 (**permalink**) 05-12-11, 15:47

Interesting. You're always so helpful here. Just out of curiosity, what database engine do you normally use?

shammat · #10 (**permalink**) 05-12-11, 16:02

Quote:

Originally Posted by CarlosinFL

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.

CarlosinFL · #11 (**permalink**) 05-13-11, 12:35

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.

CarlosinFL · #12 (**permalink**) 05-17-11, 11:19

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?

shammat · #13 (**permalink**) 05-17-11, 11:34

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

CarlosinFL · #14 (**permalink**) 05-17-11, 13:51

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...

shammat · #15 (**permalink**) 05-17-11, 15: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

Internet Services

Web Development

Digital Marketing

Consumer Tech