Thread: Auto-incrementing field

Is there a data type in PostgreSQL that will 'auto-increment'?  I have used
this in MS SQL Server in the past and would like to continue to do so with
PostgreSQL.  Thanks for any help...

    -Jim

Jim Steil
CustomCall Data Systems
(608) 274-3009

On Fri, Jul 26, 2002 at 14:49:30 -0500,
  Jim Steil <jim.steil@customcall.com> wrote:
> Is there a data type in PostgreSQL that will 'auto-increment'?  I have used
> this in MS SQL Server in the past and would like to continue to do so with
> PostgreSQL.  Thanks for any help...

You probably want to use sequences. There is even a psuedo type "serial"
that makes using them easier.
Note that sequences provide unique values, but they can skip over values.
Normally this is OK for keys, but if you really want a counter then
you need to do something different.

Hi !

Please consider using a scriplet of your own for the locking db, getting e.g.
the max id +1 as your next increment, inserting the record (probably only
with the new id) and then unlock the db.

Sometimes it is worth because auto-increment as index migth get you into
trouble in certain situations. Or if you get into an other sort of bad
situation with corrupted data, having used auto-increment as index will
double your trouble or your work amount atleast.

BR,


aarni


On Friday 26 July 2002 10:49 pm, you wrote:
> Is there a data type in PostgreSQL that will 'auto-increment'?  I have used
> this in MS SQL Server in the past and would like to continue to do so with
> PostgreSQL.  Thanks for any help...
>
>     -Jim
>
> Jim Steil
> CustomCall Data Systems
> (608) 274-3009
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Yes.. Try SERIAL datatype.

test=# Create Table test (my_auto SERIAL,Name text);
NOTICE:  CREATE TABLE will create implicit sequence 'test_my_auto_seq' for
SERIAL column 'test.my_auto'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_my_auto_key'
for table 'test'
CREATE
test=# insert into test(name) values ('Denis');
INSERT 13170208 1
test=# select * from test;
 my_auto | name
---------+-------
       1 | Denis

(1 row)

Note : It implicitely creates SEQUENCE and associate it with the field. But
if you drop the table, you need to drop the SEQUENCE manually.. it does not
drop it automatically..

Denis


----- Original Message -----
From: "Jim Steil" <jim.steil@customcall.com>
To: <pgsql-novice@postgresql.org>
Sent: Saturday, July 27, 2002 1:19 AM
Subject: [NOVICE] Auto-incrementing field


> Is there a data type in PostgreSQL that will 'auto-increment'?  I have
used
> this in MS SQL Server in the past and would like to continue to do so with
> PostgreSQL.  Thanks for any help...
>
> -Jim
>
> Jim Steil
> CustomCall Data Systems
> (608) 274-3009
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly