Re: nextval() clarification

Hi,

You don't have to select nextval separately. Just do an INSERT without the id
column (presuming it is declared as SERIAL when created) like:

INSERT INTO companies (company_name, company_whatever ) VALUES ( , );

Auto increment is indeed in a separate table that stores the next number in
the sequence, not the last one used. And yes, this will increase 'forever' no
matter how many rows there are or have been in your table.

If you want to reset or otherwise meddle with the sequence use:

>>
You need to do something like

select setval('seq-name', (select max(col) + 1 from table));
>>

BR,

Aarni

On Monday 13 September 2004 22:08, you wrote:
> 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

--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com |
Pääsintie 26 | 45100 Kouvola | FINLAND |
www.kymi.com | cfm.kymi.com |
aarni(at)kymi(dot)com | info(at)kymi(dot)com |
+358-5-3755 035 | +358-50-4910 037
-------------------------------------------------
This is a bugfree broadcast to you from a linux system.