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@gmail.com> wrote:
On Thu, Nov 19, 2009 at 11:55 AM, Ken MacDonald <
drken567@gmail.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
>
"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
