# How to Fix the “column can only be updated to DEFAULT” Error in Postgres

> The “column can only be updated to DEFAULT” error arises when a user tries to update the value of an IDENTITY column that is created using the "GENERATED ALWAY…

PostgreSQL 10 introduced a new feature named the " **IDENTITY** " column, which allows us to create a table’s column with auto-incrementing values. While working with the IDENTITY column, users may face the “column can only be updated to DEFAULT” error. The stated error occurs when the IDENTITY column uses the " **GENERATED ALWAYS** " clause.

This post illustrates a complete process of fixing the “column can only be updated to DEFAULT” ERROR in Postgres.

 **How to Fix the “column can only be updated to DEFAULT” Error in Postgres?**

The IDENTITY column in Postgres can be created either by using the " **GENERATED ALWAYS** " constraint or by using the " **GENERATED BY DEFAULT** " constraint. The “ **column can only be updated to DEFAULT** ” error arises when a user tries to update the value of an IDENTITY column that is created using the " **GENERATED ALWAYS** " option. Use one of the below-provided methods to rectify the stated error:

  * DEFAULT Option
  * ALTER IDENTITY Column to the GENERATED AS DEFAULT.



 **Example: Update IDENTITY Column in Postgres**

A table named “commandprompt_example” is created with an IDENTITY column. Here is the detailed information regarding the selected table:
    
    
    \d commandprompt_example;

Let’s fetch all the records from the said table to comprehend the working of the IDENTITY column:
    
    
    SELECT * FROM commandprompt_example;

Suppose we want to update the column having id “3”. For this purpose, execute the UPDATE query as follows:
    
    
    UPDATE commandprompt_example
    SET id =  5
    WHERE id = 3;

 **Solution 1: Using the DEFAULT Option**

One way to fix the stated error is the utilize the “DEFAULT” keyword/option:
    
    
    UPDATE commandprompt_example
    SET id = DEFAULT
    WHERE id = 3;

The output snippet shows that the selected record has been successfully updated:

 **Solution 2: ALTER IDENTITY Column to the GENERATED AS DEFAULT**

Postgres allows us to modify the value of the IDENTITY column that is created using the "GENERATED BY DEFAULT" clause. So, altering the “ **GENERATED AS ALWAYS** ” to “ **GENERATED AS DEFAULT** ” will efficiently fix the stated error. To do that utilize the ALTER TABLE command as follows:
    
    
    ALTER TABLE commandprompt_example
    ALTER COLUMN id SET GENERATED BY DEFAULT;

The selected table has been successfully altered, as shown in the following snippet:

Now, execute the UPDATE statement to modify a particular identity:
    
    
    UPDATE commandprompt_example
    SET id =  6
    WHERE id = 2
    RETURNING *;

The selected record has been successfully updated:

That’s all about fixing the “column can only be updated to DEFAULT” ERROR in Postgres.

 **Conclusion**

The “ **column can only be updated to DEFAULT** ” error arises when a user tries to update the value of an IDENTITY column that is created using the " **GENERATED ALWAYS** " option. To rectify the stated error, either use the “DEFAULT” option or alter the IDENTITY column to the “GENERATED AS DEFAULT”. This write-up has presented a couple of fixes for the “column can only be updated to DEFAULT” error in PostgreSQL.

---
[View this page online](https://www.commandprompt.com/education/how-to-fix-the-column-can-only-be-updated-to-default-error-in-postgres/)