# How to Alter IDENTITY Column in PostgreSQL

> In PostgreSQL, the ALTER TABLE statement is used with ADD IDENTITY or DROP IDENTITY to add or remove an IDENTITY column to a Postgres table.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-alter-identity-column-in-postgresql/)