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

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

From: mgalvin(at)nycap(dot)rr(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: Serial id not incrementing when manual ids are used, User Error? Bug?
Date: 2004-11-29 20:47:27
Message-ID: 112aab91123d2d.1123d2d112aab9@nyroc.rr.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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 issues with 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 val in 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 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 )?

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

Matt


Responses

pgsql-bugs by date

Next:From: Michael OwensDate: 2004-11-29 23:01:53
Subject: 8.0 Beta-5 Linux PSQL Endless loop
Previous:From: Tom LaneDate: 2004-11-29 19:34:05
Subject: Re: PLTCL

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