PostgreSQL 10 and subsequent versions introduce a new feature known as the “IDENTITY” column, enabling automatic incrementation of column values. The stated feature can be applied not only to new tables but also to existing tables. The IDENTITY column can be added or removed from a particular table using the ALTER TABLE command.

This write-up illustrates how to use ALTER TABLE statement to add or drop the IDENTITY column from a table.

How to Alter IDENTITY Column in PostgreSQL?

The below-listed topics will be covered in this PostgreSQL blog:

- Add IDENTITY Column

- Remove IDENTITY Column

Example 1: Add IDENTITY Column

Use the ALTER TABLE statement with the “ADD GENERATED AS IDENTITY” option to add/insert an IDENTITY column into an existing table. We have a sample table named “emp_info” with the following details:

\d emp_info;

Consider the following example to learn how to add an IDENTITY column to an existing table using the ALTER TABLE statement:

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:

Example 2: Remove the IDENTITY Column

To drop or delete an IDENTITY column from a table, execute the ALTER TABLE statement with the DROP IDENTITY option:

ALTER TABLE emp_info 
ALTER COLUMN emp_id 
DROP IDENTITY;

Let’s verify the table’s structure by running the “\d” command:

\d emp_info;

The output shows that the IDENTITY column has been successfully removed from the emp_info table:

That’s all about altering an IDENTITY column in PostgreSQL.

Conclusion

In PostgreSQL, the ALTER TABLE statement is used to add or remove an IDENTITY column to a Postgres table. Use the ALTER TABLE statement with the “ADD GENERATED AS IDENTITY” option to add/insert an IDENTITY column into an existing table. To drop or delete an IDENTITY column from a table, execute the ALTER TABLE statement with the DROP IDENTITY option. This post has illustrated how to add or remove the IDENTITY column of a table using the ALTER TABLE command.