PostgreSQL: Create Identity Column in a Table

PostgreSQL supports the GENERATED AS IDENTITY clause to create an identity column. An identity column is an integer column in a table that is automatically populated with an integer value each time a row is inserted. 

In PostgreSQL, the identity column is a NOT NULL column that has an implicit sequence attached to it and the column in new rows will automatically have integer values from the sequence assigned to it.

PostgreSQL allows defining identity constraints on more than one column of a table.

Use GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY clause to create an identity column in CREATE TABLE or ALTER TABLE statement.

<column_name< type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[(sequence_option)]

The GENERATED ALWAYS clause instructs PostgreSQL to generate integer value for the identity column. You are not allowed to insert or update a value in a column defined with GENERATED ALWAYS AS IDENTITY clause. If you try to do so it will raise an error.

The GENERATED BY DEFAULT instructs PostgreSQL to generate an integer value for the identity column. However, it allows you to specify a value for insert or update, PostgreSQL will use your specified value and store it in the identity column instead of system generated value.

GENERATED ALWAYS

Let's create the CARS table with its car_id as the identity column

CREATE TABLE cars (
    car_id INT GENERATED ALWAYS AS IDENTITY,
    brand_name VARCHAR NOT NULL
);

The above statement would create the identity constraint in pgAdmin, as shown below.

Now try to insert data into the CARS table

INSERT INTO CARS(brand_name)
VALUES('Honda');

You can also add data by defining card_id as DEFAULT.

INSERT INTO CARS(car_id, brand_name)
VALUES(DEFAULT, 'Jeep');

The car_id column is has GENERATED ALWAYS AS IDENTITY constraint defined, PostgreSQL generate value as bellow

Now if we try to add data by defining your car_id instead of using system generated one like below raises an error that cannot insert a non-DEFAULT value to the car_id column.

To fix this error, you can use the OVERRIDING SYSTEM VALUE clause, as shown below

INSERT INTO CARS(car_id, brand_name)
OVERRIDING SYSTEM VALUE
VALUES(5, 'Tesla');

Let’s check the data now.

GENERATED BY DEFAULT

The GENERATED BY DEFAULT AS IDENTITY clause does the same thing as GENERATED ALWAYS AS IDENTITY, the only difference is that you can manually insert values for the identity column without overriding it, and no error would be raised.

The following creates an identity column using GENERATED BY DEFAULT AS IDENTITY.

CREATE TABLE cars (
    car_id INT GENERATED BY DEFAULT AS IDENTITY,
    brand_name VARCHAR NOT NULL
);

Now insert rows to the CARS table.

INSERT INTO CARS(brand_name)
VALUES('Honda');

INSERT INTO CARS(car_id, brand_name)
VALUES(DEFAULT, 'Jeep');

The above INSERT statements would insert the following rows.

Now, let’s try to insert the value for car_id manually instead of system generated, as shown below.

INSERT INTO CARS(car_id, brand_name)
VALUES(5, 'Tesla');

The above will insert a row without any error.

Thus, manually inserted values will have more precedence over automatically generated values.

Sequence for Identity Constraint

The GENERATED AS IDENTITY constraint internally uses the sequence object. It allows you to specify your sequence to be used for system-generated values.

The following creates the identity column car_id in the CARS table using the sequence option.

CREATE TABLE cars (
    car_id INT GENERATED BY DEFAULT AS IDENTITY
        (START WITH 10 INCREMENT BY 5),
    brand_name VARCHAR NOT NULL
);

Now insert rows into the CARS table.

INSERT INTO CARS(brand_name)
VALUES('Honda');

INSERT INTO CARS(car_id, brand_name)
VALUES(DEFAULT, 'Jeep');

As the car_id column is has GENERATED BY DEFAULT AS IDENTITY constraint defined along with user-specified sequence, PostgreSQL will use the specified sequence and generate car_id value starting from 10 and increment it by 5 as shown bellow

Modify Identity Constraint

The characteristics of the existing identity column can be changed by using ALTER TABLE statement.

The following will change the identity column from GENERATED ALWAYS to GENERATED BY DEFAULT.

ALTER TABLE CARS
ALTER COLUMN car_id
SET GENERATED BY DEFAULT;

Remove Identity Constraint

The GENERATED AS IDENTITY constraint can be removed from a column of the table, without removing the column by using ALTER TABLE ALTER COLUMN statement.

ALTER TABLE CARS
ALTER COLUMN car_id
DROP IDENTITY IF EXISTS;