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


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 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 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 the 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 10 and the increment value is also 10.
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"



PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...