PostgreSQL – SERIAL
Last Updated :
06 Jul, 2022
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:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...