Introduction to PostgreSQL integer Data Type

PostgreSQL is a popular open-source relational database that supports various data types, including integer. Integer is a data type used for storing integer values and can be used to represent positive numbers, negative numbers, or zero. In PostgreSQL, the range of integer is from -2147483648 to 2147483647.

Syntax

In PostgreSQL, integer is used to define the integer data type. For example:

CREATE TABLE mytable (
    id integer,
    age integer
);

In the above example, we create a table named mytable with two integer columns, id and age.

Use Cases

Integer is widely used in PostgreSQL, especially when dealing with numeric data. Here are some common use cases of integer:

Storing integer data such as age, quantity, etc.
Sorting and filtering using integer columns in queries.
Using integer as foreign key columns to establish relationships between two tables.

Examples

Here are two examples of using integer:

Example 1

Create a table named employees with three columns: id, name, and age. Then, we insert three employee records, each with a name and age.

CREATE TABLE employees (
    id integer,
    name varchar(50),
    age integer
);

INSERT INTO employees (id, name, age)
VALUES (1, 'Alice', 25),
       (2, 'Bob', 30),
       (3, 'Charlie', 35);

Now, we can query all employee records whose age is greater than or equal to 30:

SELECT * FROM employees
WHERE age >= 30;

Result:

 id |  name   | age
----+---------+-----
  2 | Bob     |  30
  3 | Charlie |  35

Example 2

Create a table named inventory with three columns: id, name, and quantity. Then, we insert four product records, each with a name and quantity.

CREATE TABLE inventory (
    id integer,
    name varchar(50),
    quantity integer
);

INSERT INTO inventory (id, name, quantity)
VALUES (1, 'Product A', 100),
       (2, 'Product B', 200),
       (3, 'Product C', 300),
       (4, 'Product D', 400);

Now, we can query all product records whose quantity is greater than 200 and sort them in ascending order by quantity:

SELECT * FROM inventory
WHERE quantity > 200
ORDER BY quantity ASC;