Re: Auto-increment not really working

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.