CODEWITHSUNDEEP
sqlpostgresql
WildDev
WildDev

Reputation: 2367

PostgreSQL 9.1 primary key autoincrement

I'm trying to add a primary key with an autoincrement.

I have read some docs and other questions - there're SERIAL and nextval() statements but neither of them work.

Here's what I made:

CREATE TABLE IF NOT EXISTS "category" (
  "id" integer SERIAL PRIMARY KEY,
  "name" varchar(30) DEFAULT NULL
); // the error near "SERIAL"

and

CREATE SEQUENCE your_seq;
CREATE TABLE IF NOT EXISTS "category" (
  "id" integer PRIMARY KEY nextval('your_seq'),
  "name" varchar(30) DEFAULT NULL
); // the error near 'nextval'

What am I doing wrong? Is there a way to increment the primary key by 1.

Upvotes: 37

Views: 92298

Answers (2)

Valentin Podkamennyi
Valentin Podkamennyi

Reputation: 7359

If someone needs to modify an existing table to add a primary key with auto-increment:

ALTER TABLE table_name ADD COLUMN pk_column_name SERIAL PRIMARY KEY;

Upvotes: 5

mu is too short
mu is too short

Reputation: 434665

serial is, more or less, a column type so saying integer serial is like saying text text, just say serial:

CREATE TABLE IF NOT EXISTS "category" (
  "id" SERIAL PRIMARY KEY,
  "name" varchar(30) DEFAULT NULL
);

If you want to create the sequence yourself then you want to make the default value of id the next value in the sequence and that means saying default nextval('your_seq'):

CREATE SEQUENCE your_seq;
CREATE TABLE IF NOT EXISTS "category" (
  "id" integer PRIMARY KEY default nextval('your_seq'),
  "name" varchar(30) DEFAULT NULL
);

To simulate the usual serial behavior you'll also want to make the sequence owned by the table:

alter sequence your_seq owned by category.id;

Reading the Serial Types section of the manual might be fruitful.

I'd also recommend that you don't double quote your table and column names unless you have to. PostgreSQL will fold your identifiers to lower case so id and "id" will be the same thing but unnecessary quoting is a bad habit that can easily lead to a big mess of quotes everywhere.

Upvotes: 95

Related Questions