August 2, 2023

**Summary**: in this tutorial, you will learn about the PostgreSQL `NUMERIC`

type for storing numeric data.

**Table of Contents**

## Introduction to PostgreSQL NUMERIC data type

The `NUMERIC`

type can store numbers with a lot of digits. Typically, you use the `NUMERIC`

type for numbers that require exactness such as monetary amounts or quantities.

The following illustrate the syntax of the `NUMERIC`

type:

```
NUMERIC(precision, scale)
```

In this syntax, the `precision`

is the total number of digits and the `scale`

is the number of digits in the fraction part. For example, the number `1234.567`

has the precision `7`

and scale `3`

.

The `NUMERIC`

type can hold a value 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. The following shows the syntax of `NUMERIC`

type with scale zero:

```
NUMERIC(precision)
```

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.

```
NUMERIC
```

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 calculations on `NUMERIC`

values are typically slower than integers, floats, and double precisions.

## PostgreSQL NUMERIC examples

Let’s take some example of using the PostgreSQL `NUMERIC`

type.

### 1) Storing numeric values

If you store a value with a scale greater than the declared scale of the `NUMERIC`

column, PostgreSQL will round the value to a specified number of fractional digits.

See the following example.

First, create a new table named `products`

for the demonstration:

```
DROP TABLE IF EXISTS products;
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 the `price`

column:

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

Because the scale of the `price`

column is 2, PostgreSQL rounds the value `500.215`

up to `500.22`

and rounds the value `500.214`

down to `500.21`

:

The following query returns all rows of the `products`

table:

```
SELECT * FROM products;
```

In case 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 overflow
DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.
```

### PostgreSQL NUMERIC type and NaN

In addition to holding numeric values, the `NUMERIC`

type can also hold a special value called `NaN`

which stands for not-a-number.

The following example updates the price of the product id 1 to `NaN`

:

```
UPDATE products
SET price = 'NaN'
WHERE id = 1;
```

Notice that you must use single quotes to wrap the `NaN`

as shown in the UPDATE statement above.

The following query returns the data of the `products`

table:

```
SELECT * FROM products;
```

Typically, the `NaN`

is not equal to any number including itself. It means that the expression `NaN = NaN`

returns false.

However, two `NaN`

values are equal and `NaN`

is greater than other numbers. This implementation allows PostgreSQL to sort `NUMERIC`

values and use them in tree-based indexes.

The following query sorts the products based on prices:

```
SELECT * FROM products
ORDER BY price DESC;
```

As you can see from the output, the `NaN`

is greater than `500.21`

In this tutorial, you have learned about the PostgreSQL `NUMERIC`

data types and how to use `NUMERIC`

column to store numbers.

## See more

PostgreSQL Tutorial: Data Types

PostgreSQL Documentation: Arbitrary Precision Numbers