Thread: Auto-increment not really working
Hi, I am a bit puzzed about Postgres' auto-incrementing. Supposing I have the following table: CREATE TABLE foo ( id serial primary key, name varchar(20)); Then, I can do the following: INSERT INTO foo VALUES ('Test'); INSERT INTO foo VALUES ('Test 2'); ... The id for each name inserted should be 1, 2, 3, and so on. This works for me on small tables. However, earlier, I tried to insert about 9000 records through a Perl script and Postgres kept complaining: Cannot insert a duplicate key into unique index schedule_pkey And therefore Postgres didn't insert all the values. After searching around I fixed this by: INSERT INTO foo VALUES (nextval('schedule_id_key'),'Name'); This fixed the problem. Why does this happen on large tables? I am nervous now because I have used the insert calls without a nextval for small tables and things worked fine, but now I am worried.... Is there any other method for this? Thank you Ogden Nefix
On Tue, 29 Jul 2003, fixed wrote: > I am a bit puzzed about Postgres' auto-incrementing. > > Supposing I have the following table: > > CREATE TABLE foo ( > id serial primary key, > name varchar(20)); > > Then, I can do the following: > > INSERT INTO foo VALUES ('Test'); > INSERT INTO foo VALUES ('Test 2'); I assume you mean INSERT INTO foo (name) VALUES ('Test'); because those statements should fail with an error trying to convert 'Test' and 'Test2' into an integer. > The id for each name inserted should be 1, 2, 3, and so on. > > This works for me on small tables. However, earlier, I tried to insert > about 9000 records through a Perl script and Postgres kept complaining: > > Cannot insert a duplicate key into unique index schedule_pkey I've only seen this if you're trying to insert values into the serial column manually like if you leave out the column list or if you've inserted values in the past and not set the sequence past those values.