Introduction to PostgreSQL smallint Data Type

In PostgreSQL, smallint is a fixed-length integer data type used to store integers in the range of -32768 to 32767. It is commonly used for storing integer values such as age or quantity. Compared to other integer types, smallint typically requires less storage space because it uses fewer bits.

Syntax

The syntax for smallint data type in PostgreSQL is as follows:

smallint

Use Cases

smallint data type is typically used in scenarios where integer values need to be stored. It generally requires less storage space than integer or bigint data types. When designing table structures, the most appropriate integer type should be chosen based on the requirements to save storage space and improve query performance.

For example, if a table needs to store quantities that will not exceed the range of smallint data type, then smallint data type can be used. This will help reduce storage space and improve query performance.

Examples

Here are two examples of using smallint data type.

Example 1

Let’s assume we have a table called product with the following columns:

CREATE TABLE product (
    id serial primary key,
    name varchar(50),
    price smallint
);

We can insert data into the product table as follows:

INSERT INTO product (name, price)
VALUES
    ('product1', 50),
    ('product2', 70),
    ('product3', 30);

Now, we can query the product table to find products with prices less than 60:

SELECT * FROM product
WHERE price < 60;

The query result will be:

 id |   name   | price
----+----------+-------
  1 | product1 |    50
  3 | product3 |    30
(2 rows)

Example 2

Let’s assume we have a table called employee with the following columns:

CREATE TABLE employee (
    id serial primary key,
    name varchar(50),
    age smallint
);

We can insert data into the employee table as follows:

INSERT INTO employee (name, age)
VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 22);

Now, we can query the employee table to find employees with age greater than 25:

SELECT * FROM employee
WHERE age > 25;