This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL 10 IDENTITY Column is an excellent feature of this version. Since the beginning of Microsoft SQL Server, IDENTITY Column is available with it.
SQL Server 2012 introduced Sequence Object, and since beginning SERIAL/BIGSERIAL (Sequence Object) are available in PostgreSQL.
The new IDENTITY Type Column is used to generate an automatic number.
Now, we have this great feature, and creating an auto-increment column is also very simple.
Why is IDENTITY good over SERIAL/BIGSERIAL (Sequence Object)?
Sequence Object requires additional permission
Managing the Sequence Object is difficult while migrating table structure
Check the below example:
Create a sample table with IDENTITY column:
|
1 2 3 4 |
CREATE TABLE tbl_test ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name character varying ); |
Insert few records:
|
1 2 |
INSERT INTO tbl_test (name) VALUES ('abc'), ('xyz'), ('pqr'); |
Check the result:
|
1 2 3 4 5 6 7 |
SELECT *FROM tbl_test; id | name ------------------- 1 | abc 2 | xyz 3 | pqr |
Leave a Reply