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 object when changing the permissions of the table that contains the defined serial type.
• Sequence value and user data precedence: The serial type uses default constrain to assign the value of the column. That means you can override the default values. The identity column can control this behavior. The BY DEFAULT option allows the user to insert data into the column. If ALWAYS is specified, the user value won't be accepted unless the INSERT statement specifies OVERRIDING SYSTEM VALUE. Note that this setting is ignored by the COPY statement. 
• Managing table structure: It's easier, from a syntactical point view, to manage the identity column. It's also easier to alter existing columns' default values if they're defined as an identity. 

Both identity columns and serial types use sequence objects behind the scenes. In most cases, it's straightforward to replace a serial type with an identity column, because both behave similarly. 

The following examples shows how to define surrogate keys using an identity column and a serial type. They also show the limitations of the serial type:

• Create a table of the SERIAL type and perform an INSERT operation:

postgres=# CREATE TABLE test_serial (id SERIAL PRIMARY KEY, payload text);
CREATE TABLE
postgres=# INSERT INTO test_serial (payload) SELECT 'a' RETURNING *;
 id | payload
----+---------
  1 | a
(1 row)
postgres=# INSERT INTO test_serial (id, payload) SELECT 2, 'a' RETURNING *;
 id | payload
----+---------
  2 | a
(1 row)
postgres=# INSERT INTO test_serial (payload) SELECT 'a' RETURNING *;
ERROR: duplicate key value violates unique constraint "test_serial_pkey"
DETAIL: Key (id)=(2) already exists.

• Create a table with IDENTITY and perform an INSERT operation:

postgres=# CREATE TABLE test_identity ( id INTEGER generated by default as identity PRIMARY KEY, payload text);
CREATE TABLE
postgres=# INSERT INTO test_identity (payload) SELECT 'a' RETURNING *;
 id | payload
----+---------
  1 | a
(1 row)
postgres=# INSERT INTO test_identity (id, payload) SELECT 1, 'a' RETURNING *;
ERROR: duplicate key value violates unique constraint "test_identity_pkey"
DETAIL: Key (id)=(1) already exists.
postgres=# INSERT INTO test_identity (id, payload) SELECT 100000, 'a' RETURNING *;
   id | payload
--------+---------
 100000 | a
(1 row)

• IDENTITY and SERIAL internal implementation depends on sequence:

postgres=# \ds 
List of relations
Schema  | Name                 | Type     | Owner
--------+----------------------+----------+----------
 public | test_identity_id_seq | sequence | postgres
 public | test_serial_id_seq   | sequence | postgres
(2 rows)

• Create an IDENTITY column with the ALWAYS option:

postgres=# CREATE TABLE test_identity2 ( id INTEGER generated always as identity PRIMARY KEY, payload text);
CREATE TABLE
postgres=# INSERT INTO test_identity2 (id, payload) SELECT 1, 'a' RETURNING *;
ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.

IDENTITY columns are developed to overcome known limitations of the SERIAL type; mainly, permission issues and comparability.