Summary: this tutorial introduces you to the PostgreSQL NUMERIC type for storing numbers with very large number of digits.
Introduction to PostgreSQL NUMERIC data type
NUMERIC type can store numbers with many digits. Typically, you use the
NUMERIC type for monetary or other amounts which precision is required.
To declare a column of type
NUMERIC, you use the following syntax:
The precision is the total number of digits, while scale is the number of digits in the fraction part. For example, the number
1234.567 has a precision of seven and a scale of three.
NUMERIC value can have up to
131,072 digits before the decimal point
16,383 digits after the decimal point.
The scale of the
NUMERIC type can be zero or positive, so the following syntax defines a
NUMERIC column with scale of zero:
If you omit both precision and scale, you can store any precision and scale up to the limit of the precision and scale mentioned above.
In PostgreSQL, the
DECIMAL types are equivalent and both of them are also a part of SQL standard.
If precision is not required, you should not use the
NUMERIC type because calculation on
NUMERIC values are slower than integers, floats and double precision.
PostgreSQL NUMERIC examples
Storing numeric values
If you store a value with the scale greater than the declared scale of the
NUMERIC column, PostgreSQL will round the value to the specified number of fractional digits. See the following example.
First, create a new table named
products for the demonstration:
CREATE TABLE IF NOT EXISTS products (
id serial PRIMARY KEY,
name VARCHAR NOT NULL,
price NUMERIC (5, 2)
Second, insert some
products with the prices whose scales exceed the scale declared in the
INSERT INTO products (NAME, price)
Because the scale of the
price column is two, PostgreSQL rounds the value
500.215 up to
500.216 and rounds the value
500.214 down to
The following query returns all rows of the
In case you store a value whose precision exceeds the declared precision, PostgreSQL will raise an error as show in the following example:
INSERT INTO products (name, price)
In this example, PostgreSQL issued the following error:
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.
PostgreSQL NUMERIC and NaN
Besides storing numeric values, the
NUMERIC column also can store a special value called not-a-number or
SET price = 'NaN'
id = 1;
In this example, we updated the price of the product whose id is 1 to
NaN. Note that you must use quotes around
'NaN' as shown in the
UPDATE statement above.
NaN is not equal to any number including itself. It means that the expression
NaN = NaN returns false. However, PostgreSQL treats
NaN values are equal and
NaN is greater than any non-NaN value. This implementation allows PostgreSQL to sort
NUMERIC values and use them in the tree-based indexes.
The following query sorts the products based on prices:
As you can see, the
NaN is greater than
In this tutorial, you have learned about PostgreSQL
NUMERIC data types and how to use
NUMERIC column for storing values that precision is required.