PostgreSQL: Using the BIGINT Data Type for Large Integers

Understand the `BIGINT` data type in PostgreSQL and when to use it for storing large integer values in your database.

Using the PostgreSQL `BIGINT` Data Type

Understanding PostgreSQL `BIGINT`

In PostgreSQL, `BIGINT` is a data type for storing large integer values. `BIGINT` uses 8 bytes of storage and can represent both signed (positive and negative) and unsigned (non-negative) integers. The signed range is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807; the unsigned range is from 0 to 18,446,744,073,709,551,615. Note that PostgreSQL doesn't directly support unsigned `BIGINT`; the unsigned range is only achievable by applying constraints. The maximum display width is 255 characters.

When to Use `BIGINT`

Use `BIGINT` when you need to store integers that exceed the range of the standard `INT` data type. It's suitable for scenarios involving very large numbers, such as:

• Scientific constants.
• Large counters or identifiers.
• Data where the integer values might exceed the capacity of the `INT` data type.

However, keep in mind that `BIGINT` uses more storage space than `INT`, potentially impacting database performance. Use it only when necessary.

`BIGINT` Syntax in PostgreSQL

The syntax for declaring a `BIGINT` column is:

column_name BIGINT;

Examples: Using `BIGINT` in PostgreSQL

These examples demonstrate using `BIGINT` to store large integer values. We'll create a table and insert data, then query the table to view the results.

Example 1: Fundamental Constants

This example creates a table to store scientific constants, using `BIGINT` for the constant values.

CREATE TABLE Fundamental_constants (
    Serial_number SERIAL PRIMARY KEY,
    Quantity VARCHAR(255) NOT NULL,
    Constant_value BIGINT NOT NULL CHECK (Constant_value > 0)
);

INSERT INTO Fundamental_constants (Quantity, Constant_value) VALUES
('Faraday constant', 96485332890000),
('Rydberg constant', 10973731568525000),
('Speed of light', 29979245800000000),
('Bohr radius', 13000000000);

Example 2: Number of Stars in Galaxies

This example creates a table to store the estimated number of stars in different galaxies, again using `BIGINT` to handle large numbers.

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

INSERT INTO Solar_system (Galaxy_name, Number_of_stars) VALUES
('Milky Way', 50000000000000),
('IC 1101', 100000000000000),
// ... more rows ...
;

Conclusion

The PostgreSQL `BIGINT` data type is suitable for storing large integer values. Use it judiciously, considering its increased storage requirements compared to `INT`. It's a powerful tool for handling data where standard integer types might not be sufficient.