Re: Automatic increment

On Thu, Jan 11, 2001 at 10:22:05PM -0500, some SMTP stream spewed forth:
> Julio Cuz, Jr. (jcuz(at)rccd(dot)cc(dot)ca(dot)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

>