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: (view raw, whole thread or download thread mbox)
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.
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.
  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);

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().


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-2017 The PostgreSQL Global Development Group