PostgreSQL 9.1 primary key autoincrement

Question

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 it doesn't 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 do I wrong? I just want to increment the primary key by 1.

Answer 1

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.

Answer 2

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;