PostgreSQL 10 and later versions include a new feature called the "IDENTITY" column, which allows for the auto-incrementing of column values. This feature provides similar functionality to the SERIAL type but offers greater flexibility and enhanced features. IDENTITY columns are essentially a standardized version of SERIAL columns in SQL.

This write-up will demonstrate a complete process of creating and using the IDENTITY column in PostgreSQL.

How Do I Create/Generate an IDENTITY Column in Postgres?

In Postgres, an IDENTITY column comes with a built-in sequence. When a new record is added, a value is generated/created from the sequence and assigned to the identity column.

Follow the given syntax to create an identity column in Postgres:

col_name TYPE GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( seq_option ) ]

In the given syntax:

- Replace the TYPE with SMALLINT, INTEGER, or BIGINT.
- The "GENERATED ALWAYS" clause means always generate/create a new and unique value for the IDENTITY column. In that case, inserting or updating values in the IDENTITY column will lead you to an error.
- Using the "GENERATED BY DEFAULT" clause in the IDENTITY column allows us to insert or update user-specified values instead of relying on system-generated values.

Example 1: Creating GENERATED ALWAYS AS IDENTITY Column in Postgres

In the following example, a table named “commandprompt_example” is created with the “id” and “blog_name” columns. The “GENERATED ALWAYS AS IDENTITY” clause is utilized in the below-provided code example to create an IDENTITY column:

CREATE TABLE commandprompt_example(
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
blog_name TEXT
);

The following screenshot shows the successful creation of the “commandprompt_example” table:

Let’s utilize the INSERT query to insert a couple of records into the “commandprompt_example” table:

INSERT INTO commandprompt_example(blog_name)
VALUES ('blog 1'),
('blog 2');

Fetch the newly inserted records to comprehend the working of the IDENTITY column:

SELECT * FROM commandprompt_example;

The following snippet demonstrates that the auto-incrementing values have been successfully inserted into the “commandprompt_example” table:

Let’s insert a user-specified value in the IDENTITY column and see how it deals with that particular scenario:

INSERT INTO commandprompt_example(id, blog_name)
VALUES (3, 'blog 3');

An error occurred stating that we can’t insert or update an IDENTITY column that is created with the “GENERATED ALWAYS” clause:

Example 2: Creating a “GENERATED BY DEFAULT” Column in Postgres

In the following code, the id column is an IDENTITY column that is created using the “GENERATED BY DEFAULT” clause:

CREATE TABLE commandprompt_example_1(
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
blog_name TEXT
);

Now insert and retrieve the data into the “commandprompt_example_1” table:

INSERT INTO commandprompt_example_1(blog_name)
VALUES ('blog 1'),
('blog 2')
RETURNING *;

Let’s explicitly insert a value in the IDENTITY column and see how it deals with that particular scenario:

INSERT INTO commandprompt_example_1(id, blog_name)
VALUES (3, 'blog 3');

The below snippet shows that an explicit value has been successfully inserted into the “GENERATED BY DEFAULT AS IDENTITY” Column:

That’s all you need to know about creating an IDENTITY column in Postgres.

Conclusion

PostgreSQL 10 introduced a new feature named the "IDENTITY" column, which allows us to create a table’s column with auto-incrementing values. To create an IDENTITY column in Postgres, all you need to do is use the "GENERATED ALWAYS" or "GENERATED BY DEFAULT" constraints with the IDENTITY column. This write-up has illustrated the complete process of creating an IDENTITY column in PostgreSQL.