Understanding PostgreSQL SMALLINT: Storage, Range, and Performance

This in-depth guide delves into the technical details of the PostgreSQL SMALLINT data type, including its storage characteristics, range limitations, and performance implications. Ideal for database developers and administrators. #PostgreSQL #SQL #DataType #SMALLINT #Database #Performance

PostgreSQL SMALLINT Data Type

What is PostgreSQL SMALLINT?

In PostgreSQL, SMALLINT is an integer data type that uses 2 bytes of storage. It stores integers within a specific range. This range can be signed (from -32768 to 32767) or unsigned (from 0 to 65535). The maximum display width is 255 characters. Note: PostgreSQL doesn't support unsigned integer types directly.

SMALLINT is typically used for storing a limited range of numbers, like the number of pages in a book or a person's age. If you try to store a number outside the SMALLINT range, the database will return an error.

Syntax of PostgreSQL SMALLINT

The syntax is simple:

variable_name SMALLINT

Examples of PostgreSQL SMALLINT

Example 1: Employee Age

Let's create a table called Employee_age to store employee ages:

CREATE TABLE Employee_age (
    Empolyee_id SERIAL PRIMARY KEY,
    Employee_first_name VARCHAR(255) NOT NULL,
    Employee_last_name VARCHAR(255) NOT NULL,
    Employee_age SMALLINT NOT NULL CHECK (Employee_age > 0)
);

This creates a table with an employee ID, first name, last name, and age (ensuring the age is greater than 0).

Now, let's insert some data:

INSERT INTO Employee_age (Employee_first_name, Employee_last_name, Employee_age)
VALUES
('Michael', 'Smith', 22),
('Maria', 'Hernandez', 25),
('James', 'Johnson', 34),
('Margaret', 'Clark', 40),
('Catherine', 'Wilson', 26);

Finally, let's retrieve the data:

SELECT * FROM Employee_age;

Example 2: Book Pages

Let's create another table, Book_pages, to store the number of pages in books:

CREATE TABLE Book_pages (
    B_Id SERIAL PRIMARY KEY,
    Book_name VARCHAR(255) NOT NULL,
    Pages_in_book SMALLINT NOT NULL CHECK (Pages_in_book > 0)
);

The CHECK constraint ensures that the number of pages is always positive.

Insert some data:

INSERT INTO Book_pages (Book_name, Pages_in_book)
VALUES
('The Blind Assassin ', 637),
('the Overstory', 512),
('Cloud Atlas', 528),
('The Stand by Stephen King', 1153);

And retrieve the data:

SELECT * FROM Book_pages;

Overview

The PostgreSQL SMALLINT data type is useful for storing smaller integer values within a defined range. Remember its limitations when choosing it for your database design.