PostgreSQL – Identity Column

In PostgreSQL, an identity column is a specialized column type that automatically generates unique values for each row, making it ideal for primary keys and other unique identifiers. Introduced in PostgreSQL 10, the GENERATED AS IDENTITY clause offers a SQL-standard alternative to the widely-used SERIAL column.

This feature allows PostgreSQL to generate unique, auto-incrementing values directly within the database. This article will guide us through understanding the PostgreSQL identity column, including examples of GENERATED AS IDENTITY in action, and highlight how it compares with SERIAL

PostgreSQL Identity Column

The identity column in PostgreSQL is a powerful feature that automatically generates unique numbers for rows in a table. This section will go deeper into the characteristics of identity columns, their creation, and how they streamline the management of auto-incrementing values in our database. We’ll also explore practical examples to illustrate their functionality and benefits.

Syntax

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

Key Terms

• column_name: The name of the column to be defined as an identity column.
• type: Specifies the data type of the column, which can be SMALLINT, INT, BIGINT, or another compatible data type.
• GENERATED ALWAYS: Instructs PostgreSQL to always generate a value for the identity column. Any attempt to insert or update a value into this column will result in an error unless using OVERRIDING SYSTEM VALUE.
• GENERATED BY DEFAULT: Specifies that PostgreSQL should generate a value for the identity column, but allows inserting or updating specific values into the column. If a value is provided, it will be used instead of the system-generated value.

PostgreSQL Identity Column Examples

Now let’s look into some examples of Identity Column in PostgreSQL to better understand the concept.

Example 1: Using GENERATED ALWAYS

Create a table named ‘color' with ‘color_id' as an identity column and insert a row:

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

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

SELECT * FROM color;

Because ‘color_id’ column has the GENERATED AS IDENTITY constraint, PostgreSQL generates a value for it as shown in the query below.

Output

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: Using GENERATED BY DEFAULT

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;
CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY,
    color_name VARCHAR NOT NULL
);

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

SELECT * FROM color;

Output

Here unlike the previous example that uses the GENERATED ALWAYS AS IDENTITY constraint, this statement also works.

Important Points About PostgreSQL GENERATED AS IDENTITY constraint

• PostgreSQL’s GENERATED AS IDENTITY is compliant with the SQL standard, providing a consistent and portable way to create auto-incrementing columns.
• GENERATED ALWAYS ensures that PostgreSQL always generates a unique value for the column.
• GENERATED BY DEFAULT allows you to insert specific values if needed. If no value is provided, PostgreSQL will generate one.
• Identity columns are optimized for performance, particularly in high-concurrency environments.

Conclusion

In summary, PostgreSQL identity columns provide a flexible way to handle auto-incrementing values, with options like GENERATED BY DEFAULT for manual overrides when needed. Compared to the traditional SERIAL type, identity columns are more aligned with SQL standards and offer greater control over sequence properties. Using identity columns simplifies key generation and ensures consistency across applications, making them a powerful alternative to SERIAL in modern PostgreSQL databases.