Numeric

In this section, we are going to understand the working of PostgreSQL Numeric data types, which allows us to store the numeric data. And we also see examples of Numeric data type.

What is PostgreSQL NUMERIC Data Type?

In PostgreSQL, the Numeric data type is used to store the numbers with various significant numbers of digits. In other words, we can say that the PostgreSQL Numeric data type is used to specify the numeric data into the table, which needs quantities or monetary amounts where the precision is required.

The Numeric data types contain the following:

• four- and eight-byte floating-point numbers
• two-, four-, and eight-byte integers
• Selectable-precision decimals.

The below table contains all Numeric data types that support in PostgreSQL:

Name	Storage size	Range	Description
smallint	2 bytes	-32768 to +32767	stores whole numbers, small range.
integer	4 bytes	-2147483648 to +2147483647	stores whole numbers use this when you want to store typical integers.
bigint	8 bytes	-9223372036854775808 to 9223372036854775807	stores whole numbers, large range.
decimal	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.	user-specified precision, exact
numeric	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.	user-specified precision, exact
real	4 bytes	6 decimal digits precision.	variable-precision, inexact
double precision	8 bytes	15 decimal digits precision	variable-precision, inexact
serial	4 bytes	1 to 2147483647	auto incrementing integer
bigserial	8 bytes	1 to 9223372036854775807	large auto incrementing integer

Syntax of PostgreSQL Numeric data type

The syntax of the PostgreSQL Numeric data type is as follows:

NUMERIC (precision, scale)  

In the above syntax, we have used the following parameters:

Parameter	Description
Numeric	It is a keyword, which is used to store the numeric numbers.
Precision	It is the total number of digits
Scale	It is several digits in terms of the fraction part.

We can understand the concept of precision and scale by seeing in the following example:

Suppose we have the number 2356.78. In this number, the precision is 6, and the scale is 2.

Note: In PostgreSQL, the Numeric data type can have a value of up to 131,072 digits before the decimal point of 16,383 digits after the decimal point. The Numeric data type scale can be Positive (+) Or Zero (0).

The below syntax displays the Numeric data type with Scale ZERO:

NUMERIC (precision)  

And if we ignore both precision and scale, we can have any precision and scale up to the limit of the precision and scale, which mentioned above.

NUMERIC  

Note: In PostgreSQL, the Numeric and Decimal types are corresponding to each other, and both of them also follow the SQL standard.

If we don’t require the precision, we cannot use the Numeric data type since the calculations on Numeric values are usually slower than double precisions, floats, and integers.

Examples of PostgreSQL Numeric data types

Let us see different examples to understand how the PostgreSQL Numeric data type works.

Example of Storing numeric values

The PostgreSQL will round the value to a defined number of fractional digits if we want to store a value more extensive than the declared scale of the Numeric column.

For this, we will create one new table name Items table with the help of the CREATE command and insert some values by using the INSERT command.

Here, we also use the drop command to drop the table if it exists in a particular database.

DROP TABLE IF EXISTS Items;  

We are going to create Items tables by using the CREATE command into the Organization database:

CREATE TABLE Items (  

    item_id SERIAL PRIMARY KEY,  

    item_name VARCHAR(100) NOT NULL,  

    item_price NUMERIC(5,2)  

); 

Output

The Items table has been successfully created after executing the above commands, as shown in the below screenshot:

Once the Items table has been generated, we are ready to insert some items into it by using the INSERT command with their prices whose scales exceed the scale declared in the item_price column:

INSERT INTO Items (item_name, item_price)  

VALUES   

('Mercedes Benz ',300.512),   

 ('Lincoln Continental',300.513),  

 ('Audi A7',300.514); 

Output

After implementing the above command, we will get the following output, which displays that the value has been inserted successfully into the Items table.

Because the scale of the item_price column is 2, PostgreSQL rounds the values 300.512,300.513. 300.514 up to 300.51.

After creating and inserting the Items table’s values, we will use the SELECT command that returns all rows of the Items table:

SELECT * FROM Items;  

Output

After executing the above command, we will get the following result:

If we want to store a value whose precision surpasses the declared precision, then the PostgreSQL will show an error as we can see in the below example:

INSERT INTO Items (item_name, item_price)  

VALUES ('Mercedes Benz',23457.44);  

Output

On executing the above command, we will get the following error that the numeric field overflow, which implies that a field with precision 5 and scale 2 must round to absolute value, which is less than 10^3.

Example of PostgreSQL NUMERIC data type and NaN

Let see on sample example of PostgreSQL Numeric data type and NaN.

If we want to fetch the numeric values, then the PostgreSQL NUMERIC data type can also have a special value called NaN, and the NaN stand for not-a-number

In the below example, we will update the item_price of the item_id 1 to NaN:

UPDATE Items  

SET item_price = 'NaN'  

WHERE item_id = 1; 

Output

Once we implement the above command, we will get the below message window, which displays that the particular value has been updated successfully into the Items table.

Note: In the above Update statement, we have used the single quotes (”) to enclose the NaN.

Now we have used the SELECT command to fetch all the records from the Items table:

SELECT * FROM Items;  

Output

After executing the above statement, we will get the below result:

Generally, the NaN is not equal to any number, including itself, which means that the following expression NaN = NaN will return FALSE.

But, the two NaN values can be equivalent, and NaN is more significant than other numbers. This execution allows PostgreSQL to categorize the Numeric values and use them in tree-based indexes.

To categorize the Items based on their Item_prices, we will use the below command:

SELECT * FROM Items  

ORDER BY item_price DESC;  

Output

On executing the above command, we will get the following output:

As we can see in the above screenshot that the NaN is greater than the value 300.51.

Overview

In the PostgreSQL Numeric data type section, we have learned the following topics:

• The PostgreSQL Numeric data type can use the Numeric column to store numbers.
• The PostgreSQL Numeric data type can be used with NaN(not-a-number).