SMALLINT

In this section, we are going to understand the working of the PostgreSQL Smallint data type. And we also see examples of the Smallint data type.

What is PostgreSQL Smallint Data Type?

In PostgreSQL, the next integer data type that we are going to understand is SMALLINT.

The SMALLINT data type small range integer involves 2 bytes of storage size and store integers in the signed range and unsigned range.

And the Signed range starts from -32768 to 32767. And the unsigned range began at 0 to 65535. The size parameter is used to define the maximum display width that is 255.

Note: PostgreSQL does not allow us to use the unsigned integer types.

Typically, the SMALLINT data type is used to store only a partial range of records, such as the number of pages in a book, age of a person, and so on, as compared to other built-in numeric data types.

Suppose if a number is beyond the range of the Maximum and Minimum SMALLINT values. The database server does not store the data value, but as an alternative occurring an error message.

Syntax of PostgreSQL SMALLINT data type

The syntax of the PostgreSQL SMALLINT data type is as follows:

variable_name SMALLINT  

Examples of PostgreSQL Smallint data type

Let us see different examples to understand how the PostgreSQL Smallint data type works.

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

In the following example, we are going to create Employee_age into a javatpoint database by using the CREATE command to store employee’s age.

CREATE TABLE Employee_age(  

    Empolyee_id SERIAL PRIMARY KEY,  

    Employee_first_name VARCHAR (255) NOT NULL,  

    Employee_last_name VARCHAR (255) NOT NULL,  

   Employee_age SMALLINT NOT NULL CHECK (Employee_age > 0)  

);

Output

After executing the above command, we will get the below message: The Employee_age table has been successfully created, as shown in the below screenshot:

Once the Employee_age table has been generated, we can insert some values into it using the INSERT command.

 INSERT INTO Employee_age(Employee_first_name, Employee_last_name, Employee_age)  

VALUES  

('Michael', 'Smith',22),  

('Maria', 'Hernandez', 25),   

    ('James', 'Johnson', 34),    

    ('Margaret', 'Clark', 40),  

('Catherine','Wilson',26); 

Output

After executing the above command, we will get the below message: the particular value has been inserted successfully in the Employee_age table.

As we can see in the above screenshot, the multiple values have been inserted successfully into the Employee_age table.

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

SELECT *   

FROM Employee_age;

Output

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

Example2

Let us see one more example to learn the Smallint data type in detail.

So, we are going to create another new table as Book_pages table with the help of CREATE command into a similar database that is javatpoint to store the numbers of pages in a Book_pages table:

CREATE TABLE Book_pages (  

   B_Id SERIAL PRIMARY KEY,  

   Book_name VARCHAR (255) NOT NULL,  

   Pages_in_book SMALLINT NOT NULL CHECK (Pages_in_book > 0)  

); 

Output

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

Note: In the above command, we added a CHECK constraint to implement the number of pages of a book that must be positive, as the pages_in_book column is a SMALLINT column.

Once the Book_pages table has been generated, we will insert some values into it using the INSERT command as shown in the below command:

INSERT INTO Book_pages(Book_name, Pages_in_book)  

VALUES  

    ('The Blind Assassin ', 637),  

    ('the Overstory', 512),   

    ('Cloud Atlas', 528),    

    ('The Stand by Stephen King', 1153); 

Output

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

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

SELECT *   

FROM Book_pages; 

Output

After implementing the above command, we will get the following output as shown in the below screenshot:

Overview

In the PostgreSQL Smallint data type section, we have learned that it stores only a limited range of records such as the number of pages in a book, person’s age.