Re: Auto Increment

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