PostgreSQL – SERIAL

PostgreSQL has a special kind of database object generator called SERIAL. It is used to generate a sequence of integers which are often used as the Primary key of a table. 

Syntax: variable_name SERIAL

When creating a table, this sequence of integers can be created as follows: 

CREATE TABLE table_name(
    id SERIAL
);

By assigning the SERIAL to the id column, PostgreSQL carries out the following: 

• The initial step generates sequences of integers, where the next default value depends upon the value for the column.
• As SERIAL always generates a sequence of integers, it is important to set that no null value is added as an ID to any column. This is done by adding the NOT NULL constraint.
• Finally, it is needed to set the owner of the sequence for the ID column. These IDs are automatically deleted if the column or table is dropped. 

PostgreSQL supports 3 serial pseudo-types namely SMALLSERIAL, SERIAL, and BIGSERIAL which has the following characteristics: 

Name	Storage Size	Range
SMALLSERIAL	2 bytes	1 to 32, 767
SERIAL	4 bytes	1 to 2, 147, 483, 647
BIGSERIAL	8 bytes	1 to 9, 223, 372, 036, 854, 775, 807

Example 1:
First, we will create a animals table with the id column as the SERIAL column as below: 

CREATE TABLE animals(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

Now while inserting the values to the table one can use anyone of the below commands: 

INSERT INTO animals(name) 
VALUES('Dog');

or, 

INSERT INTO animals(id, name) 
VALUES(DEFAULT, 'Dog');

So let’s add some rows to our animal table using the command below: 

INSERT INTO animals(name) 
VALUES
      ('Dog'),
      ('Cat'),
      ('Cow'),
      ('Tiger'),
      ('Snake'),
      ('Bull');

Now check if ids are auto-assigned to each row by using the below command: 

SELECT * FROM animals;

Output:

Example 2:
First, we will create a fruits table with the id column as the SERIAL column as below: 

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

Now while inserting the values to the table one can use anyone of the below commands: 

INSERT INTO fruits(name) 
VALUES('Apple');

or, 

INSERT INTO fruits(id, name) 
VALUES(DEFAULT, 'Apple');

So let’s add some rows to our fruits table using the command below: 

INSERT INTO fruits(name) 
VALUES
      ('Apple'),
      ('Banana'),
      ('Cherry'),
      ('Mango'),
      ('Grapes'),
      ('Orange');

Now check if ids are auto-assigned to each row by using the below command: 

SELECT * FROM fruits;

Output:

My Personal Notes arrow_drop_up

Save