Auto increment table column
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.
You do not specify which RDBMS you are using, however, in SQL Server you can use this syntax:
CREATE TABLE [dbo].[Staff]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] VARCHAR(40) NOT NULL,CONSTRAINT [ID] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
In the SQL server database you can use Identity(1,1)
like this:
CREATE TABLE Staff( ID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(40) NOT NULL, PRIMARY KEY (ID));