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

Re: Noob question: how to auto-increment index field on INSERT?

From: Ken MacDonald <drken567(at)gmail(dot)com>
To: APseudoUtopia <apseudoutopia(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Noob question: how to auto-increment index field on INSERT?
Date: 2009-11-19 18:59:00
Message-ID: 3468cae10911191059i1fa3d31xa9f0f804c1678aed@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,
Thanks to all who replied so far. I agree that using the serial type would
be ideal, and in fact set up a couple test DB's that way. Unfortunately,
Django is auto-generating this field from its data models, and I don't seem
to have much/any control over its type.

Looking at pgadmin some more, it appears that column 'id' is set up with a
default value of 'nextval('tablename_id_seq'::regclass)'.

Then, 'tablename_id_seq' is a sequence, whose initial and current value is
'7' - which is about 100,000 less than the actual max('id') found in my
table. So it appears if I can coerce 'tablename_id_seq' to have a value >=
my current maxvalue for 'id', I can then:

INSERT INTO tablename (id, data) VALUES (nextval('tablename_id_seq'), 'foo')

without having to change the datatype on 'id' to serial, which would
probably get reset to integer the next time the DB is regenerated by Django.
I'll give this a try.

What I ended up doing:

First time thru the update/insert loop:
select setval('tablename_id_seq', (select max(id) from tablename))

which ensures that the sequence starts at the proper spot;

then for each INSERT:
INSERT INTO tablename (id, data) VALUES (nextval('tablename_id_seq'), 'foo')

Works great. Any other ways of solving this more elegantly (and without
using 'serial') welcome, of course! 'Serial' is, of course, a much nicer way
of handling this, IF you have the luxury to choose it.

I think I'll post this question to the Django mailing list also, as it's
more related to the Django auto-gen'd data types.
Thanks again,
Ken

On Thu, Nov 19, 2009 at 12:19 PM, APseudoUtopia <apseudoutopia(at)gmail(dot)com>wrote:

> On Thu, Nov 19, 2009 at 11:55 AM, Ken MacDonald <drken567(at)gmail(dot)com>
> wrote:
> > Hi,
> > I have a PostgreSQL DB created by a Django model, with a field 'id' that
> is
> > automatically created by Django as a primary key, type integer.
> >
> > I would like to create a new row by doing something like....
> >
> > INSERT INTO table (a, b, c, d) VALUES ('aa', 'bb', 'cc', 'dd')
> >
> > where I've been hoping that 'id' would get the next value of id
> available.
> > Unfortunately, instead I get a 'duplicate primary key' error saying that
> > 'id' is a duplicate, even though I'm not specifying it explicitly in the
> > INSERT. What is the proper way to auto-increment a primary key?
> > Thanks!
> > Ken
> >
>
> CREATE TABLE "table" (
> "id"  SERIAL PRIMARY KEY,  -- This is the auto-incrementing table, see
> the "SERIAL" datatype in the docs
> "data" TEXT NOT NULL
> );
>
>
> To insert, use the DEFAULT keyword.
> INSERT INTO "table" ("id", "data") VALUES (DEFAULT, 'abc 123');
>
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL
>

In response to

Responses

pgsql-novice by date

Next:From: Thomas KellererDate: 2009-11-19 19:14:27
Subject: Re: Noob question: how to auto-increment index field on INSERT?
Previous:From: APseudoUtopiaDate: 2009-11-19 17:19:23
Subject: Re: Noob question: how to auto-increment index field on INSERT?

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