Summary: in this tutorial, you will learn how to use the GENERATED AS IDENTITY constraint to create the PostgreSQL identity column for a table.
Introduction to PostgreSQL identity column
PostgreSQL version 10 introduced a new feature called GENERATED AS IDENTITY constraint that allows you to automatically assign a unique value to a column. The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the PostgreSQL’s SERIAL column.
The following illustrates the syntax of the GENERATED AS IDENTITY constraint:
1 | column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ] |
In this syntax:
- The type can be
SMALLINT,INT, orBIGINT. - The
GENERATED ALWAYSinstructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) a value into theGENERATED ALWAYS AS IDENTITYcolumn, PostgreSQL will issue an error. - The
GENERATED BY DEFAULTalso instructs PostgreSQL to generate a value for the identity column. However, if you provide 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 you to have more than one identity column in a table. Similar to the SERIAL constraint, the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.
PostgreSQL identity column examples
A) GENERATED ALWAYS example
First, create a table named color with the color_id as the identity column:
1 2 3 4 | CREATE TABLE color ( color_id INT GENERATED ALWAYS AS IDENTITY, color_name VARCHAR NOT NULL ); |
Second, insert a new row into the color table:
1 2 3 | INSERT INTO color (color_name) VALUES ('Red'); |
Because color_id column has the GENERATED AS IDENTITY constraint, PostgreSQL generates a value for it as shown in the query below:
1 2 3 4 | SELECT * FROM color; |
Third, insert a new row by providing values for both color_id and color_name columns:
1 2 3 | INSERT INTO color (color_id, color_name) VALUES (2, 'Green'); |
PostgreSQL issued the following error:
1 2 3 | [Err] ERROR: cannot insert into column "color_id" DETAIL: Column "color_id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. |
To fix the error, in this case, you can use the OVERRIDING SYSTEM VALUE clause as follows:
1 2 3 4 | INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES (2, 'Green'); |
Or use GENERATED BY DEFAULT AS IDENTITY instead.
B) GENERATED BY DEFAULT AS IDENTITY example
First, drop the color table and recreate it. This time we use the GENERATED BY DEFAULT AS IDENTITY instead:
1 2 3 4 5 6 | DROP TABLE color; CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY, color_name VARCHAR NOT NULL ); |
Second, insert a row into the color table:
1 2 3 | INSERT INTO color (color_name) VALUES ('White'); |
It works as expected.
Third, insert another row with a value for the color_id column:
1 2 3 | INSERT INTO color (color_id, color_name) VALUES (2, 'Yellow'); |
Unlike the previous example that uses the GENERATED ALWAYS AS IDENTITY constraint, this statement also works.
C) Sequence options example
Because the GENERATED AS IDENTITY constraint uses the SEQUENCE 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:
1 2 3 4 5 6 7 | DROP TABLE color; CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), color_name VARCHAR NOT NULL ); |
In this example, the system-generated value for the color_id column starts with 10 and the increment value is also 10.
First, insert a new row into the color table:
1 2 3 | INSERT INTO color (color_name) VALUES ('Orange'); |
The starting value for color_id column is ten as shown below:
1 2 3 4 | SELECT * FROM color; |
Second, insert another row into the color table:
1 2 3 | INSERT INTO color (color_name) VALUES ('Purple'); |
The value for the color_id of the second row is 20 because of the increment option.
1 2 3 4 | 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 the ALTER TABLE statement:
1 2 3 | ALTER TABLE table_name ALTER COLUMN column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY { ( sequence_option ) } |
Let’s see the following example.
First, create a new table named shape:
1 2 3 4 | CREATE TABLE shape ( shape_id INT, shape_name VARCHAR NOT NULL ); |
Second, change the shape_id column to an identity column:
1 2 | ALTER TABLE shape ALTER COLUMN shape_id ADD GENERATED ALWAYS AS IDENTITY; |
The following command describes the shape table in psql tool:
1 | \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 following ALTER TABLE statement:
1 2 3 4 | ALTER TABLE table_name ALTER COLUMN column_name { SET GENERATED { ALWAYS| BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } |
For example, the following statement changes the shape_id column of the shape table to GENERATED BY DEFAULT:
1 2 | ALTER TABLE shape ALTER COLUMN shape_id SET GENERATED BY DEFAULT; |
The following command describes the structure of the shape table in the psql tool:
1 | \d shape |
As you can see from the output, the shape_id column changed from GENERATED ALWAYS to GENERATED BY DEFAULT.
Removing the GENERATED AS IDENTITY constraint
The following statement removes the GENERATED AS IDENTITY constraint from an existing table:
1 2 3 | ALTER TABLE table_name ALTER COLUMN column_name DROP IDENTITY [ IF EXISTS ] |
For example, you can remove the GENERATED AS IDENTITY constraint column from the shape_id column of the shape table as follows:
1 2 3 | ALTER TABLE shape ALTER COLUMN shape_id DROP IDENTITY IF EXISTS; |
In this tutorial, you have learned how to use the PostgreSQL identity column and how to manage it through the GENERATED AS IDENTITY constraint.