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

Auto-increment not really working

From: fixed <onefix(at)waste(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Auto-increment not really working
Date: 2003-07-30 01:36:03
Message-ID: Pine.LNX.4.56.0307292025250.2426@waste.org (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: btoberDate: 2003-07-30 02:35:00
Subject: Re: Does the block of code in a stored procedure execute as a transaction?
Previous:From: Bruce MomjianDate: 2003-07-30 01:20:48
Subject: Re: Using YY-MM-DD date input

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