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 EAA10650 for ; Mon, 20 Jul 1998 04:06:46 -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 LAA00563 for ; Mon, 20 Jul 1998 11:06:37 +0300 (IDT) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 20 Jul 1998 11:06:45 +0300 To: pgsql-sql@postgreSQL.org From: Federico Passaro Subject: Re: Autoincrement Herouth Maoz wrote: > (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. When I have to pay this penalty: on insert / update / query ? > > > 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. Could you explain your assertion please: the C code I mentioned is ready and well tested .... > > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma federico