pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Federico Passaro <fede@link.it>
To: wari@technology.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: Autoincrement
Date: Sun, 19 Jul 1998 13:40:00 +0300
Message-ID: <l03110706b1d77a9ec5ac@[147.233.159.109]> (raw)
In-Reply-To: <35AF305B.14B1124B@link.it>
References: <199807171006.GAA29551@hub.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
> <PostGreSQL source dir>/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





view thread (11+ messages)  latest in thread

Message-ID: <l03110706b1d77a9ec5ac@[147.233.159.109]>
Permalink:  ../l03110706b1d77a9ec5ac@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110706b1d77a9ec5ac@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, fede@link.it, wari@technology.com
  Subject: Re: Autoincrement
  In-Reply-To: <l03110706b1d77a9ec5ac@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox