PostgreSQL SMALLINT Data Type
PostgreSQL - SMALLINT
CREATE TABLE order_items (
product_id integer NOT NULL,
discount smallint DEFAULT 0,
status_code smallint NOT NULL
);
In the above SQL command, we are creating a table named order_items with product_id, discount and status_code.
If you look at their data types, discount and status_code are in smallint and product_id is an integer.
So why is that?
A SMALLINT data type has a small storage size of 2 bytes which can store values in -32768 to +32767 range.
In other words, if we want to store values which will not exceed 32767 positive numbers or 32767 negative numbers, then we can use this data type.
But for product_id, numbers can exceed 32767, so we are using INTEGER type.
Please note that this data type can be used to store even negative numbers up to -32768 not less than that or not greater than 32767 value.
This data type is strictly for whole numbers, no decimal numbers, if decimal values are entered, they will be rounded to the nearest whole number.
In our example, the discount column has smallint data type whereas product_id has integer type.
As discount will not exceed 100 percentage, using smallint is beneficial, where as status_code will be less than the smallint range for most of the applications, so we used smallint.