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

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

Ken MacDonald wrote on 19.11.2009 17:55:
> 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?

You should create the column with the datatype serial

Thomas

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

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
>

Ken MacDonald wrote on 19.11.2009 19:59:
> 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)'.

Which is essentially what "serial" is: just a shorthand for the above construct

Thomas

This could be due to the sequence being out of sync with the table itself.
Postgres uses the sequence to work out the id value to use for the new row.
The sequence should be the value of the id of the last row inserted in the
table. If the value of the sequence + 1 already exists in the table then you
will get this error. This all works by magic in Django provided the table
and the sequence match.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Noob-question-how-to-auto-increment-index-field-on-INSERT-tp2141875p3396818.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.