This tutorial shows you how to use the GENERATED AS IDENTITY constraint to create the PostgreSQL identity column for a table.
PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.
The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.
The following illustrates the syntax of the GENERATED AS IDENTITY constraint:
column_name type
GENERATED { ALWAYS | BY DEFAULT }
AS IDENTITY[ ( sequence_option ) ]
In this syntax:
The type can be SMALLINT, INT, or BIGINT.
The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.
The GENERATED 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 the SERIAL, the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.
To fix the error, you can use the OVERRIDING SYSTEM VALUE clause as follows:
INSERT INTO color (color_id, color_name)
OVERRIDING SYSTEM VALUE
VALUES(2, 'Green');
Alternatively, you can use GENERATED BY DEFAULT AS IDENTITY instead.
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:
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.
Databases
