PostgreSQL Integer Types: SMALLINT, INT, BIGINT

In this tutorial, we will be learning integer types in PostgreSQL which includes SMALLINT, INTEGER and BIGINT. We will go through detailed information about each type and then will see examples. So, let’s dive into it.

Also Read: PostgreSQL – Data Types

Introduction to Integer Types

As said, PostgreSQL provides us with three integer types- SMALLINT, INTEGER, and BIGINT.

Let’s look at the table below to understand the storage size and minimum-maximum limit of each of the integer types.

Integer types store whole numbers, i.e, non-fractional numbers. If you try to store any fractional value or a value outside the range, PostgreSQL will throw an error.

SMALLINT in PostgreSQL

In PostgreSQL, SMALLINT can also be declared as INT2. As shown in the table, smallint takes 2 bytes of storage to store any number in the range of -32768 to +32768.

It is ideal for small integer numbers such as a number of categories(which can not exceed a thousand in most cases), age etc.

Now let’s create a table having a smallint column type.

CREATE TABLE students(
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age SMALLINT NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now insert some data into it. We will first try to insert a value outside the range and see the result. Then we will enter the correct value.

INSERT INTO students(name,age)
VALUES('Tom Holand', 200000);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The above query will return an error “smallint out of range” because the value is not in the range.

INSERT INTO students(name,age)
VALUES('Tom Holand', 20);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now the value is in range, therefore will be inserted successfully.

INTEGER in PostgreSQL

Note that, int4 or int are aliases for the integer type in PostgreSQL. It has a range of -2147483648 to +2147483647 and takes 4 bytes of storage space.

INT type can actually store pretty large numbers such as population, number of activities per user etc. Let’s take an example of it.

CREATE TABLE states(
id SERIAL PRIMARY KEY,
name VARCHAR(100),
population INT NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now we will insert an out-of-range value first and see the result. Then we will insert a valid value.

INSERT INTO states(name,population)
VALUES('Goa', -20000000000);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This query will return an “out of range” error just like the previous one. Let’s try another query.

INSERT INTO states(name,population)
VALUES('Goa', 1458545);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

BIGINT in PostgreSQL

You can also use int8 for the bigint in PostgreSQL. It uses 8 bytes of storage space and has a range of -9223372036854775808 to +9223372036854775807.

There are very less possibilities that you will need a BIGINT type in your application because INTEGER does the work most of the time. Also, BIGINT takes up a lot of storage space and causes a performance issue. Therefore, unless you have a perfect reason, we recommend not using it.

Conclusion

In this tutorial, we have seen different integer types available in PostgreSQL. In real-time applications, you would mostly use the BIGINT or INTEGER type. You will come across the usage of BIGINT rarely unless a solid reason is there. There is no rocket science in the integer types, therefore it is very easy to try, learn and use.

References

PostgreSQL Official Documentation