Serial

In this section, we are going to understand the working of PostgreSQL Serial pseudo-type, which allows us to define auto-increment columns in tables. And we also see examples of the PostgreSQL Serial pseudo-type.

What is PostgreSQL Serial pseudo-type?

In PostgreSQL, we have one particular kind of database object generator known as Serial, which is used to create a sequence of Integers that are frequently used as a Primary key in a table.

The sequence can be generated with the help of the SERIAL pseudo-type, while we are creating a new table, as we can see in the following command:

CREATE TABLE table_name(  

    ID SERIAL  

); 

The PostgreSQL does the following if we provide the SERIAL pseudo-type to the ID column:

• Firstly, PostgreSQL will create a sequence object and then establish the next value created by the sequence as the particular column’s pre-defined value.
• After that, PostgreSQL will enhance a NOT NULL constraint to the ID column since a sequence always produces an integer that is a non-null value.
• At last, PostgreSQL will provide the owner of the sequence to the ID column; as an output, the sequence object is removed when the table or ID column is dropped.

Note: We can use both the command to specify the Serial pseudo-type as both the below command is similar to each other.

CREATE TABLE table_name(  

    ID SERIAL  

);

 CREATE SEQUENCE table_name_ID_seq;  

CREATE TABLE table_name (  

   ID integer NOT NULL DEFAULT nextval('table_name_ID_seq')  

);  

  

ALTER SEQUENCE table_name_ID_seq  

OWNED BY table_name.ID; 

The PostgreSQL Serial pseudo-type has been classified into three types which are as follows:

• SMALLSERIAL
• SERIAL
• BIGSERIAL

We have the following table, which contains all the Serial pseudo-type specification that is supported by PostgreSQL:

Name	Storage Size	Range
SMALLSERIAL	2 bytes	1 to 32767
SERIAL	4 bytes	1 to 2147483647
BIGSERIAL	8 bytes	1 to 9223372036854775807

Syntax of PostgreSQL Serial pseudo-type

The syntax of the PostgreSQL Serial pseudo-type as follows:

variable_name SERIAL  

Examples of PostgreSQL SERIAL type

Let us see different examples to understand how the PostgreSQL Serial pseudo type works.

Note: We can define the PRIMARY KEY constraint for the SERIAL column because the SERIAL type does not indirectly create an index on the column or make the column as the primary key column.

We are creating one new table with the CREATE command’s help and inserting some values using the INSERT command.

In the below example, we are using the CREATE command to generate a Cars table into the Organization database:

CREATE TABLE Cars(  

   Car_id SERIAL PRIMARY KEY,  

   Car_name VARCHAR NOT NULL,  

   Car_model VARCHAR NOT NULL   

); 

Output

The Cars table has been successfully created after executing the above commands, as shown in the below screenshot:

Once the Cars table has been generated, we can insert some values into it using the INSERT command. And we can use the DEFAULT keyword in the INSERT command or omit the column name (Car_id).

INSERT INTO Cars(Car_name, Car_model)   

VALUES('Porche','911 Carrera');

Output

After implementing the above command, we will get the following message, and the value has been inserted successfully into the Cars table:

OR Using the DEFAULT Keyword with the Column name (Car_id):

INSERT INTO Cars(Car_id, Car_name, Car_model)   

VALUES(DEFAULT,'Audi','A8');

Output

On implementing the above command, we will get the following message; the value has been inserted successfully into the Cars table:

As we can see in the above screenshot, the PostgreSQL inserted two rows into the Cars table with the Car_id column values are 1 and 2.

After creating and inserting the Cars table’s values, we will use the SELECT command returns all rows of the Cars table:

SELECT * FROM Cars;  

Output

After successfully implementing the above command, we will get the following result:

We can use the pg_get_serial_sequence() function to get the sequence name of a SERIAL column in a specified table as we can see in the below syntax:

pg_get_serial_sequence('table_name','column_name')  

To get the current value created by the sequence, we can pass a sequence name to the currval() function.

In the following example, we used the currval() function to return the current value produced by the Cars table Car_id_seq object:

SELECT currval(pg_get_serial_sequence('Cars', 'car_id'));  

Output

After implementing the above command, we will get the below output:

We can use the RETURNING Car_id clause into the INSERT command if we want to get those values created by the sequence when we insert a new row into the table.

The below command is used to insert a new row into the Cars table and returns those records generated for the Car_id column.

INSERT INTO Cars(Car_name,Car_model)   

VALUES('Jaguar', 'XK')  

RETURNING Car_id; 

Output

On executing the above command, we will get the following output, which returns the Car_id as 3:

Note:

• As we understood above, the sequence generator operation is not transaction-safe, which implies that each user will get a different value if two parallel database connections try to get the next value from a sequence.
• And the sequence number of that user will be idle and creates a gap in the sequence if one user can roll back the transaction.

Example2

Let us see one more example to learn the Serial pseudo-type in detail.

So, we are going to create another new table as a Vegetables table with the help of the CREATE command into a similar database that is Organization with the Veg_id column as the SERIAL pseudo-type.

CREATE TABLE Vegetables(  

     Veggie_id SERIAL PRIMARY KEY,  

    Veggie_name VARCHAR NOT NULL,  

Veggie_seasons VARCHAR NOT NULL  

); 

Output

The Vegetables table has been successfully created after executing the above commands, as shown in the below screenshot:

Once the Vegetables table has been generated, we will insert some values into it using the INSERT command, and omit the Veggies_id column as shown in the below command:

INSERT INTO Vegetables(Veggie_name,Veggie_seasons)   

VALUES('Broccoli','Spring');

Output

We will get the following message on implementing the above command: the value has been inserted successfully into the Vegetables table.

Or, we can also use the Default keyword and uses the Veggie_id column as shown in the following command:

INSERT INTO Vegetables (Veggie_id,Veggie_seasons, Veggie_seasons)   

VALUES(DEFAULT, 'Sweet Potatoes','Winter'); 

Output

After executing the above command, we will get the below message, which says that either we can use the Default keyword or the ignore the column name, we will get a similar output:

Therefore, we will add some more values to the Cars table with the help of following the command:

 INSERT INTO Vegetables(Veggie_name,Veggie_seasons)   

VALUES('Jalapeno Peppers','Fall'),  

('Cucumbers','Summer'),  

('Winter Squash','Winter'),  

('Snow Peas','Spring'),  

('Black Radish','All seasons'),  

('Pumpkin','Fall'); 

Output

After executing the above command, we will get the below message, which displays that the value has been inserted successfully into the Vegetables table.

After creating and inserting the Vegetables table’s values, we will use the SELECT command to return all rows of the Vegetables table:

 SELECT *   

FROM Vegetables; 

Output

After successfully implementing the above command, we will get the below output:

Overview

In the PostgreSQL Serial pseudo-type section, we have learned Serial pseudo-type functionality, which is mostly used to create an automatic increases column value for a particular table.