BIGINT Data Type in Postgres

An integer data type represents some range of mathematical integers. An integer may or may not be allowed to contain negative values. PostgreSQL, being a modern database engine, allows to store various data types including integers.

In this guide, we will focus on the BIGINT data type in PostgreSQL.

PostgreSQL Integers

Mathematically, an integer is defined as a whole number: positive/negative number (including 0). When we’re trying to store an integer in the memory of a computer, it takes up some space. Depending on the size of an integer, the required size differs.

Postgres comes with three different integer data types:

SMALLINT: 16-bit integer data
INTEGER: 32-bit integer data
BIGINT: 64-bit integer data

The BIGINT integer data type occupies 64-bit (or 8 bytes) of storage. The value can be from -2⁶³ to 2⁶³-1. For most use cases, the range is big enough to hold any integer value.

However, BIGINT comes with several drawbacks. Since it uses such a big amount of memory for every single entry, it may lead to high memory usage. It can also lead to slower performance. This is why BIGINT should only be used when necessary and not out of luxury.

Prerequisites

To perform the steps demonstrated in this guide, you will need the following components:

A properly-configured Linux system. For practice, it’s common practice to spin a new virtual machine. Learn more about installing Ubuntu in VirtualBox.
A functioning PostgreSQL installation. Learn more about installing PostgreSQL on Ubuntu.

Demonstration of BIGINT Usage

In this section, we will have a quick demonstration of using BIGINT to store the data of appropriate magnitude.

First, let’s have a quick look at the limitation of the INTEGER data type. Create a new table for the demo:

CREATE TABLE demo_integer (

id SERIAL PRIMARY KEY,

name VARCHAR (255) NOT NULL,

value INTEGER NOT NULL CHECK (value > 0)

);

Now, we’ll try to insert a couple of entries into the table:

INSERT INTO demo_integer(name, value)

VALUES

('within_range', 10000000),

('out_of_range', 99999999999999999);

As the error suggests, the value of out_of_range entry is out of range of what the INTEGER can store. To store the value, we need BIGINT.

We can alter the column type with the following query:

ALTER TABLE demo_integer

ALTER COLUMN value TYPE BIGINT;

Now, try to insert the entries again:

INSERT INTO demo_integer(name, value)

VALUES

('within_range', 10000000),

('out_of_range', 99999999999999999);

Check the result:

$ SELECT * FROM demo_integer;

Conclusion

In this guide, we discussed the various integer data types that PostgreSQL supports including BIGINT. We also showcased the usage of BIGINT in a simple demo. We worked with a demo table to demonstrate the usage of BIGINT. If there are already other tables present in the database, check out the listing of PostgreSQL tables to keep a track of them. PostgreSQL comes with a lot of supported data types. For example, Boolean, numeric, character, date and time, JSON, and more.

Interested in learning more? The PostgreSQL sub-category contains numerous other guides on the various aspects of PostgreSQL. Happy computing!

BIGINT Data Type in Postgres

PostgreSQL Integers

Prerequisites

Demonstration of BIGINT Usage

Conclusion

About the author

Sidratul Muntaha