August 2, 2023
Summary: this tutorial introduces you to various PostgreSQL integer types including
Introduction to PostgreSQL integer types
To store the whole numbers in PostgreSQL, you use one of the following integer types:
The following table illustrates the specification of each integer type:
If you try to store a value outside of the permitted range, PostgreSQL will issue an error.
Unlike MySQL integer, PostgreSQL does not provide unsigned integer types.
SMALLINT requires 2 bytes storage size which can store any integer numbers that is in the range of (-32,767, 32,767).
You can use the
SMALLINT type for storing something like ages of people, the number of pages of a book, etc.
The following statement creates a table named
CREATE TABLE books ( book_id SERIAL PRIMARY KEY, title VARCHAR (255) NOT NULL, pages SMALLINT NOT NULL CHECK (pages > 0) );
In this example, the
pages column is a
SMALLINT column. Because the number of pages of a book must be positive, we added a
CHECK constraint to enforce this rule.
INTEGER is the most common choice between integer types because it offers the best balance between storage size, range, and performance.
INTEGER 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 the
INTEGER 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_id serial PRIMARY KEY, city_name VARCHAR (255) NOT NULL, population INT NOT NULL CHECK (population >= 0) );
INT is the synonym of
In case you want to store the whole numbers that are out of the range of the
INTEGER type, you can use the
BIGINT 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).
BIGINT 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.