PostgreSQL NUMERIC Data Type

PostgreSQL provides NUMERIC types to store numeric data with high precision requirements, such as amounts, miles, sales, etc.

Computations on values of NUMERIC type are generally slower than floating-point types, so if precision is not a requirement, you should use either floating-point types or integers.

PostgreSQL `NUMERIC` syntax

This is the syntax of the NUMERIC type :

NUMERIC(precision, scale)
NUMERIC(precision)
NUMERIC

Explanation:

precision: Optional. It is the total number of digits allowed to be stored, including the number of decimal places.
scale: Optional. The number of digits in the fractional part.

For example, the number 1234.567 has precision 7 and scale 3.

Note that DECIMAL and NUMERIC are equivalent.

PostgreSQL `NUMERIC` value

NUMERIC values are suitable for storing numeric values with a large number of numbers. It has up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.

In addition to ordinary numeric values, there are several special values in NUMERIC type:

'Infinity': gigantic
'-Infinity': negative infinity
'NaN': not a number

Among them, PostgreSQL v14 supports 'Infinity' and '-Infinity'.

PostgreSQL `NUMERIC` Examples

Basic usage

Use the following statement to create a table named test_numeric:

CREATE TABLE test_numeric (
  numeric_5_2 NUMERIC(5,2),
  numeric_i NUMERIC
);

Insert a row into the table using the following statement :

INSERT INTO test_numeric (numeric_5_2)
VALUES (1234.456)
RETURNING numeric_5_2;

ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.

Here, PostgreSQL gives an error because the number 1234.234 is out of range of NUMERIC(5,2).

The following statement modifies the inserted value, run it to retry the insert:

INSERT INTO test_numeric (numeric_5_2)
VALUES (234.456)
RETURNING numeric_5_2;

 numeric_5_2
-------------
      234.46
(1 row)

Here, since the NUMERIC(5,2) allowed decimal places are 2, 234.456 is rounded to 234.46.

Infinity and NaN

PostgreSQL 14 began to support Infinity and -Infinity. You cannot write Infinity AND -Infinity to a limited NUMERIC, otherwise PostgreSQL will give an error, for example:

INSERT INTO test_numeric (numeric_5_2)
VALUES ('Infinity')
RETURNING numeric_5_2;

ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 cannot hold an infinite value.

The following statement writes a Infinity to a NUMERIC column:

INSERT INTO test_numeric (numeric_i)
VALUES ('Infinity')
RETURNING numeric_i;

 numeric_i
-----------
  Infinity
(1 row)

The following statement writes a -Infinity to a NUMERIC column:

INSERT INTO test_numeric (numeric_i)
VALUES ('-Infinity')
RETURNING numeric_i;

 numeric_i
-----------
 -Infinity
(1 row)

The following statement writes a NaN to a NUMERIC column:

INSERT INTO test_numeric (numeric_5_2)
VALUES ('NaN')
RETURNING numeric_5_2;

 numeric_5_2
-------------
         NaN
(1 row)

Conclusion

PostgreSQL provides the NUMERIC type for storing numeric data with high precision requirements. Due to performance issues, please choose the NUMERIC type only when necessary.

PostgreSQL NUMERIC syntax

PostgreSQL NUMERIC value