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"



NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...