Introduction to PostgreSQL identity column

PostgreSQL version 10 introduced a new constraintGENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.

TheGENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good oldSERIAL column.

The following illustrates the syntax of theGENERATED AS IDENTITY constraint:

column_name typeGENERATED { ALWAYS | BY DEFAULT }AS IDENTITY[ (sequence_option ) ]

In this syntax:

• The type can beSMALLINT,INT, orBIGINT.
• TheGENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into theGENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.
• TheGENERATED BY DEFAULT instructs PostgreSQL to generate a value for the identity column. However, if you supply a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.

PostgreSQL allows a table to have more than one identity column. Like theSERIAL, theGENERATED AS IDENTITY constraint also uses theSEQUENCE object internally.

PostgreSQL identity column examples

Let’s take some examples of using the PostgreSQL identity columns.

1) GENERATED ALWAYS example

First,create a table namedcolor with thecolor_id as the identity column:

CREATE TABLE color (    color_idINT GENERATED ALWAYS AS IDENTITY,    color_nameVARCHAR NOT NULL);

Second, insert a new row into thecolor table:

INSERT INTO color(color_name)VALUES ('Red');

Becausecolor_id column has theGENERATED AS IDENTITY constraint, PostgreSQL generates a value for it as shown in the query below:

SELECT * FROM color;

Third, insert a new row by supplying values for bothcolor_id andcolor_name columns:

INSERT INTO color (color_id, color_name)VALUES (2,'Green');

PostgreSQL issued the following error:

[Err] ERROR:  cannotinsert into column"color_id"DETAIL:  Column"color_id" is anidentity column definedas GENERATED ALWAYS.HINT:Use OVERRIDINGSYSTEM VALUE to override.

To fix the error, you can use theOVERRIDING SYSTEM VALUE clause as follows:

INSERT INTO color (color_id, color_name)OVERRIDINGSYSTEM VALUEVALUES(2,'Green');

Alternatively, you can useGENERATED BY DEFAULT AS IDENTITY instead.

2) GENERATED BY DEFAULT AS IDENTITY example

First,drop thecolor table and recreate it. This time we will use theGENERATED BY DEFAULT AS IDENTITY instead:

DROP TABLE color;CREATE TABLE color (    color_idINT GENERATED BY DEFAULT AS IDENTITY,    color_nameVARCHAR NOT NULL);

Second, insert a row into thecolor table:

INSERT INTO color (color_name)VALUES ('White');

It works as expected.

Third, insert another row with a value for thecolor_id column:

INSERT INTO color (color_id, color_name)VALUES (2,'Yellow');

Unlike the previous example that uses theGENERATED ALWAYS AS IDENTITY constraint, the statement above works perfectly fine.

3) Sequence options example

Because theGENERATED AS IDENTITY constraint uses theSEQUENCE object, you can specify the sequence options for the system-generated values.

For example, you can specify the starting value and the increment as follows:

DROP TABLE color;CREATE TABLE color (    color_idINT GENERATED BY DEFAULT AS IDENTITY    (START WITH 10 INCREMENTBY 10),    color_nameVARCHAR NOT NULL);

In this example, the system-generated value for thecolor_id column starts with 10 and the increment value is also 10.

First, insert a new row into the color table:

INSERT INTO color (color_name)VALUES ('Orange');

The starting value forcolor_id column is ten as shown below:

SELECT * FROM color;

Second, insert another row into thecolor table:

INSERT INTO color (color_name)VALUES ('Purple');

The value of thecolor_id of the second row is 20 because of the increment option.

SELECT * FROM color;

Adding an identity column to an existing table

You can add identity columns to an existing table by using the following form of theALTER TABLE statement:

ALTER TABLE table_nameALTER COLUMN column_nameADD GENERATED {ALWAYS |BY DEFAULT }AS IDENTITY { ( sequence_option ) }

For example:

First, create a new table namedshape:

CREATE TABLE shape (    shape_idINT NOT NULL,    shape_nameVARCHAR NOT NULL);

Second, change theshape_id column to the identity column:

ALTER TABLE shapeALTER COLUMN shape_idADD GENERATED ALWAYS AS IDENTITY;

Note that theshape_id needs to have theNOT NULL constraint so that it can be changed to an identity column. Otherwise, you’ll get the following error:

ERROR:  column"shape_id" of relation"shape" must be declaredNOT NULL before identity can be addedSQL state:55000

The following command describes theshape table in psql tool:

\d shape

It returns the following output which is what we expected:

Changing an identity column

You can change the characteristics of an existing identity column by using the followingALTER TABLE statement:

ALTER TABLE table_nameALTER COLUMN column_name{SET GENERATED { ALWAYS| BY DEFAULT } |  SET sequence_option | RESTART [ [WITH ] restart ] }

For example, the following statement changes theshape_id column of theshape table toGENERATED BY DEFAULT:

ALTER TABLE shapeALTER COLUMN shape_idSET GENERATED BY DEFAULT;

The following command describes the structure of the shape table in the psql tool:

\d shape

As you can see from the output, theshape_id column changed fromGENERATED ALWAYS toGENERATED BY DEFAULT.

Removing the GENERATED AS IDENTITY constraint

The following statement removes theGENERATED AS IDENTITY constraint from an existing table:

ALTER TABLE table_nameALTER COLUMN column_nameDROP IDENTITY [IF EXISTS ]

For example, you can remove theGENERATED AS IDENTITY constraint column from theshape_id column of theshape table as follows:

ALTER TABLE shapeALTER COLUMN shape_idDROP IDENTITY IF EXISTS;

In this tutorial, you have learned how to use the PostgreSQL identity column and how to manage it by using theGENERATED AS IDENTITY constraint.

Last updated onFebruary 1, 2024

On this page

• Introduction to PostgreSQL identity column
• PostgreSQL identity column examples
• Adding an identity column to an existing table
• Changing an identity column
• Removing the GENERATED AS IDENTITY constraint