Thread: Serial id not incrementing when manual ids are used, User Error? Bug?

Hello Everyone,

    My name is Matt, this is my first post to the list. I've been using post for a while and love it. I haven't had any
issueswith it and i have found it to be a great and very flexible system.  

That said... I looked through the archives a bit but could not find what i was looking for so here goes:

It seems that when i manually insert an id into a serial id column the sequence doesn't get updated so that when i:

    CREATE TABLE table1 (
        table1_id SERIAL,
        name      VARCHAR( 255 ),
        PRIMARY KEY( table1_id )
    );

    // Manually set id during a migration process
    INSERT INTO table1( table1_id, name ) VALUES( 1, 'test' );

the id 1 has now been used, er, should be used. Then when i:

    // Let post use seq to set id when inserting brand new record
    INSERT INTO table1( name ) VALUE( 'Matt' );

post tries to use the id 1 "again" and the insert fails. Shouldn't post know that i already used 1 and  use the next
valin the sequence, in this case post should really say, hey 1 is used i will set the id to 2. 

I should then end up with 2 records

table1_id | name
----------------
        1 | test
        2 | Matt

So does post not update a SERIAL column sequence when id's are manually entered? Would I have to manually do a nextval
onthe seq when manually setting id's? Should't post know that i used an id number and use the next availible number,
somethinglike ( (the largest id number in the key) + increment_val )? 

I can elaborate more if needed. Thanks in advance!!!

Matt

On Mon, Nov 29, 2004 at 03:47:27PM -0500, mgalvin@nycap.rr.com wrote:

> So does post not update a SERIAL column sequence when id's are
> manually entered? Would I have to manually do a nextval on the seq
> when manually setting id's? Should't post know that i used an id
> number and use the next availible number, something like ( (the
> largest id number in the key) + increment_val )?

SERIAL is just a convenient way of creating an INTEGER column with
a default value.  If you provide a value for that column then the
default isn't used, so the INSERT never calls nextval() to increment
the sequence.

If you need to set some of the SERIAL column's values explicitly,
e.g., when importing data, then you can use ALTER SEQUENCE or
setval() when you're finished to change the sequence's value.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/