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

Search Discussions

  • Thomas Kellerer at Nov 19, 2009 at 5:07 pm

    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
  • APseudoUtopia at Nov 19, 2009 at 5:19 pm

    On Thu, Nov 19, 2009 at 11:55 AM, Ken MacDonald 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 at Nov 19, 2009 at 6:59 pm
    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 wrote:
    On Thu, Nov 19, 2009 at 11:55 AM, Ken MacDonald 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
  • Thomas Kellerer at Nov 19, 2009 at 7:15 pm

    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
  • Davemac at Feb 23, 2011 at 10:43 am
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 19, '09 at 5:02p
activeFeb 23, '11 at 10:43a
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase