PostgreSQL – SMALLINT Integer Data Type
In PostgreSQL, the SMALLINT data type is a compact, efficient way to store integer values within a small range. Using only 2 bytes of storage, SMALLINT is ideal for scenarios where the range of possible values is relatively small, such as the age of individuals or the number of pages in a book.
In this article, we will provide a detailed overview of the PostgreSQL SMALLINT data type, including syntax, usage examples, and best practices for storing data within manageable ranges.
PostgreSQL – SMALLINT Integer Data Type
The SMALLINT data type in PostgreSQL stores small-range integer values and is an efficient choice for fields that would not exceed its range. With a storage requirement of only 2 bytes, SMALLINT can store integer values from -32,768 to 32,767, making it suitable for fields like ages, counts, or scores that do not require large storage.
Syntax
variable_name SMALLINT
Examples of PostgreSQL SMALLINT Data Type
Now let’s look into some examples of use cases of SMALLINT integer type. These examples illustrate the efficiency of SMALLINT for data with a limited numeric range, making it perfect for cases where storage space and performance are key considerations.
Example 1: Storing Number of Pages in a Book
In this example, we create a table called books to store information about different books, including the number of pages each book has. Using SMALLINT for the pages column is suitable because the number of pages usually falls within the range of SMALLINT.
Query:
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title VARCHAR (255) NOT NULL,
pages SMALLINT NOT NULL CHECK (pages > 0)
);
INSERT INTO books(title, pages)
VALUES
('Jumanji', 600),
('Insurgent', 7530),
('Nottingham', 8657),
('Dracula', 3000);
SELECT * FROM books;
Output
Explanation:
The query will display the records with the number of pages for each book. The pages column is defined as SMALLINT, which is efficient for storing the relatively small values required for book page counts.
Example 2: Storing Age of Students
In this example, we will create a student_age table to store student ages. Age values generally fall within the SMALLINT range, making it an appropriate choice for this data.
Query:
CREATE TABLE student_age(
student_id SERIAL PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
age SMALLINT NOT NULL CHECK (age > 0)
);
INSERT INTO student_age(first_name, last_name, age)
VALUES
('Raju', 'Kumar', 25),
('Nikhil', 'Aggarwal', 21),
('Baccha', 'Yadav', 45),
('Geeta', 'Devi', 30);
SELECT * FROM student_age;
Output
Explanation:
The age column uses SMALLINT to efficiently store typical student ages without requiring additional storage space.
Important Points About PostgreSQL SMALLINT Integer Data Type
- Range Consideration: Ensure that values stored do not exceed the range of SMALLINT to avoid overflow errors.
- If no constraints are applied, SMALLINT columns can accept any integer value within its range.
- Ideal Use Cases: Ideal for scenarios where the range of possible values is relatively small, such as ages, counts, or scores.
- Use Constraints for Data Integrity: Use constraints, like CHECK, to enforce valid data ranges and maintain data integrity.
SMALLINT vs. Other Integer Data Types in PostgreSQL
- SMALLINT vs INTEGER: SMALLINT uses 2 bytes of storage, while INTEGER uses 4 bytes and supports a larger range. Use SMALLINT for smaller values to save space, and INTEGER when you need a range of up to approximately ±2 billion.
- SMALLINT vs BIGINT: BIGINT occupies 8 bytes and can store very large integers, up to about ±9 quintillion. Choose BIGINT for exceptionally large numbers, such as transaction IDs or account balances, and SMALLINT for more compact data.
Conclusion
The PostgreSQL SMALLINT data type is a practical and efficient choice for storing small-range integer values. By using SMALLINT, we can optimize storage efficiency and ensure data integrity with constraints. Whether storing ages, page numbers, or other limited-range values, SMALLINT offers an ideal balance of compact storage and functionality.
FAQs
What is a Smallint data type?
Smallint is a data type in PostgreSQL used to store small-range integer values from -32,768 to 32,767, requiring only 2 bytes of storage.
What is the difference between Boolean and Smallint in PostgreSQL?
Boolean represents true/false values and only requires 1 byte, whereas Smallint stores integer values within a defined range, making it suitable for small numeric data but not for binary logic.
What is the difference between Smallint and BIGINT?
Smallint uses 2 bytes and can store values from -32,768 to 32,767, while Bigint uses 8 bytes and has a much larger range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, suitable for large numeric data.
