Re: nextval() clarification

On Tuesday 14 Sep 2004 12:29 am, Greg Donald wrote:
> $sql = "SELECT nextval('companies_company_id_seq'::text)";
>
> And then I do my insert with that value.
>
> It works fine but I don't understand why it always gives me a +1 value
> of what I'm expecting. For example when I have an empty table it
> gives me 2 as the first nextval instead of a 1, or if I have two rows
> in there already it gives me a 4. Is this by design or am I missing
> something? It's not a problem I just want to know what's going on.
>

I new to postgresql too. :)

Even in MySQL, the auto_increment value for a field in the table is kept
separately. I remember using phpmyadmin to change this value. This counter
will keep increasing no matter whether you table is shrinking or increasing.

Just guessing, that sequences in postgesql are kept as separate counters along
with the table, and will only keep on increasing. Postgresql will never check
how many records are there in the table while returning a vlue for this
sequence. Again, I am just guessing that this si teh behaviour. It doesnt
make sense for the databse to check all teh records to find out the value of
a simple counter.

- Sandip

--
Sandip Bhattacharya * Puroga Technologies * sandip(at)puroga(dot)com
Work: http://www.puroga.com * Home: http://www.sandipb.net

PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3

It's a naive, domestic operating system without any breeding, but I
think you'll be amused by its presumption.