PostgreSQL INTEGER Data Type (original) (raw)

Last Updated : 15 Jul, 2025

In PostgreSQL, the **INTEGER data type is widely used for storing **numerical data efficiently. It is a **4-byte data type that allows us to store **whole numbers within a specified range, making it ideal for various use cases like **population counts, active user statistics, and more.

In this article, we will provide a comprehensive explanation of the **INTEGER data type in **PostgreSQL, including **syntax, **practical use cases, **examples with outputs, and some important considerations when using this **data type in our **PostgreSQL database.

What is PostgreSQL **INTEGER Data Type?

The **INTEGER data type, also known as **INT, is one of the most commonly used **data types in PostgreSQL. It stores **whole numbers (i.e., numbers without decimal points) and requires **4 bytes of storage. The range of values it can store is between **-2,147,483,648 to **2,147,483,647.

This makes it suitable for **various applications, such as:

• Storing the **population of a country.
• Tracking **user counts on websites or social media platforms.
• Representing counts in **inventory management.

**Syntax

variable_name INTEGER

Examples of PostgreSQL INTEGER Data Type

Now let's look into some examples of use cases of INTEGER Data Type****,** which can be applied in a variety of **real-world scenarios where whole numbers are needed, such as **tracking inventory, **user counts, and more.

**Example 1: Storing Country Population Data

In this example, we will create a table that stores the population of various countries by using the below commands. This approach is ideal for storing large numerical values like national populations efficiently.

**Query:

CREATE TABLE countries_population(
country_id SERIAL PRIMARY KEY,
name VARCHAR (255) NOT NULL,
population INTEGER NOT NULL CHECK (population> 0)
);

INSERT INTO countries_population(name, population)
VALUES
('India', 1352600000),
('Russia', 14450000),
('Canada', 37600000),
('Japan', 126500000);

SELECT * FROM countries_population;

**Output

**Explanation:

In this case, the **population column is of type **INTEGER, ensuring that the stored data represents whole numbers only. We also use the **CHECK constraint to enforce that the population value must always be **greater than zero, ensuring valid data.

**Example 2: Storing Active Users on Social Media Platforms

In this example, we will create a table that **stores the **number of active users on various social media apps by using the below commands. We also use the CHECK constraint to **enforce that the population value must always be **greater than zero, ensuring valid data.

**Query:

CREATE TABLE social_media(
id SERIAL PRIMARY KEY,
name VARCHAR (255) NOT NULL,
active_users INTEGER NOT NULL CHECK (active_users> 0)
);

INSERT INTO social_media(name, active_users)
VALUES
('Facebook', 249279585),
('Twitter', 330000000),
('Instagram', 1070000000),
('Linkedin', 210000000);

SELECT * FROM social_media;

**Output

**Explanation:

In this case, the **population column is of type **INTEGER, ensuring that the stored data represents whole numbers only. The **SERIAL data type for the **country_id field automatically generates **unique identifiers for each row, making it easier to **reference and **maintain the records.

Important Points About PostgreSQL INTEGER Data Type

• When no value is specified, the **INTEGER**type defaults to **NULL**unless a **NOT NULL**constraint is applied.
• **PostgreSQL offers the **SERIALpseudo-type which is an auto-incrementing INTEGER. This is handy for creating unique identifiers.
• **INTEGER**operations are generally faster than operations on larger numeric types (**BIGINT, **NUMERIC).
• While **INTEGER**can store up to **2,147,483,647, calculations or intermediate results exceeding this range can cause **overflow errors. Consider using **BIGINT**for operations where values may exceed this range.

Conclusion

The **INTEGER data type in PostgreSQL is a highly **flexible and **efficient choice for storing whole numbers in our database. It is widely used in applications that require the representation of **counts, population **statistics, and more. However, if our application requires values beyond the **INTEGER range, consider using BIGINT. By following best practices for defining and using **INTEGER in PostgreSQL, we can ensure both **data accuracy and performance in our **database applications.