PostgreSQL is an open-source relational database management system that supports various numeric data types. In this tutorial, we will focus on the NUMERIC data type in PostgreSQL and its use cases, including a real-world example to demonstrate its usage. So without further ado, let’s get started!
Also read: PostgreSQL – Data Types
Introduction to NUMERIC Type in PostgreSQL
The NUMERIC data type, also known as the DECIMAL data type, is used to store numbers with a specified number of decimal places. This data type is particularly useful when precise calculations and high-precision storage are required, such as in financial or scientific applications.
The size of the NUMERIC data type can be specified as NUMERIC(p,s), where p is the total number of digits and s is the number of digits to the right of the decimal point. We will see this in detail in the syntax section.
Note that, PostgreSQL offers us the DECIMAL as well as the NUMERIC types, which are exactly the same and are only synonyms of each other. Therefore, it doesn’t make any difference if you use any of them.
PostgreSQL NUMERIC Type Syntax
The following is the syntax of the NUMERIC type.
NUMERIC(precision, scale)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here,
- Precision– The number of digits to both sides of the decimal point, or the total number of significant digits in the complete number, determines a
numeric precision. This must be a positive value. - Scale – The number of decimal digits in the fractional component, to the right of the decimal point, determines a numeric scale. This can be a positive or negative value. If
For example, the number 123.45 has a precision of 5 and a scale of 2.
The NUMERIC type has an extraordinary storage size, which is up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.
If you want to create a NUMERIC type equivalent to an integer, you can skip the scale parameter as shown below:
NUMERIC(precision)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)On the other hand, if you skip both parameters, the column will be able to store any value without any coercion as shown below:
NUMERICCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Note that, A declaration of a numeric type may only specify a maximum precision of 1000.
It is now possible to declare a numeric column with a negative scale in PostgreSQL 15 and later. Then, decimal points will be left-rounded for values. The precision still relates to the largest possible number of unrounded digits. Therefore, the below syntax
NUMERIC(2, -3)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)will round the values to the nearest thousand value, which is between -99000 and 99000.
Declaring a scale bigger than the indicated precision is also acceptable. Only fractional values can be stored in such a column, and it needs at least the given scale minus the declared precision in the number of zero digits immediately to the right of the decimal point. For example, a column that is
NUMERIC(3, 5)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)will round values to 5 decimal places and can contain values between -0.00999 and 0.00999.
PostgreSQL NUMERIC Data Type Examples
Now let’s see the NUMERIC type in action with some examples.
Note that, if you try to store a value having a greater scale than the specified scale, PostgreSQL will round that value. However, if you try to insert a value having greater precision than the specified precision, PostgreSQL will throw an error. Let’s see it by example.
let’s create a table first called sales.
CREATE TABLE sales (
id serial primary key,
name VARCHAR(100),
amount numeric(5,2)
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Let’s insert some records into it.
Example 1 – Correct Values, Rounding the scale
INSERT INTO sales(name,amount) VALUES
('Shampoo',123.234),
('Conditioner',54.33),
('Hair Gel',50);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Now let’s display the table data.
SELECT * FROM sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the first value is rounded up to the correct scale. Whereas, the third value automatically got the two zeros after the decimal point.
Now let’s try to insert a value with higher precision.
Example 2 – Higher Precision
INSERT INTO sales(name,amount) VALUES
('Face Wash',2999.44);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, we got an error saying the “numeric field overflow”.
Example 3 – Correct precision but Wrong Value
Note that, even if the total number of digits in the value is equal to the precision but the total number of digits before the decimal point is greater than precision minus scale, you will get an error. let’s see an example.
INSERT INTO sales(name,amount) VALUES
('Face Wash',2999.4);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the precision of the value is 5. However, it will not be accepted. Let’s run the query and see.
As you can see, we got an error because the number of digits before the decimal points is greater.
Example 4 – NaN Value
Postgresql allows using the NaN which is a short form for “Not a Number” in a NUMERIC column. It is used to indicate the undefined result.
INSERT INTO sales(name,amount) VALUES
('Face Gel', 'NaN');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Note that, the NaN value must be inserted as a string (inside the quotes).
Another interesting thing, two NaN values are equal, however, NaN = NaN expression returns false. Moreover, a NaN value is always greater than other numbers. Let’s check it by sorting the sales table in descending order.
SELECT * FROM sales ORDER BY amount DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the record with the amount NaN is placed at the top, which means the NaN is treated as the greatest among all numbers.
Conclusion
In this tutorial, we have seen the NUMERIC type in PostgreSQL. By specifying the precision of numbers with the NUMERIC data type, you can ensure that important information like prices, tax rates, and mathematical results are stored accurately. These examples demonstrate the versatility and utility of the NUMERIC data type in different scenarios and illustrate how it can be used to meet the specific needs of a wide range of applications.