PostgreSQL: Numeric Data Type
PostgreSQL supports the NUMERIC type to store values with many digits. The
NUMERIC data type is used to store numbers such as monitory amounts or quantities where exact value is required.
Precisionis a total number of digits that can be stored in
Scaleis a number of digits in the fractional part, meaning a number of digits to the right of decimal point.
The precision must be a positive number, while the scale can be zero, positive or negative number. If the scale is zero then NUMERIC can be defined as
You can define
NUMERIC type without specifying precision and scale. In that case, a numeric value of any length can be stored in a column with implementation limits.
If precision is not required then do not use
NUMERIC datatype as calculation on
NUMERIC value is slower compared to integer, float, and double datatypes.
Let us see different examples to understand how
NUMERIC datatype works. Here we are creating the
Product table as below.
CREATE TABLE product ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC(6,2) );
The following inserts some rows to
INSERT INTO Product(name, price) VALUES ('Keyboard', 450.65), ('Monitor', 5000.50), ('Mouse', 200.10);
The following select query returns all rows from
If you try to insert value where precision exceeds the defined precision, PostgreSQL will raise error as below.
INSERT INTO Product(name, price) VALUES ('Printer', 450967.50);
If you try to insert a value where the scale exceeds the defined scale value for the column, PostgreSQL will round off the scale value to the declared scale value as following.
INSERT INTO Product(name, price) VALUES ('Printer', 450.5078);
As you can see below, it rounded off
printer to 2 digits scale value
Along with holding numeric values, Numeric type of PostgreSQL can also hold special value that is Not-a-Number (NaN).
Let's insert new product with
NaN. Note that,
NaN should be enclosed in single quotes (' ')
INSERT INTO Product(name, price) VALUES ('Cable', 'NaN');
The following query select data from
NaN is not equal to any number. However two
NaN values are equal, hence when you compare
NaN it will return true.
NaN is always greater than any other number, you can check it when you query
Product table order by ascending order of
NaN value comes last.