TutorialsArena

PostgreSQL: Creating Auto-Incrementing Columns (IDENTITY)

Simplify database design with auto-incrementing columns in PostgreSQL. This guide explains how to use the GENERATED ALWAYS/BY DEFAULT AS IDENTITY constraints.

Creating Auto-Incrementing Columns in PostgreSQL

Understanding PostgreSQL Identity Columns

PostgreSQL's `GENERATED ALWAYS AS IDENTITY` and `GENERATED BY DEFAULT AS IDENTITY` constraints provide a way to create columns that automatically generate unique integer values. This simplifies database design and is commonly used for primary keys. These constraints handle the creation and management of auto-incrementing sequences, eliminating the need for manual sequence management.

`GENERATED AS IDENTITY` Constraint Syntax

The syntax for creating an identity column is:

column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

Where:

  • column_name: The name of the column.
  • data_type: The data type of the column (e.g., `SMALLINT`, `INT`, `BIGINT`).
  • GENERATED { ALWAYS | BY DEFAULT }: Specifies whether a value is always generated (`ALWAYS`) or only when a value is not explicitly provided (`BY DEFAULT`).
  • sequence_options (optional): Allows customizing the sequence (e.g., `START WITH`, `INCREMENT BY`).

Example 1: `GENERATED ALWAYS AS IDENTITY`

This example creates a table with a `GENERATED ALWAYS AS IDENTITY` column. This means that the `Veggie_id` column will always have a value automatically generated by PostgreSQL; you cannot insert your own value into this column. Attempting to insert a value for the identity column will result in an error. The `OVERRIDING SYSTEM VALUE` clause can be used to override this behaviour and explicitly specify the value for the identity column.

1. Creating the Table

CREATE TABLE Statement

CREATE TABLE Vegetable (
    Veggie_id INT GENERATED ALWAYS AS IDENTITY,
    Veggie_name VARCHAR(255) NOT NULL
);

2. Inserting Data

INSERT INTO Statement

INSERT INTO Vegetable (Veggie_name) VALUES ('Sweet Potato');

3. Overriding the System Value

INSERT INTO (OVERRIDING SYSTEM VALUE)

INSERT INTO Vegetable (Veggie_id, Veggie_name) OVERRIDING SYSTEM VALUE VALUES (2, 'Carrot');

Example 2: `GENERATED BY DEFAULT AS IDENTITY`

This example uses `GENERATED BY DEFAULT AS IDENTITY`. This means that PostgreSQL generates a value for the column only if you don't explicitly provide a value during insertion. If you supply a value, PostgreSQL uses your value.

1. Recreate the Table

DROP TABLE and CREATE TABLE Statements

DROP TABLE Vegetable;
CREATE TABLE Vegetable (
    Veggie_id INT GENERATED BY DEFAULT AS IDENTITY,
    Veggie_name VARCHAR(255) NOT NULL
);

2. Inserting Data (with and without ID)

INSERT INTO Statements

INSERT INTO Vegetable (Veggie_name) VALUES ('Onion');
INSERT INTO Vegetable (Veggie_id, Veggie_name) VALUES (2, 'Cabbage');

Sequence Options

You can customize the sequence used for generating identity column values. For example:

CREATE TABLE with Sequence Options

CREATE TABLE Vegetable (
    Veggie_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 5 INCREMENT BY 5),
    Veggie_name VARCHAR(255) NOT NULL
);

This creates a `Vegetable` table where the `Veggie_id` starts at 5 and increments by 5 for each new row.

Adding an Identity Column to an Existing Table

You can add an identity column to an existing table using the `ALTER TABLE` command.

ALTER TABLE Syntax (Adding Identity Column)

ALTER TABLE table_name ADD COLUMN column_name INT GENERATED ALWAYS AS IDENTITY;

This adds a new column named `column_name` with an auto-incrementing sequence.

Conclusion

PostgreSQL's `GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY` constraint offers a straightforward method for creating auto-incrementing columns. This simplifies database design, enhances data management, and improves the overall efficiency of database operations.