Introduction to PostgreSQL integer types

To store the whole numbers in PostgreSQL, you can use one of the following integer types:

• SMALLINT
• INTEGER
• BIGINT

The following table illustrates the specification of each integer type:

If you attempt to store a value outside of the permitted ranges, PostgreSQL will issue an error.

UnlikeMySQL integer, PostgreSQL does not provide unsigned integer types.

SMALLINT

TheSMALLINT requires 2 bytes storage size which can store any integer numbers that are in the range of (-32,767, 32,767).

You can use theSMALLINT type for storing something like the ages of people, the number of pages of a book, and so on.

The following statementcreates a table namedbooks:

CREATE TABLE books (    book_idSERIAL PRIMARY KEY,    titleVARCHAR (255)NOT NULL,    pagesSMALLINT NOT NULL CHECK (pages> 0));

In this example, thepages column is aSMALLINT column. Because the number of pages must be positive, we added aCHECK constraint to enforce this rule.

INTEGER

TheINTEGER is the most common choice between integer types because it offers the best balance between storage size, range, and performance.

TheINTEGER type requires 4 bytes storage size that can store numbers in the range of (-2,147,483,648, 2,147,483,647).

You can use theINTEGER type for a column that stores quite big whole numbers like the population of a city or even country as the following example:

CREATE TABLE cities (    city_idserial PRIMARY KEY,    city_nameVARCHAR (255)NOT NULL,    population INT NOT NULL CHECK (population >= 0));

Notice thatINT is the synonym ofINTEGER.

BIGINT

If you want to store the whole numbers that are out of the range of theINTEGER type, you can use theBIGINT type.

TheBIGINT type requires 8 bytes storage size that can store any number in the range of (-9,223,372,036,854,775,808,+9,223,372,036,854,775,807).

UsingBIGINT type is not only consuming a lot of storage but also decreasing the performance of the database, therefore, you should have a good reason to use it.

Summary

• UseSMALLINT,INT, andBIGINT data types to store integers in the database.

Last updated onFebruary 2, 2024

On this page

• Introduction to PostgreSQL integer types
• Summary