In PostgreSQL, the ALTER TABLE statement is used along with the “ADD GENERATED AS IDENTITY” option to add the IDENTITY column to a pre-existing table. However, a user may face unwanted circumstances if the column to be altered is not defined as NOT NULL.

This write-up illustrates how to fix the “column ‘column_name’ of the relation ‘relation_name’ must be declared NOT NULL” error in PostgreSQL.

How to Fix the “column ‘column_name’ of the relation ‘relation_name’ must be declared NOT NULL before an identity can be added” Error in PostgreSQL?

To fix the stated error, follow the below-exhibited steps:

Step 1: Check Table Structure

Check the table’s structure by executing the following meta-command:

\d emp_info;

The output snippet demonstrates that no constraint has been applied to the “emp_id” column:

In the below-given example code, the ALTER TABLE statement 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;

On executing the above code, we encountered the following error:

To fix the “column emp_id must be declared NOT NULL before IDENTITY can be added” error, first, we need to add a NOT 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:

Now execute the “\d” meta-command one more time to see the modified structure of the emp_info table:

\d emp_info;

The NOT NULL constraint has been added to the emp_id column:

Now execute the given statement to change the emp_info column to an IDENTITY column:

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

Execute the below-provided meta-command with the table name to check if the IDENTITY column is added to the table or not:

\d emp_info;

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

That’s all about fixing the “column of the relation must be declared NOT NULL before an identity can be added” error in Postgres.

Conclusion

In PostgreSQL, altering a regular column to an IDENTITY column requires the targeted column to be defined as NOT NULL. If the selected column does not meet this criterion, the error “The column of the relation must be declared NOT NULL before an identity can be added” occurs in Postgres, as demonstrated in this write-up.