PostgreSQL 10 and later versions have the latest feature named the IDENTITY column. This column is used to generate auto-incrementing values. The IDENTITY column can be added to newly created tables or existing tables. When this column is no longer needed, it can be deleted from the table.

This write-up will demonstrate a practical guide on dropping an IDENTITY column from a table in PostgreSQL.

How to Drop IDENTITY Column From a Postgres Table?

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

ALTER TABLE tbl_name 
ALTER COLUMN col_name 
DROP IDENTITY [ IF EXISTS ];

Use the IF EXISTS option to check the presence of an IDENTITY column before dropping it from a table.

Example 1

First, let’s check the structure of the sample table by executing the following meta-command:

\d emp_info;

The output screenshot shows that the emp_id is an IDENTITY column:

The below-provided coding example illustrates how to drop/remove an IDENTITY column from a specific table:

ALTER TABLE emp_info 
ALTER COLUMN emp_id 
DROP IDENTITY;

Now execute the “\d” command to verify the table’s structure:

\d emp_info;

The emp_info table does not have any IDENTITY column:

Trying to drop an IDENTITY column that does not actually exist will result in the following error:

ALTER TABLE emp_info 
ALTER COLUMN emp_id 
DROP IDENTITY;

To avoid the stated error, use the IF EXIST option along with the DROP IDENTITY option:

ALTER TABLE emp_info 
ALTER COLUMN emp_id 
DROP IDENTITY IF EXISTS;

The following snippet proves that Postgres shows a notice instead of throwing an error:

That’s all about dropping an identity column from a table in Postgres.

Conclusion

To drop or delete an IDENTITY column from a table, execute the ALTER TABLE statement with the DROP IDENTITY option. Use the IF EXISTS option with the DROP IDENTITY clause to check the presence of an IDENTITY column before dropping it from a table. This blog post has illustrated a thorough guide on dropping/deleting the IDENTITY column from a Postgres table.