PostgreSQL – BIGINT Integer Data Type

Last Updated : 05 Nov, 2024

PostgreSQL is a powerful open-source relational database management system that offers various integer data types to suit different needs. One of these is the BIGINT data type, designed for storing very large integers.

In this article, we will explain the details of the BIGINT data type in PostgreSQL, including its characteristics, syntax, practical examples, and its significance in various applications.

PostgreSQL – BIGINT Integer Data Type

The BIGINT data type in PostgreSQL is a signed 64-bit integer that can store a wide range of numerical values. This type requires 8 bytes of storage and can handle values ranging from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

The BIGINT data type is particularly useful for scenarios where we need to store large numerical values that exceed the limits of standard integer types. Understanding when and how to use BIGINT is important for effective database design.

Syntax

variable_name BIGINT

Examples of PostgreSQL BIGINT Integer Data Type

Now let’s look into some examples of use cases of BIGINT integer Data type.

Example 1: Storing the Number of Stars in Galaxies

In this example we will create a table that stores the number of stars in various galaxies by using the below commands. The BIGINT data type is ideal for this purpose due to the large numbers involved.

Query:

CREATE TABLE galaxy 
(
    id SERIAL PRIMARY KEY,
    name VARCHAR (255) NOT NULL,
    stars BIGINT NOT NULL CHECK (stars> 0)
);

INSERT INTO galaxy(name, stars)
VALUES
    ('Milky_Way', 2500000000000),
    ('Bodes', 2700000000000), 
    ('Cartwheel', 1300000000000),  
    ('Comet', 5700000000000);

SELECT * FROM galaxy;

Output

PostgreSQL BIGINT Integer Data Type Example

Explanation:

This query displays the galaxies and their respective star counts, demonstrating how BIGINT can effectively handle large numbers.

Example 2: Storing Scientific Constants

In this example we will create a table that stores the value of various scientific constants by using the below commands:

Query:

CREATE TABLE constants
(
    id SERIAL PRIMARY KEY,
    name VARCHAR (255) NOT NULL,
    value BIGINT NOT NULL CHECK (value> 0)
);

INSERT INTO constants(name, value)
VALUES
    ('Mole', 602213950000000000),
    ('Rydberg_constant', 10973731568525000), 
    ('Bohr_radius ', 13000000000);

SELECT * FROM constants;

Output

PostgreSQL BIGINT Integer Data Type Example

Explanation:

This query displays scientific constants and their respective values, showcasing the ability of BIGINT to store large amounts of data.

Conclusion

The BIGINT data type in PostgreSQL is a powerful tool for storing very large numerical values. However, due to its significant storage requirements and potential performance impacts, it should be used only when necessary. By understanding its characteristics, syntax, and practical applications, we can use BIGINT to enhance our database schema and improve data integrity.

FAQs

What is the integer data type in PostgreSQL?

The integer data type in PostgreSQL, also known as INT, is a 4-byte signed integer that stores whole numbers ranging from -2,147,483,648 to 2,147,483,647.

What is BIGINT vs integer?

BIGINT is an 8-byte signed integer in PostgreSQL, allowing for a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, while integer (INT) is a 4-byte type with a range of -2,147,483,648 to 2,147,483,647.