Is it possible to configure an existing table to have auto increment of a field?
xbox=# \d 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
"games_pkey" PRIMARY KEY, btree (id)
"games_title_key" UNIQUE, btree (title)
"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:
CREATE SEQUENCE games_seq_id;
SELECT setval('games_seq_id', max(id)) FROM games;
ALTER TABLE games ALTER COLUMN id SET DEFAULT
However when I check, I show the sequence is actually still owned by myself. Am I getting ownership confused here?
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.
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:
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
Trying to change it:
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:
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:
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.
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:
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?
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