PostgreSQL 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:

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.

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:

Syntax of PostgreSQL Serial pseudo-type

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

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:

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).

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):

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:

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:

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:

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.

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.

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:

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:

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:

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:

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.