BIGINT is a numeric data type in PostgreSQL that stores the integer type values. One of the following data types is used to store a whole number in PostgreSQL: SMALLINT, INTEGER, or BIGINT. All these data types differ in storage size and range. If we talk about the BIGINT data type, it is used to store large values.

This write-up will present an in-depth sight of the Postgres BIGINT data type. So, let’s start!

What is the Need For BIGINT Data Type in PostgreSQL?

In Postgres, the most common data type to store a numeric value is INT or INTEGER. So, a question that thrills our minds is what is the need for BIGINT when we already have an INT data type to store the numeric values? Well! The below-listed points can fix your queries:

- The SMALLINT requires 2, INT requires 4, and BIGINT requires 8 Bytes of storage size.

- The SMALLINT data type stores a number within the range -32,768 to +32,767.

- The INT/INTEGER stores values between -2,147,483,648 to +2,147,483,647.

- The BIGINT data type stores the values between -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

Considering the figures given above, we can conclude that the BIGINT data type is used to store the larger numeric values. For instance, you have to store a numeric value that is out of the range of the INTEGER data type. In such cases, you can use the BIGINT data type.

Note: Use the BIGINT data type only when you have a valid cause(when you require more storage) to use it. This is because the BIGINT data type consumes more storage and slows down the database performance.

Up till now, this write-up explained why and when to use the BIGINT data type in PostgreSQL. Now without wasting any time, we will jump into the practical implementation of the BIGINT data type.

Example #1: How to Create a Column With BIGINT Data Type?

Let’s create a table named “bigint_example” with three columns: id, name, and amount. Let’s set the data type of the id column as INT, TEXT for the name column, and BIGINT for the amount column:

CREATE TABLE bank_details( 
id INT PRIMARY KEY,
name TEXT NOT NULL,
amount BIGINT NOT NULL 
);

The table named bank_details has been created. Let’s verify the table creation using the SELECT command:

SELECT * FROM bank_details;

Three columns with the desired data types have been created successfully.

Example #2: How to Insert BIGINT Data to a Table in Postgres?

Let’s insert some data into the newly created table named bank_details:

INSERT INTO bank_details(id, name, amount)
VALUES
(1, 'JPMorgan Chase & Co.', 3000000000000),
(2, 'Bank of America Corp.', 2520000000000), 
(3, 'Wells Fargo & Co.', 1780000000000), 
(4, 'Citigroup Inc.', 1670000000000),
(5, 'U.S. Bancorp', 564000000000);

The output shows that five records have been inserted into the bank_details table. Let’s verify/check the table’s data using SELECT statement:

SELECT * FROM bank_details;

The output verifies that all the data has been inserted into the bank_details table.

Conclusion

BIGINT is a numeric data type in PostgreSQL that stores the integer type values. It stores a value between -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. However, it is recommended that the BIGINT data type should be used only when you have a valid cause(when you require more storage) to use it. This is because the BIGINT data type consumes more storage and slows down the database performance. This write explained how to use the BIGINT data type in PostgreSQL.