Re: nextval() clarification

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(at)lfix(dot)co(dot)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