PostgreSQL - SMALLINT Integer Data Type
Last Updated : 08 Nov, 2024
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.
Similar Reads
PostgreSQL - INTEGER Data Type 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
4 min read
PostgreSQL - Interval Data Type The interval data type in PostgreSQL stores time periods using 16 bytes of storage and supports a range from -178,000,000 years to 178,000,000 years. It provides a precision attribute ('p') that allows you to specify the number of fractional digits retained in the seconds field, enhancing the precis
2 min read
PostgreSQL - BIGINT Integer Data Type PostgreSQL is a powerful open-source relational database management system that offers various integer data types to suit different needs. One of these is the BIGINT data type, designed for storing very large integers. In this article, we will explain the details of the BIGINT data type in PostgreSQ
3 min read
PostgreSQL - NUMERIC Data Type In PostgreSQL, the NUMERIC data type is designed for high-precision number storage by making it ideal for financial and scientific applications where accuracy is critical. It supports a large number of digits both before and after the decimal point, minimizing rounding errors. Understanding the nuan
5 min read
PostgreSQL - TIME Data Type In PostgreSQL, the TIME data type is essential for applications that require precise time tracking, such as scheduling systems and event logging. This data type allows for accurate time-based entries without storing date information. PostgreSQL’s TIME data type also supports fractional seconds for u
4 min read