Thread: Autoincrement

Hi, is there a way to do a field that can automatically increment itself,
like in paradox or dbase?

________________________________________
Regards
Wari Wahab

> Hi, is there a way to do a field that can automatically increment itself,
> like in paradox or dbase?
>

this is a hack, but it works...

CREATE SEQUENCE key_s INCREMENT 1 START 1;
CREATE TABLE cliente (
    key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
    name     varchar(100) UNIQUE NOT NULL,
    username    varchar(8) NOT NULL
    );
Antonio Garcia Mari
Mallorca (Spain)

Antonio Garcia Mari wrote:

> > Hi, is there a way to do a field that can automatically increment itself,
> > like in paradox or dbase?
> >
>
> this is a hack, but it works...
>
> CREATE SEQUENCE key_s INCREMENT 1 START 1;
> CREATE TABLE cliente (
>         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
>         name    varchar(100) UNIQUE NOT NULL,
>         username        varchar(8) NOT NULL
>         );
> Antonio Garcia Mari
> Mallorca (Spain)

  You are right, but it's better to put the autoincrementing field as the last
one like in:

CREATE TABLE cliente (
        name    varchar(100) UNIQUE NOT NULL,
        username        varchar(8) NOT NULL ,
        key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
        );

This way you can use the sintax

insert into cliente values ('JACK', 'postgres');

in place of

insert into cliente (name, username) values ('JACK', 'postgres');

A more robust solution is to use a trigger. Look at the files
<PostGreSQL source dir>/contrib/spi/autoinc.*

federico

> Hi, is there a way to do a field that can automatically increment itself,
> like in paradox or dbase?
>
>

Read the man page on create_sequence.  This will tell you how its done in
Postgres...james