If you’re getting an error in PostgreSQL that reads something like “cannot insert into column” with detail that explains that the “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to insert your own value into an identity column that was created with the GENERATED ALWAYS option, but you’re not explicitly overriding the column from auto generating its own value.

To fix this issue, use OVERRIDING SYSTEM VALUE when inserting the value.

Example of Error

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

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

Result:

ERROR:  cannot insert into column "idiotid"
DETAIL:  Column "idiotid" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

I got that error because the column was defined as GENERATED ALWAYS but I didn’t include OVERRIDING SYSTEM VALUE.

Solution

To fix this issue, I need to specify OVERRIDING SYSTEM VALUE in the INSERT statement:

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.

It’s important to note that this won’t work when doing updates. There’s no OVERRIDING SYSTEM VALUE argument for the UPDATE statement. When doing updates on such columns (i.e. identity columns defined as GENERATED ALWAYS), we can only use the DEFAULT keyword to update the column.

Also note that this error only occurs because the identity column was created using the ALWAYS option. If it was created using the AS DEFAULT argument, we wouldn’t have received the error.