INTEGER

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

What is PostgreSQL Integer Data Type?

In PostgreSQL, the INTEGER data type is also known as INT. The PostgreSQL Integer data type has been classified into three types which are as follows:

• INTEGER
• SMALLINT
• BIGINT

In this section, we are going to understand the INTEGER Data type with examples. And we will cover the SMALLINT and BIGINT data type in the PostgreSQL tutorial.

The PostgreSQL Integer data types involves 4 bytes of storage size and store integers in the signed and unsigned ranges.

And the Signed range starts from -2147483648 to 2147483647. And the unsigned range began with 0 to 4294967295. 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.

The INTEGER data type is the most commonly used for range, performance, and size storage as compared to other integer types (smallint, bigint).

The Integer data type is beneficial when we are storing data such as the number of active users on a social media application like (Facebook, Instagram, etc.), and the population of a city, or the country, and so on.

We have the following table, which contains all the Integer data types specification that is supported by PostgreSQL:

Name	Storage Size	Minimum	Maximum
INTEGER	4 bytes	-2147483648	2147483647
SMALLINT	2 bytes	-32768	32767
BIGINT	8 bytes	-9223372036854775808	9223372036854775807

Note: PostgreSQL will raise an error if we try to store a value outside of the given range.

Syntax of PostgreSQL Integer data type

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

variable_name INTEGER

Examples of PostgreSQL Integer data type

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

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

We are going to create Social_site into a javatpoint database by using the CREATE command to store the number of active users on several social media applications.

CREATE TABLE Social_site (  

    Id SERIAL PRIMARY KEY,  

    Website_name VARCHAR (255) NOT NULL,  

    active_users INTEGER NOT NULL CHECK (active_users> 0)  

);

Output

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

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

 INSERT INTO social_site(Website_name, active_users)  

VALUES  

     ('Instagram', 100000000),   

    ('Facebook', 2203000000),  

        ('WhatsApp', 2000000000),  

    ('Twitter', 30000000),   

        ('Youtube', 200000000); 

Output

After executing the above command, we will get the below message:

if the value is beyond the range of Integer data type (2147483647), then the PostgreSQL will show an error as integer out of range.

So, now we will insert the value for the Facebook active user under the range of Integer data type, as we can see in the following command:

INSERT INTO social_site(Website_name, active_users)  

VALUES  

     ('Instagram', 100000000),   

    ('Facebook', 1903000000),  

        ('WhatsApp', 2000000000),  

    ('Twitter', 30000000),   

        ('Youtube', 200000000); 

Output

After executing the above command, we will get the following result, which displays that the value has been inserted successfully into the Social_site table.

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

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

SELECT * FROM Social_site;  

Output

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

Example2

Let us see one more example to learn the Integer data type in detail. So, we are going to create another new table as countries_citizen table by using the CREATE command into a similar database that is javatpoint to store the citizen of several countries:

CREATE TABLE countries_citizen (  

    Id SERIAL PRIMARY KEY,  

    Country_name VARCHAR (255) NOT NULL,  

    citizen INTEGER NOT NULL CHECK (citizen> 0)  

);

Note: We can also use INT instead of INTEGER as both the alternative word of each other.

Output

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

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

INSERT INTO countries_citizen (Country_name, citizen)  

VALUES  

('India', 1380004385),  

('United States', 331000000),   

    ('Indonesia', 252164800),    

    ('China', 1366990000),  

('Brazil',203212000); 

Output

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

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

SELECT *   

FROM countries_citizen; 

Output

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

Overview

In the PostgreSQL Integer data type section, we have learned that it can be the best balance between storage range, performance, and size.