Обсуждение: 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.