PostgreSQL - AUTO INCREMENT

The AUTO INCREMENT feature allows to create a unique numerical value for each additional record inserted into a table. Generally, it is used to create the numerical primary key field.

PostgreSQL AUTO INCREMENT Statement

In PostgreSQL, the auto-incrementing integer datatypes can be used to achieve an auto-increment feature. These datatypes are:

Auto-incrementing integer data types

For example - the below mentioned query creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which auto-increment is applied on column EmpID.

CREATE TABLE Employee (
  EmpID SERIAL NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  PRIMARY KEY(EmpID)
);

Inserting Records

While inserting a new record in the table discussed above, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

The statement mentioned above will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.