Re: nextval() clarification - Mailing list pgsql-novice

Bah,

Just the other way around. Sequence storing the last value used.

dataguard=# SELECT * FROM langs_lang_id_seq;
   sequence_name   | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called

-------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 langs_lang_id_seq |         12 |            1 | 9223372036854775807 |
1 |           1 |       0 | f         | t
(1 row)



INSERT INTO lang(lang_name, lang_show, default_lang)
VALUES('#lang_name#', '#lang_show#', '#default_lang#')



dataguard=# SELECT * FROM langs_lang_id_seq;
   sequence_name   | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called

-------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 langs_lang_id_seq |         13 |            1 | 9223372036854775807 |
1 |           1 |      32 | f         | t
(1 row)


Sorry...,

Aarni

On Monday 13 September 2004 22:47, you wrote:
> 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@kymi.com | info@kymi.com |
+358-5-3755 035 | +358-50-4910 037
-------------------------------------------------
This is a bugfree broadcast to you from a linux system.