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
SQL Interview Questions Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970's, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join) SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
7 min read
ACID Properties in DBMS In the world of DBMS, transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliability. This is where the ACID prop
8 min read
Introduction of DBMS (Database Management System) A Database Management System (DBMS) is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate, and secure their data effectively. From small application
8 min read
SQL Query Interview Questions SQL or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database
15 min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read
Window Functions in SQL SQL window functions are essential for advanced data analysis and database management. They enable calculations across a specific set of rows, known as a "window," while retaining the individual rows in the dataset. Unlike traditional aggregate functions that summarize data for the entire group, win
7 min read