Skip site navigation (1) Skip section navigation (2)

Re: nextval() clarification

From: Ron St-Pierre <rstpierre(at)syscor(dot)com>
To: Greg Donald <destiney(at)gmail(dot)com>,pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: nextval() clarification
Date: 2004-09-13 20:41:15
Message-ID: 414605EB.40300@syscor.com (view raw or flat)
Thread:
Lists: pgsql-novice
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



In response to

pgsql-novice by date

Next:From: Greg DonaldDate: 2004-09-13 20:55:41
Subject: Re: nextval() clarification
Previous:From: Aarni RuuhimäkiDate: 2004-09-13 20:05:13
Subject: Re: nextval() clarification

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group