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.

Syntax:

NUMERIC(precision, scale)

Precision is a total number of digits that can be stored in NUMERIC data type.
Scale is 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 NUMERIC(precision).

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.

Example: A Table with Numeric Type Column

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(6,2)
);

The following inserts some rows to Product table.

Example: Insert Numeric Values

INSERT INTO Product(name, price)
VALUES
	('Keyboard', 450.65),
	('Monitor', 5000.50),
	('Mouse', 200.10);

The following select query returns all rows from Product table.

If you try to insert value where precision exceeds the defined precision, PostgreSQL will raise error as below.

Example:

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.

Example:

INSERT INTO Product(name, price)
VALUES ('Printer', 450.5078);

As you can see below, it rounded off price of printer to 2 digits scale value

PostgreSQL NaN

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 price as NaN. Note that, NaN should be enclosed in single quotes (' ')

Example: Insert NaN

INSERT INTO Product(name, price)
VALUES ('Cable', 'NaN');

The following query select data from Product table.

NaN is not equal to any number. However two NaN values are equal, hence when you compare NaN with 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 price, the NaN value comes last.