PostgreSQL - INTEGER Data Type
Last Updated :
18 Nov, 2024
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
SERIAL
pseudo-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.
Similar Reads
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Introduction of DBMS (Database Management System) A Database Management System (DBMS) is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate, and secure their data effectively. From small application
8 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15 min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read
Window Functions in SQL SQL window functions are essential for advanced data analysis and database management. They enable calculations across a specific set of rows, known as a "window," while retaining the individual rows in the dataset. Unlike traditional aggregate functions that summarize data for the entire group, win
7 min read