Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.7.5) with ESMTP id GAA19148 for ; Sun, 19 Jul 1998 06:40:41 -0400 (EDT) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id NAA21915; Sun, 19 Jul 1998 13:40:08 +0300 (IDT) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <35AF305B.14B1124B@link.it> References: <199807171006.GAA29551@hub.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Sun, 19 Jul 1998 13:40:00 +0300 To: Federico Passaro , wari@technology.com From: Herouth Maoz Subject: Re: Autoincrement Cc: pgsql-sql@postgreSQL.org (redirected to the SQL list because it really has nothing to do with interfaces): At 14:07 +0300 on 17/7/98, Federico Passaro wrote: > 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'); No! The syntax may look attractive to you, because you have to write less, but you will pay for it in performance! I definitely would not put that field last. This is because in the current version of Postgres, any fields following the first VARCHAR have a performance penalty. So, in order to avoid this, you should place all fixed-sized fields at the beginning, and then all the variable-length fields. In any case it is always recommeded to explicitly specify the names of all the fields in an insert operation, rather than rely on your memory of the correct order. > A more robust solution is to use a trigger. Look at the files > /contrib/spi/autoinc.* I am not sure a solution which depends on writing code in C and having postgres superuser privileges can be considered "more robust". I'd recommend the use of sequences in any case. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma