I didn't know about the serial type... I think that is by far the easiest route... However, I had the same problem and this is how I solved it...
Create a sequence using the following syntax :
- CREATE SEQUENCE sequence [INCREMENT increment] [MINVALUE minvalue] [MAXVALUE maxvalue] [START start] [CACHE cache] [CYCLE]
e.g.
CREATE SEQUENCE my_sequence INCREMENT 1 START 100 {or whatever you want to start at}
This gives you a lot of control on your autoincrement values. I've used this so that test data has a value less than an arbitrary number so I can test different things. Anything that gets inserted automatically gets one of the generated id's or I can force an id...
Then for your autoincrementing column do the following declaration:
CREATE TABLE mytable (
my_id int8 not null default nextval('my_sequence::text) primary key,
my_name varchar(32),
my_data text
);
When you do an insert omit the column's name that has a default value from the list of columns...
e.g.
- INSERT INTO mytable (my_name, my_data) values ('A Name','Some Data');
Remember that dropping a table and recreating it doesn't reset the sequence. You must explicitly drop the sequence...
If your not sure what the sequences are you can do a \ds to list all the sequences at the psql prompt...
Hope this helps
-
-
Thomas Swan - ________________________________________
- Graduate Student - Computer Science
- The University of Mississippi
-
- "People can be sorted into two fundamental groups,
- those that divide people into two groups and
- those that don't."