I've got a generic SQL table shown below in PostgreSQL 9.1:
zoo=# \d 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
"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:
zoo=# CREATE SEQUENCE users_users_id_seq
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:
Once you have created the sequence, just change the default value for the column in question:
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?
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).