PostgreSQL 10 and later versions include a new feature called the "IDENTITY" column, which allows for the auto-incrementing of column values. The stated feature can be applied not only to new tables but also to existing tables. For this purpose, the ALTER TABLE statement is used along with the “ADD GENERATED AS IDENTITY” option.

This write-up will illustrate a thorough guide on adding an IDENTITY column to an already existing table in Postgres.

How Do I Add an IDENTITY Column to an Already Existing Postgres Table?

Use the ALTER TABLE statement with the “ADD GENERATED AS IDENTITY” option to add/insert an IDENTITY column into an existing table. However, altering a regular column to an IDENTITY column requires the targeted column to be defined as NOT NULL.

Use the below-provided syntax for the ALTER TABLE command to modify a regular column to an IDENTITY column:

ALTER TABLE tbl_name 
ALTER COLUMN col_name 
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY { ( seq_option ) }

Use either the “ALWAYS” or “BY DEFAULT” option to define a column as an IDENTITY. Visit the following guide on “Create IDENTITY Column” to learn more about the “ALWAYS” and “BY DEFAULT” options.

Example

We have a sample table named “emp_info” with the following details:

\d emp_info;

In the following example, the ALTER TABLE command is executed to add the IDENTITY column to the emp_info table:

ALTER TABLE emp_info
ALTER COLUMN emp_id
ADD GENERATED BY DEFAULT AS IDENTITY;

An error occurred while altering the “emp_id” column to the IDENTITY column:

To rectify the “column emp_id must be declared NOT NULL before IDENTITY can be added” error, first add a NON-NULL constraint on the selected column:

ALTER TABLE emp_info
ALTER COLUMN emp_id SET NOT NULL;

The NOT NULL constraint is successfully added to the “emp_id” column:

Let’s change the emp_info column to an IDENTITY column using the following query:

ALTER TABLE emp_info
ALTER COLUMN emp_id
ADD GENERATED BY DEFAULT AS IDENTITY;

To verify the table’s alteration, execute the below-provided meta-command with the table name:

\d emp_info;

The IDENTITY column has been successfully added to the emp_info table:

That’s all about adding an identity column to an existing table in Postgres.

Conclusion

In PostgreSQL, the ALTER TABLE statement is executed with the “ADD GENERATED AS IDENTITY” option to add an IDENTITY column to a pre-existing table. However, altering a regular column to an IDENTITY column requires the targeted column to be defined as NOT NULL. This article has illustrated a thorough guide on adding an IDENTITY column to an already existing table.