Learning PostgreSQL 11 - Third Edition

Serial types and identity columns

Serial types and identity columns are used to define surrogate keys. The synopsis for defining a column as an identity column is as follows:

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

It's better to use identity columns, which were introduced in PostgreSQL 10, rather than serial, because this overcomes some of the serial type's limitations:

• Compatibility: The identity column adheres to SQL standards, this makes it easier to migrate PostgreSQL to other relational databases and vice versa.
• Permissions: The permissions of the sequence object that was created by using the serial column is managed separately. Often, developers tend to forget to assign proper permissions to the sequence ...