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

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