Thread: sequence id

Hello all,

I am learning PostgreSQL and I found it really interesting. I am writing
some sql scripts to create some
tables and then add data via INSERT.

A question about SERIAL Data type. If I have a table definition like:



CREATE TABLE www_server (
        id serial NOT NULL,
        role char(5),
        webserver varchar(25),
        ip varchar(12),
        hardware varchar(80),
        software varchar(80),
        checked DATE
         );

And have an INSERT statement like:

INSERT INTO www_server
    (role,webserver,ip,hardware,software,checked) VALUES
        (
        'www',
        'webserver1',
        '',
        'Pentium II, 256 MBRAM',
        'RedHat 7.3',
        '2002-07-16'
        );


my id which is a SERIAL will be incremented ok. But lets say I make
mistakes and want to delete from www_server everything. The next id
available will be which one was left previously. So If I run the INSERT
once I get 1 for my 'id'. If I delete and re-run my INSERT my 'id' is 2.

Do I need to do a VACUUM or how to restart from 1 ? I tried VACUUM; but
nothing worked. What worked was to DROP SEQUENCE and then the TABLE.

On the other hand is somehow reservated under PostgreSQL the name 'ID' for
column ?
If I create a table with ID works fine but selects are not working. If I
have 'id' for the column I can do SELECTs.

thanks
stefan

On Wed, Jul 17, 2002 at 01:14:10PM +0300, stefan@extum.com wrote:
> my id which is a SERIAL will be incremented ok. But lets say I make
> mistakes and want to delete from www_server everything. The next id
> available will be which one was left previously. So If I run the INSERT
> once I get 1 for my 'id'. If I delete and re-run my INSERT my 'id' is 2.
>
> Do I need to do a VACUUM or how to restart from 1 ? I tried VACUUM; but
> nothing worked. What worked was to DROP SEQUENCE and then the TABLE.

You can use setval() to reset the sequence. In general, sequences are
not intended to be anything more than unique, sequential numeric
identifiers -- assuming that there are no "holes" in the sequence or
that the generated numbers have any meaning in themselves is usually a
faulty assumption.

> On the other hand is somehow reservated under PostgreSQL the name 'ID' for
> column ?
> If I create a table with ID works fine but selects are not working. If I
> have 'id' for the column I can do SELECTs.

You'll need to enclose it in double-quotes.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Thanks for comment. I am reading about setval() now.

Stefan

On Tue, 16 Jul 2002, Neil Conway wrote:

> On Wed, Jul 17, 2002 at 01:14:10PM +0300, stefan@extum.com wrote:
> > my id which is a SERIAL will be incremented ok. But lets say I make
> > mistakes and want to delete from www_server everything. The next id
> > available will be which one was left previously. So If I run the INSERT
> > once I get 1 for my 'id'. If I delete and re-run my INSERT my 'id' is 2.
> >
> > Do I need to do a VACUUM or how to restart from 1 ? I tried VACUUM; but
> > nothing worked. What worked was to DROP SEQUENCE and then the TABLE.
>
> You can use setval() to reset the sequence. In general, sequences are
> not intended to be anything more than unique, sequential numeric
> identifiers -- assuming that there are no "holes" in the sequence or
> that the generated numbers have any meaning in themselves is usually a
> faulty assumption.
>
> > On the other hand is somehow reservated under PostgreSQL the name 'ID' for
> > column ?
> > If I create a table with ID works fine but selects are not working. If I
> > have 'id' for the column I can do SELECTs.
>
> You'll need to enclose it in double-quotes.
>
> Cheers,
>
> Neil
>
>