BIGINT

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

What is PostgreSQL Bigint Data Type?

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

The BIGINT data types are large-range integers, which involve 8 bytes of storage size and store integers in the signed range and unsigned range.

And the Signed range starts from -9223372036854775808 to 9223372036854775807. And the unsigned range began with 0 to 18446744073709551615. 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.

Generally, the BIGINT data type is used to store a vast range of records such as the scientific constants, the number of stars in a galaxy, etc.

Note:

• Generally, we do not prefer to use the Bigint data type as it takes lots of storage and reducing the performance of the database server.
• However, if the integer values might surpass the range given by the int or integer data type, then only we go for the PostgreSQL bigint data type as it is an extensive range of integer.

Syntax of PostgreSQL BIGINT data type

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

variable_name BIGINT  

Examples of PostgreSQL BIGINT data type

Let us see different examples to understand how the PostgreSQL BIGINT 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 will create Fundamental_constants into a javatpoint database by using the CREATE command to store the values of serval scientific fundaments constants.

CREATE TABLE Fundamental_constants(  

    Serial_number SERIAL PRIMARY KEY,  

    Quantity VARCHAR (255) NOT NULL,  

    Constant_value BIGINT NOT NULL CHECK (Constant_value> 0)  

); 

Output

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

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

 INSERT INTO Fundamental_constants(Quantity, Constant_value)  

VALUES  

    ('Faraday constant', 96485332890000),  

        ('Rydberg constant', 10973731568525000),  

    ('speed of light', 29979245800000000),  

        ('Bohr_radius', 13000000000); 

Output

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

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

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

SELECT *   

FROM Fundamental_constants;

Output

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

Example2

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

So, we are going to create another new table as Solar_system table with the help of CREATE command into a similar database that is javatpoint to store the numbers of stars in several solar system or galaxies:

CREATE TABLE Solar_system(  

    Serial_number SERIAL PRIMARY KEY,  

    Galaxy_name VARCHAR (255) NOT NULL,  

    Number_of_stars BIGINT NOT NULL CHECK (Number_of_stars> 0)  

);

Output

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

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

INSERT INTO Solar_system(Galaxy_name, Number_of_stars)  

VALUES  

        ('Milky_Way', 50000000000000),  

        ('IC 1101',100000000000000),  

    ('Comet', 5700000000000),  

    ('Bodes', 2700000000000),   

     ('Cartwheel', 1300000000000);  

Output

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

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

SELECT *   

FROM Solar_system; 

Output

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

Overview

In the PostgreSQL Bigint data type section, we have learned that it stores only an extensive range of records such as the number of stars in the solar system, constants.