pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Federico Passaro <fede@link.it>
To: pgsql-sql@postgreSQL.org
Subject: Re: Autoincrement
Date: Mon, 20 Jul 1998 11:06:45 +0300
Message-ID: <l03110702b1d8ab0b0cc8@[147.233.159.109]> (raw)

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
> > <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.

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





view thread (11+ messages)  latest in thread

Message-ID: <l03110702b1d8ab0b0cc8@[147.233.159.109]>
Permalink:  ../l03110702b1d8ab0b0cc8@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110702b1d8ab0b0cc8@[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: fede@link.it
  Subject: Re: Autoincrement
  In-Reply-To: <l03110702b1d8ab0b0cc8@[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