An Overview Of PostgreSQL NUMERIC Type

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

The 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.

The 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 NUMERIC and 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:

Second, insert some products with the prices whose scales exceed the scale declared in the price column:

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 500.21 :

The following query returns all rows of the products table:

In case you store a value whose precision exceeds the declared precision, PostgreSQL will raise an error as show in the following example:

In this example, PostgreSQL issued the following error:

PostgreSQL NUMERIC and NaN

Besides storing numeric values, the NUMERIC column also can store a special value called not-a-number or NaN.

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.

Typically, the 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 500.21

In this tutorial, you have learned about PostgreSQL NUMERIC data types and how to use NUMERIC column for storing values that precision is required.