Introduction to PostgreSQL NUMERIC data type

TheNUMERIC type can store numbers with a lot of digits. Typically, you use theNUMERIC type for storing numbers that require exactness such as monetary amounts or quantities.

Here’s the syntax for declaring a column with theNUMERIC type:

column_name NUMERIC(precision, scale)

In this syntax:

• Theprecision is the total number of digits
• Thescale is the number of digits in the fraction part.

The storage type of the numeric type depends on theprecision andscale.

TheNUMERIC type can hold a value of up to131,072 digits before the decimal point16,383 digits after the decimal point.

The scale of theNUMERIC type can be zero, positive, or negative.

PostgreSQL 15 or later allows you to declare a numeric column with a negative scale.

The following declares the price column with the numeric type that can store total numbers with 7 digits, 5 before the decimal points and 2 digits after the decimal point:

priceNUMERIC(7,2)

If you use a negative scale, you can store up to precision + scale digits on the left and no digits on the right of the decimal point. For example:

amount NUMERIC(5,-2)

In this example, you can store up to 7 digits before and 0 digits after the decimal point.

The following example shows how to declare a column of type numeric with a zero scale:

quantity NUMERIC(5, 0)

It’s equivalent to the following declaration that does not explicitly specify the zero scale:

quantityNUMERIC(5)

If you omit precision and scale, they will default to 131072 and 16383, respectively.

NUMERIC

NUMERIC, DECIMAL, and DEC types

In PostgreSQL, theNUMERIC andDECIMAL types are synonyms so you can use them interchangeably:

DECIMAL(p,s)

If you prefer a shorter name, you can use the name DEC because DEC and DECIMAL are the same type:

DEC(p,s)

If precision is not required, you should not use theNUMERIC type because calculations onNUMERIC values are typically slower thanintegers, float, and double precisions.

Special values

Besides the ordinal numeric values, the numeric type has several special values:

• Infinity
• -Infinity
• NaN

These values represent “infinity”, “negative infinity”, and “not-a-number”, respectively.

PostgreSQL NUMERIC data type examples

Let’s take some examples of using the PostgreSQLNUMERIC type.

1) Storing numeric values

If you store a value with a scale greater than the declared scale of theNUMERIC column, PostgreSQL willround the value to a specified number of fractional digits. For example:

First,create a new table calledproducts:

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

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

INSERT INTO products (name, price)VALUES ('Phone',500.215),       ('Tablet',500.214);

Because the scale of theprice column is 2, PostgreSQL rounds the value500.215 up to500.22 and rounds the value500.214 down to500.21 :

The followingquery returns all rows of theproducts table:

SELECT * FROM products;

Output:

id |  name  | price----+--------+--------  1 | Phone  | 500.22  2 | Tablet | 500.21(2 rows)

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

INSERT INTO products (name,price)VALUES('Phone',123456.21);

PostgreSQL issued the following error:

ERROR:numeric field overflowDETAIL:  A fieldwith precision 5, scale2 must roundto anabsolute value less than10^3.

2) PostgreSQL NUMERIC type and NaN

In addition to holding numeric values, theNUMERIC type can also hold a special value calledNaN which stands for not-a-number.

The following example updates the price of product id 1 toNaN :

UPDATE productsSET price = 'NaN'WHERE id = 1;

Notice that you must use single quotes to wrap theNaN as shown in theUPDATE statement above.

The following query returns the data of theproducts table:

SELECT * FROM products;

Output:

id |  name  | price----+--------+--------  2 | Tablet | 500.21  1 | Phone  |    NaN(2 rows)

Typically, theNaN is not equal to any number including itself. It means that the expressionNaN = NaN returnsfalse. You’ll find this implementation [in JavaScript forNaN](https://www.javascripttutorial.net/javascript-nan/).

But in PostgreSQL, twoNaN values are equal. Also,NaN values are greater than regular numbers such as 1, 2, 3. This implementation allows PostgreSQL to sortNUMERIC values and use them in tree-basedindexes.

The following querysorts the products based on prices from high to low:

SELECT * FROM productsORDER BY priceDESC;

Output:

id |  name  | price----+--------+--------  1 | Phone  |    NaN  2 | Tablet | 500.21(2 rows)

The output indicates that theNaN is greater than500.21

Summary

• Use the PostgreSQLNUMERIC data type to store numbers that require exactness.

Last updated onApril 18, 2024

On this page

• Introduction to PostgreSQL NUMERIC data type
• PostgreSQL NUMERIC data type examples
• Summary