Thread: Automatic increment
Hi-- Some of you guys suggested to use the 'SERIAL' type for a field that needs to be AUTOMATICALLY INCREASED BY 1 by PGSQL without user intervention, but it doesn't seem to work. What am I doing wrong? Julio Cuz, Jr. Riverside Community College jcuz@rccd.cc.ca.us
Julio Cuz, Jr. (jcuz@rccd.cc.ca.us) wrote:
> Some of you guys suggested to use the 'SERIAL' type for a field that needs
> to be AUTOMATICALLY INCREASED BY 1 by PGSQL without user intervention, but
> it doesn't seem to work. What am I doing wrong?
Though I wasn't here for that, here's how I normally declare
automatically-incrementing values:
CREATE SEQUENCE foo_id;
CREATE TABLE foo (
foo_id integer not null primary key default nextval(foo_id);
...
);
insertions to foo get their own unique ID. With the php extension, you
can get the oid of the row that was inserted and go get the ID if you
happen to need it.
On Thu, Jan 11, 2001 at 07:09:08PM -0800, some SMTP stream spewed forth:
> Hi--
>
> Some of you guys suggested to use the 'SERIAL' type for a field that needs
> to be AUTOMATICALLY INCREASED BY 1 by PGSQL without user intervention, but
> it doesn't seem to work. What am I doing wrong?
I know obviously not what query you are using, but here is an example:
create table example (col1 serial col2 <whatever>);
insert into example (col2) values ('whatever');
select * from example;
example
col1 | col2
-----+------------
1 | whatever
The initial value of a serial column is "1".
gh
>
> Julio Cuz, Jr.
> Riverside Community College
> jcuz@rccd.cc.ca.us
>
On Thu, Jan 11, 2001 at 10:22:05PM -0500, some SMTP stream spewed forth:
> Julio Cuz, Jr. (jcuz@rccd.cc.ca.us) wrote:
> > Some of you guys suggested to use the 'SERIAL' type for a field that needs
> > to be AUTOMATICALLY INCREASED BY 1 by PGSQL without user intervention, but
> > it doesn't seem to work. What am I doing wrong?
>
> Though I wasn't here for that, here's how I normally declare
> automatically-incrementing values:
>
> CREATE SEQUENCE foo_id;
>
> CREATE TABLE foo (
> foo_id integer not null primary key default nextval(foo_id);
>
> ...
> );
>
> insertions to foo get their own unique ID. With the php extension, you
> can get the oid of the row that was inserted and go get the ID if you
> happen to need it.
I believe that it is commonly preferred to do:
select nextval('sequence');
insert into foo (foo_id,...) values (<nextval>, ...);
That is, select the nextval() first, then use that value in the insert.
This avoids numerous issues (like searching through the table, even
by oid) which would be covered in the archives of this list.
gh
>
GH (grasshacker@over-yonder.net) wrote:
> I believe that it is commonly preferred to do:
> select nextval('sequence');
> insert into foo (foo_id,...) values (<nextval>, ...);
Many people do the first SELECT on a form, stuff the result in a hidden
variable, then do the INSERT in the form's handler. This prevents the
double- (or triple-) clicking of "submit" resulting in extra database
inserts.
On Thu, Jan 11, 2001 at 10:41:35PM -0500, some SMTP stream spewed forth:
> GH (grasshacker@over-yonder.net) wrote:
> > I believe that it is commonly preferred to do:
> > select nextval('sequence');
> > insert into foo (foo_id,...) values (<nextval>, ...);
>
>
> Many people do the first SELECT on a form, stuff the result in a hidden
> variable, then do the INSERT in the form's handler. This prevents the
> double- (or triple-) clicking of "submit" resulting in extra database
> inserts.
My humble appreciation to you for bringing to my attention that which I
(unbelievably - to me, anyway ;-) ) overlooked.
*bow out into the darkness of the wild Internet yonder*
gh