When I was started work on PostgreSQL, creating the first time a table, I become stuck on the Identity column because I was familiar with the MS SQL Server. So, first of all, I searched the question, "Does Postgres automatically generate an id for every row?".
The answer
is Yes.
There are several ways you can generate the identity column in PostgreSQL, and some examples are given below
1- SERIAL and BIGSERIAL
Using serial
and bigserial you can generate the identity column
Example:
--For the SERIAL
CREATE TABLE employee
(
empid serial NOT NULL,-- It's generate for
the int
name text,
address
text,
constraint
pk_employee_empid Primary
key(empid)
);
--Insert some records
INSERT INTO employee(name,address)VALUES('Naina','Noida');
INSERT INTO employee(name,address)VALUES('Ashish','Delhi');
--Now select statement
SELECT * FROM employee;
|
In result
you can see the ID is auto-generates and increment by one
Result:
The same thing will happen with bigserial
--For the BIGSERIAL
CREATE TABLE employee
(
empid bigserial NOT NULL,-- it generate for
the bigint
name text,
address
text,
constraint
pk_employee_empid Primary
key(empid)
);
|
2- GENERATED ALWAYS AS IDENTITY
Using
GENERATED ALWAYS AS IDENTITY key we can generate the identity column, it
automatically assign a unique value to a column which introduced as a new
feature in PostgreSQL version 10.
Run the following query to re-create the employee table.
DROP TABLE IF EXISTS employee;
CREATE TABLE employee
(
empid INT GENERATED ALWAYS AS IDENTITY,
name text not null,
address
text,
constraint
pk_employee_empid Primary
key(empid)
);
Insert some records
INSERT INTO employee(name,address)VALUES('Naina','Noida');
INSERT INTO employee(name,address)VALUES('Ashish','Delhi');
|
Result: