Auto increment table column

Posted on by mtoplistChuyên mục:Top List

Postgres 10 or later

(serial columns remain unchanged, see below.)

Consider a standard-SQL IDENTITY column. Can be GENERATED BY DEFAULT or (stricter) GENERATED ALWAYS.
Basics in the manual for CREATE TABLE.
Details in this blog entry by its principal author Peter Eisentraut.

Create table with IDENTITY column

CREATE TABLE staff (
  staff_id int GENERATED ALWAYS 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 ALWAYS 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 ALWAYS AS IDENTITY PRIMARY KEY;

See:

Replace serial with IDENTITY column

See:

You can override system values or user input in INSERT commands with OVERRIDING {SYSTEM|USER} VALUE.

Postgres 9.6 or older

(Still supported in newer versions, too.)
Use the serial pseudo data type:

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 use legal, lower-case, unquoted identifiers in my examples. Makes your life with Postgres easier.

mtoplist