PostgreSQL provides the INTEGER or INT data type that occupies 32-bit(4 bytes) of memory. It is the common data type that is utilized for storing numeric values. Its range starts from -2,147,483,648 to +2,147,483,647 numbers. The INTEGER data type is classified into multiple types, i.e., INTEGER, BIGINT, and SMALLINT. These data types have different storage sizes and ranges for storing numeric values. Users can not store the values that exceed the allocated range of a particular data type otherwise they will encounter an error.

Today, we will guide you about the Postgres INTEGER data type along with different examples. Let's start with the first example.

How to Use INTEGER | INT Data Type in Postgres

You must follow the below-given syntax to create a table column with INTEGER data type:

CREATE TABLE tableName(
columnName INTEGER constraint
);

Step 1: Create a Column With INTEGER Data Type in PostgreSQL

First of all, let’s create a table in the PostgreSQL database using the “CREATE TABLE” statement:

CREATE TABLE school_info( 
std_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
fee INT NOT NULL);

The table consists of three columns: std_id, name, and fee. The std_id and fee columns are initialized with the INTEGER data type to store the numerical values while the name column is of type TEXT:

Now, you can verify the table's structure through the “SELECT” statement, as follows:

SELECT * FROM school_info;

The output shows that the “std_id”, “name” and “fee” columns have been created with integer, text, and integer data types, respectively.

Step 2: Insert INTEGER Data to a Table in PostgreSQL

This section illustrates how to use the “INSERT INTO” statement to add values in the integer-type columns like “std_id” and “fee” columns:

INSERT INTO school_info(std_id, name, fee)
VALUES(1, 'Peter', 4500),
(2, 'Harry', 2000),  
(3, 'Willey',5000), 
(4, 'Henry', 1700),
(5, 'John', 5000);

The integer values have been successfully inserted in the “std_id” and “fee” columns of the table. To display all values of the table, use the “SELECT *” statement followed by the table name as shown in the below statement:

SELECT * FROM school_info;

This way, users can insert the integer data into any specific table.

How to Perform Mathematical Operations on INTEGER Data Type

You can invoke built-in mathematical methods and operators to perform different functionalities on the integer data. For example, you can use operators like "+", "-", "/", etc., to perform basic arithmetic operations. Similarly, you can employ methods like FACTORIAL(), MOD(), ABS(), etc., to perform intermediate to advanced mathematical operations. Let's consider a code example to learn the working of these methods and operators on INT data type.

Example: Performing Mathematical Operations on INTEGERS

In the below code, we use the built-in "DIV()" function to half the students' free. Also, we use the "+" operator to add 500 hundred in the fee column:

SELECT *, 
DIV(fee, 2) AS half_fee, 
fee + 500 AS modified_fee
FROM school_info;

Let's execute the code and see how the stated operators and methods work in Postgres:

This is how the integer/int data type works in Postgres.

Conclusion

In PostgreSQL, the INTEGER data type is used to store the numerical data between the range of -2,147,483,648 to +2,147,483,647 numbers. This data type is most widely utilized for storing numbers in tables. This article has explained the usage of INTEGER data type along with practical implementation in the PostgreSQL database.