pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedAutoincrement
5+ messages / 5 participants
[nested] [flat]
* Autoincrement
@ 1998-07-17 06:59 Wari Wahab <wari@technology.com>
1998-07-17 09:47 ` Re: [INTERFACES] Autoincrement Antonio Garcia Mari <agarcia@at4.net>
1998-07-17 12:27 ` Re: [INTERFACES] Autoincrement James Olin Oden <joden@Lee.k12.nc.us>
0 siblings, 2 replies; 5+ messages in thread
From: Wari Wahab @ 1998-07-17 06:59 UTC (permalink / raw)
To: pgsql-interfaces
Hi, is there a way to do a field that can automatically increment itself,
like in paradox or dbase?
________________________________________
Regards
Wari Wahab
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Autoincrement
1998-07-17 06:59 Autoincrement Wari Wahab <wari@technology.com>
@ 1998-07-17 09:47 ` Antonio Garcia Mari <agarcia@at4.net>
1998-07-17 11:07 ` Re: [INTERFACES] Autoincrement Federico Passaro <fede@link.it>
1998-07-19 10:40 ` Re: Autoincrement Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 2 replies; 5+ messages in thread
From: Antonio Garcia Mari @ 1998-07-17 09:47 UTC (permalink / raw)
To: Wari Wahab <wari@technology.com>; pgsql-interfaces
> 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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Autoincrement
1998-07-17 06:59 Autoincrement Wari Wahab <wari@technology.com>
1998-07-17 09:47 ` Re: [INTERFACES] Autoincrement Antonio Garcia Mari <agarcia@at4.net>
@ 1998-07-17 11:07 ` Federico Passaro <fede@link.it>
1 sibling, 0 replies; 5+ messages in thread
From: Federico Passaro @ 1998-07-17 11:07 UTC (permalink / raw)
To: pgsql-interfaces; Wari Wahab <wari@technology.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Autoincrement
1998-07-17 06:59 Autoincrement Wari Wahab <wari@technology.com>
1998-07-17 09:47 ` Re: [INTERFACES] Autoincrement Antonio Garcia Mari <agarcia@at4.net>
@ 1998-07-19 10:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 5+ messages in thread
From: Herouth Maoz @ 1998-07-19 10:40 UTC (permalink / raw)
To: Federico Passaro <fede@link.it>; wari@technology.com; +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
> <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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Autoincrement
1998-07-17 06:59 Autoincrement Wari Wahab <wari@technology.com>
@ 1998-07-17 12:27 ` James Olin Oden <joden@Lee.k12.nc.us>
1 sibling, 0 replies; 5+ messages in thread
From: James Olin Oden @ 1998-07-17 12:27 UTC (permalink / raw)
To: Wari Wahab <wari@technology.com>; +Cc: pgsql-interfaces
> Hi, is there a way to do a field that can automatically increment itself,
> like in paradox or dbase?
>
>
Read the man page on create_sequence. This will tell you how its done in
Postgres...james
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1998-07-19 10:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-17 06:59 Autoincrement Wari Wahab <wari@technology.com>
1998-07-17 09:47 ` Antonio Garcia Mari <agarcia@at4.net>
1998-07-17 11:07 ` Federico Passaro <fede@link.it>
1998-07-19 10:40 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-07-17 12:27 ` James Olin Oden <joden@Lee.k12.nc.us>
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