pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Autoincrement
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