Open In App

PostgreSQL – SMALLINT Integer Data Type

Last Updated : 28 Aug, 2020
Improve
Improve
Like Article
Like
Save
Share
Report

PostgreSQL allows a type of integer type namely SMALLINT. It requires 2 bytes of storage size and can store integers in the range of -37, 767 to 32, 767. It comes in handy for storing data like the age of people, the number of pages in a book, etc.

Syntax: variable_name SMALLINT

Now let’s look into some examples of use cases of SMALLINT integer type.
Example 1:
In this example we will create a table that stores the number of pages in a book by using the below commands:

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR (255) NOT NULL,
    pages SMALLINT NOT NULL CHECK (pages > 0)
);

Now let’s add some data to the table using the below command:

INSERT INTO books(title, pages)
VALUES
    ('Jumanji', 600),
    ('Insurgent', 7530), 
    ('Nottingham', 8657),  
    ('Dracula', 3000);

Now let’s check our inserted data using the below commands:

SELECT * FROM books;

Output:

Example 2:
In this example we will create a table that stores the ages of students by using the below commands:

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)
);

Now let’s add some data to the table using the below command:

INSERT INTO student_age(first_name, last_name, age)
VALUES
    ('Raju', 'Kumar', 25),
    ('Nikhil', 'Aggarwal', 21), 
    ('Baccha', 'Yadav', 45),  
    ('Geeta', 'Devi', 30);

Now let’s check our inserted data using the below commands:

SELECT * FROM student_age;

Output:


Similar Reads

PostgreSQL - INTEGER Data Type
PostgreSQL allows a type of integer type namely INTEGER. It requires 4 bytes of storage size and can store integers in the range of -2, 147, 483, 648 to 2, 147, 483, 647. It comes in handy for storing data like the population of a country, the number of active users on a social media app, etc. Syntax: variable_name INTEGER Now let's look into some
2 min read
PostgreSQL - BIGINT Integer Data Type
PostgreSQL allows a type of integer type namely BIGINT. It requires 8 bytes of storage size and can store integers in the range of -9, 223, 372, 036, 854, 775, 808 to +9, 223, 372, 036, 854, 775, 807. Using BIGINT type is not only consuming a lot of storage but also decreasing the performance of the database, therefore, you should have a good reaso
2 min read
PostgreSQL - Connect To PostgreSQL Database Server in Python
The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python setup.py build sudo python setup.py installCreate a
4 min read
PostgreSQL - Export PostgreSQL Table to CSV file
In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine. For Server-Side Export: Use the below syntax to copy a PostgreSQL table from the server itself: Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER; Note: If you have permissio
2 min read
PostgreSQL - Installing PostgreSQL Without Admin Rights on Windows
If you are a part of a corporation, it is highly unlikely that you have the admin privileges to install any external software. But the curious souls that all software developers are, in this article, we will see the detailed process of installation of PostgreSQL without having administrator rights on our Windows machine. Installation: Follow the be
4 min read
PostgreSQL - Boolean Data Type
In this article, we will look into the PostgreSQL Boolean data type and it's implementation while designing a database table. PostgreSQL boolean data type has 3 states namely TRUEBOOL. The below table depicts all valid literal values for TRUE and FALSE in PostgreSQL: True False true false 't' 'f' 'true' 'false' 'y' 'n' 'yes' 'no' 1 0 Now, let's loo
2 min read
PostgreSQL - CHAR Data Type
PostgreSQL supports a character data type called CHAR. This data type is used to store character of limited length. It is represented as char(n) in PostgreSQL, where n represents the limit of the length of the characters. If n is not specified it defaults to char(1) or character(1). Any attempt to store a longer string in the column that defined wi
2 min read
PostgreSQL - VARCHAR Data Type
PostgreSQL supports a character data type called VARCHAR. This data type is used to store characters of limited length. It is represented as varchar(n) in PostgreSQL, where n represents the limit of the length of the characters. If n is not specified it defaults to varchar which has unlimited length. Any attempt to store a longer string in the colu
2 min read
PostgreSQL - TEXT Data Type
PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same. Syntax: variable_name TEXT Example 1: Let's create a new table(say, text_test) for the demonstration using the below commands
2 min read
PostgreSQL - NUMERIC Data Type
PostgreSQL supports the NUMERIC type for storing numbers with a very large number of digits. Generally NUMERIC type are used for the monetary or amounts storage where precision is required. Syntax: NUMERIC(precision, scale) Where, Precision: Total number of digits. Scale: Number of digits in terms of a fraction. The NUMERIC value can have up to 131
2 min read