Received: from virtual.zia.ms.it (root@virtual.zia.ms.it [195.103.158.42]) by
	hub.org (8.8.8/8.7.5) with ESMTP id HAA02925 for
	<pgsql-interfaces@postgreSQL.org>;
	Fri, 17 Jul 1998 07:07:15 -0400 (EDT)
Received: from link.it (rub080.pi00.c1.interbusiness.it [194.184.214.80])
	by virtual.zia.ms.it (8.8.8/8.8.8) with ESMTP id NAA02599;
	Fri, 17 Jul 1998 13:07:09 +0200
Sender: federico@link.it
Message-ID: <35AF305B.14B1124B@link.it>
Date: Fri, 17 Jul 1998 13:07:08 +0200
From: Federico Passaro <fede@link.it>
X-Mailer: Mozilla 4.03 [en] (X11; I; Linux 2.0.34 i586)
MIME-Version: 1.0
To: PostGreSQL Interface <pgsql-interfaces@postgreSQL.org>,
	Wari Wahab <wari@technology.com>
Subject: Re: [INTERFACES] Autoincrement
References: <199807171006.GAA29551@hub.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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