Auto increment table column

Jimmy picture Jimmy · Mar 26, 2012 · Viewed 52.1k times · Source

Using Postgres, I'm trying to use AUTO_INCREMENT to number my primary key automatically in SQL. However, it gives me an error.

CREATE TABLE Staff   (
  ID        INTEGER NOT NULL AUTO_INCREMENT,
  Name      VARCHAR(40) NOT NULL,
  PRIMARY KEY (ID)
);

The error:

********** Error **********
ERROR: syntax error at or near "AUTO_INCREMENT"
SQL state: 42601
Character: 63

Any idea why?

Answer

Erwin Brandstetter picture Erwin Brandstetter · Mar 26, 2012

Postgres 10 or later

serial columns (see below) remain unchanged. But consider an IDENTITY column. Postgres 10 implements this standard-SQL feature.

Basic syntax and info in the manual for CREATE TABLE.
Detailed explanation in this blog entry of its primary author Peter Eisentraut.

Create table with IDENTITY column

CREATE TABLE staff (
   staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , staff    text NOT NULL
);

Add IDENTITY column to existing table

Table may or may not be populated with rows.

ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY;

To also make it the PK at the same time (table can't have a PK yet):

ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;

Related:

Replace serial with IDENTITY column

See:

Postgres 9.6 or older

(Or any version, really.)
Use the serial pseudo data type instead:

CREATE TABLE staff (
   staff_id serial PRIMARY KEY,
 , staff    text NOT NULL
);

It creates and attaches the sequence object automatically and sets the DEFAULT to nextval() from the sequence. It does all you need.

I used lower case identifiers in my example. Makes your life with Postgres easier.