Create an IDENTITY Column in PostgreSQL

Posted on by Ian

Prior to PostgreSQL 10, if we wanted to create an auto-incrementing column, we would typically use the SERIAL type, or we’d create our own sequence and apply it to an integer column.

But from PostgreSQL 10 onwards, we’ve been able to create identity columns.

Identity columns are basically the SQL standard-conforming variant of SERIAL columns. Identity columns have an implicit sequence attached to them. When a new row is inserted, a new sequence value is generated and assigned to the identity column. Identity columns are implicitly NOT NULL.

In this article I demonstrate how to create an identity column in PostgreSQL.

Syntax

The following syntax applies to creating an identity column in PostgreSQL:

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

This is applied to the column, within the definition for the table.

The sequence_options argument in the syntax indicates that It’s possible to override the options of the sequence.

The ALWAYS and BY DEFAULT arguments are used in the event that the user provides the value instead of letting the identity column generate its own value. These are treated differently, depending on whether it’s an INSERT or an UPDATE:

  • In an INSERT statement:
    • ALWAYS means that a user-specified value will only be accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE.
    • BY DEFAULT means that the user-specified value takes precedence.
  • In an UPDATE statement:
    • ALWAYS means that any updates to the column must set it to DEFAULT. Anything else will be rejected and will result in an error. Note that there is no OVERRIDING clause for the UPDATE command.
    • BY DEFAULT means that the column can be updated normally.

Example

Here’s an example of code that creates a table with an identity column:

CREATE TABLE Idiots (
    IdiotId int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    IdiotName VARCHAR
);

We can see that the GENERATED... part is applied as the definition of the column that we want to be the identity column.

In this case, I specified ALWAYS instead of BY DEFAULT. This will have implications if we ever try to override the identity column (i.e. by inserting our own value into the identity column). I demonstrate this in some of the examples below.

The following examples illustrate the two ways the identity column can be populated; by the default value that’s generated or by a value of our choosing.

Generate an Identity Value

Once we’ve created an identity column, the column will generate its own default value whenever we insert a new row into the table. Therefore, we don’t need to provide a value for that column.

Here’s an example of inserting data into the table we created above:

INSERT INTO Idiots ( IdiotName ) 
VALUES ( 'Dumb' ), ( 'Dumber' ), ( 'Dumbest' )
RETURNING *;

Result:

 idiotid | idiotname 
---------+-----------
       1 | Dumb
       2 | Dumber
       3 | Dumbest

Here, I used the RETURNING clause to show the rows that were inserted.

In this example, I didn’t explicitly provide any values to go into the identity column and so the insert operation used the default auto-incrementing numbers generated by the identity column. This is one of the benefits of auto-incrementing columns, we don’t need to create and keep track of our own values.

Override the Identity Column

Here’s what happens when we try to insert our own value into the identity column:

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.

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

Let’s do it again, but this time with OVERRIDING SYSTEM VALUE: 

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

Result:

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

This time our custom value was accepted. In other words, we overrode the value that the identity column was going to generate for us and we inserted our own.

Note that this wouldn’t have been an issue if we had created the identity column using the AS DEFAULT argument. When that argument is used, the user-specified value takes precedence.

Update the Value in the Identity Column

Now let’s try to update the value in the identity column:

UPDATE Idiots 
SET IdiotId = 4
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.

As the error message explains, we can only update the identity column to DEFAULT when it was created using GENERATED ALWAYS. Therefore, we’ll need to do this:

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

Result:

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

This time it worked. Homer’s IdiotId was updated to 4, which was the next default value for the identity column.

Again, this wouldn’t have been an issue if we had created the identity column using the AS DEFAULT argument. When that argument is used, the column can be updated normally.