PostgreSQL Identity Column

GENERATED AS IDENTITY Constraint allows you to automatically assign a unique value to a column which introduced as a new feature in PostgreSQL version 10. The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the PostgreSQL’s SERIAL column.

Syntax of Postgres identity column

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

Note:

1- Column data should be SMALLINT, INT, or BIGINT.

2- GENERATED ALWAYS generates a value for the identity column. If you try to insert a value in identity column manually through query statement, it will raise an error.

3- The GENERATED BY DEFAULT also instructs PostgreSQL to generate a value for the identity column. However, if you provide a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.

4- PostgreSQL allows you to have more than one identity column in a table. Similar to the SERIAL constraint, the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.PostgreSQL identity column examples

A) GENERATED ALWAYS example

Create a table named EMP with id and name (id is identity column)

CREATE TABLE (   id INT GENERATED ALWAYS AS IDENTITY,   name text not null );

Now, insert a record in the table, run the following query

INSERT INTO emp(name)   VALUES('Emp1');

id column has the GENERATED AS IDENTITY constraint, PostgreSQL generates a value for it as shown in the query below:

SELECT * FROM emp; Result: id |  name ---+------- 1  |  "Emp1"

An another analysis for the EMP table, now, I will insert new row providing the value for both id and name columns.

INSERT INTO emp(id, name) VALUES(2, 'Emp2');

It will raise the following error:

"ERROR:  cannot insert into column "id"

DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

HINT:  Use OVERRIDING SYSTEM VALUE to override.

SQL state:  428C9"

To fix the error, in this case, you can use the OVERRIDING SYSTEM VALUE clause as follows.

INSERT INTO emp(id, name) OVERRIDING SYSTEM VALUE VALUES(2, 'Emp2');

Result:

id             |              name ----+-------- 1              |              "Emp1" 2              |              "Emp2"

B) GENERATED BY DEFAULT AS IDENTITY example

I will drop the already created EMP table, and again create it using the following query.

DROP TABLE EMP; CREATE TABLE EMP (     id INT GENERATED BY DEFAULT AS IDENTITY ,        name text not null )

Now, insert a row

INSERT INTO emp(name) VALUES('Emp1'); SELECT * from emp

Result:

id |  name ---+------- 1  |  "Emp1"

Add another row with value for id also.

INSERT INTO emp(id, name) VALUES(2, 'Emp2');

Added successfully, it works as expected

Result:

id             |              name ----+-------- 1              |              "Emp1" 2              |              "Emp2"

C) Sequence options example

Because of the GENERATED AS IDENTITY constraint uses the SEQUENCE object, you can specify the sequence options for the system-generated values.

For example, you can specify the starting value and an increment as follows:

DROP TABLE EMP; CREATE TABLE EMP (     id INT GENERATED BY DEFAULT AS IDENTITY     (START WITH 5 INCREMENT BY 5),     name VARCHAR NOT NULL );

In this example, the system-generated value for the color_id column starts with 5 and the increment value is also 5.

First, insert a new row into the color table:

INSERT INTO emp (name) VALUES('Emp1');

Result:

id |  name ---+------- 5  |  Emp1

Added another row

INSERT INTO emp (name) VALUES('Emp2');

Result:

id             |              name ----+-------- 5              |              "Emp1" 10           |              "Emp2"