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