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.