PostgreSQL: Integer Data Type

PostgreSQL supports integer types: SMALLINT, INTEGER, and BIGINT to store whole number values. Here is a specification of each of these types.

Note that if you try to store value in each of the above types outside its defined range, Postgres will raise an error.

You can use SMALLINT to store small values like the number of students in class, the number of pages in a book, the age of people, etc.

Lets create Book table with pages column is defined as SMALLINT and insert some data to it.

CREATE TABLE Book (
	id SERIAL NOT NULL PRIMARY KEY,
	name VARCHAR(50) NOT NULL,
	pages SMALLINT NOT NULL
);

INSERT INTO Book(name, pages)
VALUES
	('The Power of Passion', 250),
	('How to Win Friends', 300);

Now let's fetch the data from Book table.

Now if you try to insert data in Book table with value of pages column beyond SMALLINT range, PostgreSQL will raise out of range error and will not allow to insert data

INSERT INTO book(name, pages)
VALUES ('Five Points Someone', 	33000);

The INTEGER type is used to store big whole numbers like the population of cities or countries. Note that short-form INT can also be used instead of INTEGER while defining the datatype of the column.

As the BIGINT type requires a lot more storage and decreases the performance of the database, so use it only if you ready need it.