PostgreSQL – Identity Column

In PostgreSQL, the GENERATED AS IDENTITY constraint is used to create a PostgreSQL identity column. It allows users 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.

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

Let’s analyze the above 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) a value into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.
• The GENERATED BY DEFAULT also 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.

Now let’s look into some examples.

Example 1:
First, create a table named color with the color_id as the identity column:

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

Second, insert a new row into the color table:

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:

SELECT * FROM color;

This will result in the below:

Third, insert a new row by providing values for both color_id and color_name columns:

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

PostgreSQL issued the following error:

[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:

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

Now if we use the below statement to verify the entry:

SELECT * FROM color;

Output:

Example 2:
In this example, we will use the GENERATED BY DEFAULT AS IDENTITY to create the same table we created above. To do so, drop the color table as below:

DROP TABLE color;

Now recreate the table as below:

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY,
    color_name VARCHAR NOT NULL
);

Now, insert a row into the color table:

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

Now, insert another row with a value for the color_id column:

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

Here unlike the previous example that uses the GENERATED ALWAYS AS IDENTITY constraint, this statement also works.
To verify the inserted data use the below statement:

SELECT * FROM color;

Output:

My Personal Notes arrow_drop_up

Save