PostgreSQL Identity Columns

In PostgreSQL, an identity column is a special generated column that is automatically generated and unique. An identity column is a constraint defined on the column.

Identity columns are similar with SERIAL, they are implemented internally using SEQUENCE.

Unlike primary keys, multiple identity columns are allowed in a table, and duplicate values are allowed in a identity column.

Syntax for Identity Columns

To create an identity column, use the following syntax:

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

explain:

The data type of the identity column can be: SMALLINT, INT, or BIGINT.
The value of the GENERATED ALWAYS AS IDENTITY column cannot be written. Inserting or updating a value into this column will causes an error. There is a special case where a value can be inserted into this column using the OVERRIDING SYSTEM VALUE directive in the INSERT statement.
The value of the GENERATED BY DEFAULT AS IDENTITY column can be written, including inserts and updates.
The sequence_options specifies the option to the sequence used inside of by identify column. It is optional, if not specified, PostgreSQL uses the default option to generate a sequence.

You can define an identity column when creating a table with the CREATE TABLE statement, or add an identity column with the ALTER TABLE statement.

Define the identity column when creating the table

To add an identity column when creating a table, use the following syntax:

CREATE TABLE table_name (
  column_definition,
  ...
  col_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ],
  ...
);

Add identity column

To add an identity column after the table was created, use the ALTER TABLE statement:

ALTER TABLE table_name
  ADD COLUMN col_name data_type
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

Set a column to the table identity column

To set an existing column as an identity column, use the ALTER TABLE statement:

ALTER TABLE table_name
  ALTER COLUMN col_name
    ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

ALTER TABLE table_name
  ALTER COLUMN col_name
    SET GENERATED { ALWAYS | BY DEFAULT };

Unset an identity column

To suppress an identity column, use the ALTER TABLE statement:

ALTER TABLE table_name
  ALTER COLUMN col_name
    DROP IDENTITY [ IF EXISTS ];

PostgreSQL identity columns Examples

Below are some examples showing the usage of PostgreSQL identity columns.

Example of creating an identity column

Use the following statement to create a table where the id column is a identity column:

CREATE TABLE test_identity (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  txt VARCHAR(45) NOT NULL
);

You can view the status of the identity column in test_identity with the \d command:

\d test_identity

                            Table "public.test_identity"
Column |         Type          | Collation | Nullable |           Default
--------+-----------------------+-----------+----------+------------------------------
id     | integer               |           | not null | generated always as identity
txt    | character varying(45) |           | not null |

Insert 2 rows of test data using the following statement:

INSERT INTO test_identity (txt)
VALUES ('Apple'), ('Peach');

Find all rows in the test_identity table:

SELECT * FROM test_identity;

id |  txt
----+-------
 1 | Apple
 2 | Peach
(2 rows)

As you can see from the output, the auto-generated values in the id column are an auto-incrementing sequence.

Insert a value instance into an identity column

The following INSERT statement attempts to insert a value into the identity column:

INSERT INTO test_identity (id, txt)
VALUES (1, 'Banana')
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.

Here, the value cannot be inserted directly because the id column has a constraint: GENERATED ALWAYS AS IDENTITY. According to PostgreSQL’s prompt, you can use OVERRIDING SYSTEM VALUE force to insert, as follows:

INSERT INTO test_identity (id, txt)
OVERRIDING SYSTEM VALUE
VALUES (1, 'Banana')
RETURNING *;

id |  txt
----+--------
 1 | Banana
(1 row)

At the same time, we can also see that the identity column allows duplicate values.

Example of multiple identity columns

There can be multiple identity columns in a table. The following statement adds an identity column id_2 with a starting value of 10 and increments by 10 each time:

ALTER TABLE test_identity
  ADD COLUMN id_2 INTEGER GENERATED ALWAYS AS IDENTITY
    (START WITH 10 INCREMENT BY 10);

Let’s look at the data in the table:

SELECT * FROM test_identity;

id |  txt   | id_2
----+--------+------
 1 | Apple  |   10
 2 | Peach  |   20
 1 | Banana |   30
(3 rows)

You can find that after adding an identity column, the value in the identity column in the existing row has been automatically generated.