PostgreSQL Integer Data Types

Summary: In this tutorial, you’ll learn about PostgreSQL integer types, including SMALLINT, INTEGER, and BIGINT, to store integers in the database.

Getting started with PostgreSQL integer types #

PostgreSQL supports the following integer data types for storing integers:

• SMALLINT
• INTEGER
• BIGINT

The table below presents the specifications for each integer type:

If you try to insert or update a value beyond the allowed ranges, PostgreSQL will generate an error.

SMALLINT #

The SMALLINT data type requires 2 bytes of storage and can store integer values ranging from -32,767 to 32,767.

In practice, you can use the SMALLINT type for storing small-range integer values like student grades, product quantities, and people’s ages.

For example, the following statement CREATE TABLE statement creates a people table for storing personal information including name and age:

CREATE TABLE people (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age SMALLINT NOT NULL CHECK (
    age >= 1
    AND age <= 150
  )
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the people table, the age column is a SMALLINT column. Because age can be from 1 to 150, we use a CHECK constraint to enforce this rule.

INTEGER #

The INTEGER data type is the most common choice among integer types because it provides the best balance between storage size, range, and performance.

The INTEGER type requires 4 bytes of storage and can store numbers in the range of (-2,147,483,648 to 2,147,483,647).

INT is the synonym of the INTEGER so that you can use them interchangeably.

In practice, you can use the INTEGER data type for a column that stores quite large whole numbers.

For example, the following creates a table that has an INTEGER column:

CREATE TABLE inventories (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

BIGINT #

The BIGINT type requires an 8-byte storage size that can store any number from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

For example, the following creates a table videos for storing video data including video views:

CREATE TABLE videos (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  view_count BIGINT CHECK (view_count > 0)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Notice that the BIGINT data type consumes more storage than SMALLINT and INT and may decrease database performance.

Summary #

• Use PostgreSQL integer types, including SMALLINT, INT, and BIGINT, to store integers in the database.

Quiz #