Thread: nextval() clarification
Long time MySQL user, six weeks into Postgres here.
I'm converting a PHP script that uses MySQL to use Postgres instead.
To substitue the missing MySQL functionaility with mysql_insert_id()
and auto_increment I am using code like this:
$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.
TIA..
--
Greg Donald
http://destiney.com/
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@puroga.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.
On Mon, 2004-09-13 at 19:59, Greg Donald wrote:
> Long time MySQL user, six weeks into Postgres here.
>
> I'm converting a PHP script that uses MySQL to use Postgres instead.
> To substitue the missing MySQL functionaility with mysql_insert_id()
> and auto_increment I am using code like this:
>
> $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 get the impression that you probably know this, but, just in case:
every time you use nextval(), the sequence is incremented. If you
insert a record with a null value for the SERIAL field, nextval() will
be used to generate a value; if you use it again to find a value it will
increment again. Once nextval() has been used once, whether by default
or directly, you should then use currval() to get the value just
returned for the sequence. You cannot use currval() until after
nextval() has been used in the same session.
I can't see from what you describe how you are managing to run nextval()
twice, but that must be happening somehow. Perhaps you are using $sql
twice, in which case it will increment the sequence each time ??
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"For my thoughts are not your thoughts, neither are
your ways my ways, saith the LORD. For as the heavens
are higher than the earth, so are my ways higher than
your ways, and my thoughts than your thoughts."
Isaiah 55:8,9
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.
* Greg Donald <destiney@gmail.com> [130904, 13:59]:
> Long time MySQL user, six weeks into Postgres here.
>
> I'm converting a PHP script that uses MySQL to use Postgres instead.
> To substitue the missing MySQL functionaility with mysql_insert_id()
> and auto_increment I am using code like this:
>
> $sql = "SELECT nextval('companies_company_id_seq'::text)";
>
> And then I do my insert with that value.
> [ cut ]
I think you should put <nextval(....)> directly into the INSERT
instruction, so that it advances only once per added record.
Have a look at Documentation PostgreSQL 7.4, Ch 9.11, table 9.34.
HTH
Regards, Ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]
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.
Greg Donald wrote:
>Long time MySQL user, six weeks into Postgres here.
>
>I'm converting a PHP script that uses MySQL to use Postgres instead.
>To substitue the missing MySQL functionaility with mysql_insert_id()
>and auto_increment I am using code like this:
>
>$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.
>
>TIA..
>
>
>
As others have mentioned, you are basically calling the nextval
operation twice. If the field you
are inserting data into is of type serial, whenever a record/row is
inserted, the nextval is inserted.
eg
CREATE TABLE testSeq (
id serial,
name text
);
INSERT INTO testSeq (name) VALUES ('foo');
testdb=# SELECT * FROM testSeq;
id | name
----+------
1 | foo
See, it automatically increments the value.
then INSERT INTO testSeq (name) VALUES ( 'fooBar');
and then if you re-increment the value yourself:
testdb=# SELECT nextval('public.testseq_id_seq'::text);
nextval
---------
3
and then insert another row:
INSERT INTO testSeq (name) VALUES ('bar');
testdb=# SELECT * FROM testSeq;
id | name
----+--------
1 | foo
2 | fooBar
4 | bar
(3 rows)
bar is now 4, because you manually called nextval().
hth
Ron
On Mon, 13 Sep 2004 21:54:11 +0200, Ennio-Sr <nasr.laili@tin.it> wrote: > I think you should put <nextval(....)> directly into the INSERT > instruction, so that it advances only once per added record. > Have a look at Documentation PostgreSQL 7.4, Ch 9.11, table 9.34. > HTH I would but then I don't know how to ensure I return that exact same id from the insert, like mysql_insert_id() does. That's the whole reason why I aquire the id first, so I know what it is after the insert. SELECT max(id) would be off under load I think. I just didn't understand why it appeared to be +2 and not +1 as a novice might expect. But now I get it. Thanks to all. -- Greg Donald http://destiney.com/
Greg Donald wrote:
>On Mon, 13 Sep 2004 21:54:11 +0200, Ennio-Sr <nasr.laili@tin.it> wrote:
>
>
>>I think you should put <nextval(....)> directly into the INSERT
>>instruction, so that it advances only once per added record.
>>Have a look at Documentation PostgreSQL 7.4, Ch 9.11, table 9.34.
>>HTH
>>
>>
>
>I would but then I don't know how to ensure I return that exact same
>id from the insert, like mysql_insert_id() does. That's the whole
>reason why I aquire the id first, so I know what it is after the
>insert. SELECT max(id) would be off under load I think.
>
>I just didn't understand why it appeared to be +2 and not +1 as a
>novice might expect. But now I get it. Thanks to all.
>
>
>
After your insert statement, you can call CURRVAL to find out what the
value was that you
inserted into the serial column.
eg SELECT currval('companies_company_id_seq'::text)
Ron
On Mon, Sep 13, 2004 at 03:55:41PM -0500, Greg Donald wrote:
> On Mon, 13 Sep 2004 21:54:11 +0200, Ennio-Sr <nasr.laili@tin.it> wrote:
> > I think you should put <nextval(....)> directly into the INSERT
> > instruction, so that it advances only once per added record.
> > Have a look at Documentation PostgreSQL 7.4, Ch 9.11, table 9.34.
> > HTH
>
> I would but then I don't know how to ensure I return that exact same
> id from the insert, like mysql_insert_id() does. That's the whole
> reason why I aquire the id first, so I know what it is after the
> insert. SELECT max(id) would be off under load I think.
Are you aware of CURRVAL()?
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
info TEXT NOT NULL
);
INSERT INTO foo (info) VALUES ('First Item');
INSERT INTO foo (info) VALUES ('Second Item');
SELECT CURRVAL('foo_id_seq');
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/