If you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.

When an identity column was created with the GENERATED ALWAYS option, we can only use DEFAULT when updating that column.

So to fix this issue, either use DEFAULT as the new value, or alter the column to use the GENERATED AS DEFAULT option.

Another option is to insert a new row and use the OVERRIDING SYSTEM VALUE option in the insert statement.

Example of Error

Here’s an example of code that produces the error:

UPDATE Idiots 
SET IdiotId = 21
WHERE IdiotId = 20
RETURNING *;

Result:

ERROR:  column "idiotid" can only be updated to DEFAULT
DETAIL:  Column "idiotid" is an identity column defined as GENERATED ALWAYS.

I got that error because the IdiotId column was defined as GENERATED ALWAYS when it was created. When this option is selected, the only value we can use to update the column is DEFAULT.

Solution 1

One solution to this issue is to set the column to DEFAULT:

UPDATE Idiots 
SET IdiotId = DEFAULT
WHERE IdiotId = 20
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
       4 | Homer

This time we didn’t get an error. The system generated its own value for the identity column.

Solution 2

Another solution is to alter the column so that it’s defined using the GENERATED BY DEFAULT option:

ALTER TABLE Idiots
ALTER COLUMN IdiotId SET GENERATED BY DEFAULT;

Now we can insert our preferred value into the identity column:

UPDATE Idiots 
SET IdiotId = 21
WHERE IdiotId = 20
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
      21 | Homer

This time we didn’t get an error.

Solution 3

When a column has been created with the GENERATED ALWAYS option, we have more flexibility when inserting data (as opposed to updating existing data). In particular, when using the INSERT statement, PostgreSQL provides us with the OVERRIDING SYSTEM VALUE option.

Therefore, another option available to us is to insert a new value altogether using the OVERRIDING SYSTEM VALUE in the INSERT statement. This might be an option if you’re unable to alter the table like we did in the previous example.

First, let’s alter the identity column back to use our original GENERATED ALWAYS:

ALTER TABLE Idiots
ALTER COLUMN IdiotId SET GENERATED ALWAYS;

Now let’s insert a new value using OVERRIDING SYSTEM VALUE:

INSERT INTO Idiots ( IdiotId, IdiotName ) 
OVERRIDING SYSTEM VALUE
VALUES ( 20, 'Homer' )
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
      20 | Homer

This time my custom value was accepted. In other words, I overrode the value that the identity column was going to generate and I inserted my own.