Thread: Auto Increment

I am writing a small stamping program and was wondering: what is the best
way to do auto incrementing. I use MySQL also, and it handles
auto-incrementing, but postgresql does not.

I am using the Perl-DBI interface.

Thanks
-
Stephen Lawrence Jr.
logart@dairypower.com

I'm a Postgres newbie who used MySQL autoincrement functionality
too.  Here's what I found.

If you will create the column type as serial, postgres will
create a sequence and assign the default value for the serial
column to be the sequence.nextval() value.

or you can just create a sequence and upon insertion into the
column, insert the value of the column as sequence.nextval() yourself.

This works for me.  HTH.

-darrel

On Fri, 7 Jul 2000, Stephen Lawrence wrote:

> I am writing a small stamping program and was wondering: what is the best
> way to do auto incrementing. I use MySQL also, and it handles
> auto-incrementing, but postgresql does not.
>
> I am using the Perl-DBI interface.
>
> Thanks
> -
> Stephen Lawrence Jr.
> logart@dairypower.com
>
>

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