pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAutoincrement
11+ messages / 9 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; 11+ messages in thread
From: Wari Wahab @ 1998-07-17 06:59 UTC (permalink / raw)
To: Postgres Interfaces Mailing List <pgsql-interfaces@postgreSQL.org>
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] 11+ 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; 11+ messages in thread
From: Antonio Garcia Mari @ 1998-07-17 09:47 UTC (permalink / raw)
To: Wari Wahab <wari@technology.com>; pgsql-interfaces@postgreSQL.org
> 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] 11+ 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; 11+ messages in thread
From: Federico Passaro @ 1998-07-17 11:07 UTC (permalink / raw)
To: PostGreSQL Interface <pgsql-interfaces@postgreSQL.org>; 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] 11+ 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; 11+ 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
(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] 11+ 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; 11+ messages in thread
From: James Olin Oden @ 1998-07-17 12:27 UTC (permalink / raw)
To: Wari Wahab <wari@technology.com>; +Cc: Postgres Interfaces Mailing List <pgsql-interfaces@postgresql.org>
> 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] 11+ messages in thread
* Re: Autoincrement
@ 1998-07-20 08:06 Federico Passaro <fede@link.it>
1998-07-20 09:23 ` Re: [SQL] Re: Autoincrement Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 11+ messages in thread
From: Federico Passaro @ 1998-07-20 08:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Re: Autoincrement
1998-07-20 08:06 Re: Autoincrement Federico Passaro <fede@link.it>
@ 1998-07-20 09:23 ` Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 11+ messages in thread
From: Herouth Maoz @ 1998-07-20 09:23 UTC (permalink / raw)
To: Federico Passaro <fede@link.it>; +Cc: pgsql-sql
At 11:06 +0300 on 20/7/98, Federico Passaro wrote:
> When I have to pay this penalty: on insert / update / query ?
Query, I think.
> > > 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 ....
As far as I understand, the autoinc trigger increments the value of the
field on both insert and update. If you want to do it only for insert
(which is what you have to do if the field is to be used as a key), you
have to change the code. That opens a can of bugs.
Besides, the warning is general. Things that appear in the contrib
directory are not actually guaranteed to be up to date - are they? The
contributors may not submit updated versions if there are any future
versions of Postgres. Thus, you may remain without a function.
The funny thing is that the autoinc trigger actually relies on the builtin
sequences, thus it is almost the same, but you rely on external code for
your, and do a lot of work you didn't want to do (create function, create
trigger).
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: [SQL] Re: Autoincrement
@ 1998-07-22 20:51 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jackson, DeJuan @ 1998-07-22 20:51 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; Federico Passaro <fede@link.it>; +Cc: pgsql-sql
> At 11:06 +0300 on 20/7/98, Federico Passaro wrote:
>
>
> > When I have to pay this penalty: on insert / update / query ?
>
> Query, I think.
>
I would think you would have to pay this penalty for all accesses to
that column. Meaning in any WHERE|ORDER BY|GROUP BY clause that the
column is accessed. Which could be for INSERT, UPDATE, and SELECT,
DELETE.
INSERT because if indexes
UPDATE because of WHERE, if not indexed
SELECT because of WHERE/ORDER BY/GROUP BY
DELETE because of WHERE, if not indexed
-DEJ
^ permalink raw reply [nested|flat] 11+ messages in thread
* autoincrement
@ 2001-10-25 20:09 Alexey Prohorenko <green@usa.dp.ua>
2001-10-25 21:11 ` Re: autoincrement Bruce Momjian <pgman@candle.pha.pa.us>
2001-10-25 21:22 ` Re: autoincrement Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 2 replies; 11+ messages in thread
From: Alexey Prohorenko @ 2001-10-25 20:09 UTC (permalink / raw)
To: pgsql-sql
How can I reproduce in PostgreSQL type structure from MySQL:
INT NOT NULL PRIMARY KEY AUTO_INCREMENT
?
To be more exact -- in two words: "how can I create autoincrement
field?"
Thanks a lot.
--
green
[http://www.extrasy.net]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoincrement
2001-10-25 20:09 autoincrement Alexey Prohorenko <green@usa.dp.ua>
@ 2001-10-25 21:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2001-10-25 21:11 UTC (permalink / raw)
To: Alexey Prohorenko <green@usa.dp.ua>; +Cc: pgsql-sql
Use SERIAL instead of AUTO_INCREMENT.
> How can I reproduce in PostgreSQL type structure from MySQL:
> INT NOT NULL PRIMARY KEY AUTO_INCREMENT
>
> ?
>
> To be more exact -- in two words: "how can I create autoincrement
> field?"
>
> Thanks a lot.
>
> --
> green
> [http://www.extrasy.net]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: autoincrement
2001-10-25 20:09 autoincrement Alexey Prohorenko <green@usa.dp.ua>
@ 2001-10-25 21:22 ` Aasmund Midttun Godal <postgresql@envisity.com>
1 sibling, 0 replies; 11+ messages in thread
From: Aasmund Midttun Godal @ 2001-10-25 21:22 UTC (permalink / raw)
To: green@usa.dp.ua; +Cc: pgsql-sql
SERIAL type (look it up in the docs).
Regards,
Aasmund
PS. I may not be good at counting, but I have counted your two words 3 times and I still get 6.
On Thu, 25 Oct 2001 23:09:32 +0300, Alexey Prohorenko <green@usa.dp.ua> wrote:
> How can I reproduce in PostgreSQL type structure from MySQL:
> INT NOT NULL PRIMARY KEY AUTO_INCREMENT
>
> ?
>
> To be more exact -- in two words: "how can I create autoincrement
> field?"
>
> Thanks a lot.
>
> --
> green
> [http://www.extrasy.net]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Aasmund Midttun Godal
aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2001-10-25 21:22 UTC | newest]
Thread overview: 11+ 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>
1998-07-20 08:06 Re: Autoincrement Federico Passaro <fede@link.it>
1998-07-20 09:23 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-07-22 20:51 RE: [SQL] Re: Autoincrement Jackson, DeJuan <djackson@cpsgroup.com>
2001-10-25 20:09 autoincrement Alexey Prohorenko <green@usa.dp.ua>
2001-10-25 21:11 ` Re: autoincrement Bruce Momjian <pgman@candle.pha.pa.us>
2001-10-25 21:22 ` Re: autoincrement Aasmund Midttun Godal <postgresql@envisity.com>
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