# Decimal numbers

[Wiki](../README.md) / [Spatial, temporal and decimal values](README.md)

**On this page**

- [Creating decimals](#creating-decimals)
- [Equality and ordering](#equality-and-ordering)
- [Arithmetic](#arithmetic)
- [Functions](#functions)
- [Aggregates](#aggregates)
- [Properties, indexes and constraints](#properties-indexes-and-constraints)
- [Drivers and Bolt](#drivers-and-bolt)
- [Upgrading and storage](#upgrading-and-storage)

From version 1.7.2, a `DECIMAL` holds an exact decimal number of up to 38
significant digits, like SQL `DECIMAL(38, s)`. Use it for money, rates and
quantities, where `0.1 + 0.2` has to be `0.3`.

```cypher
RETURN decimal('0.1') + decimal('0.2') = decimal('0.3');   // true
RETURN 0.1 + 0.2 = 0.3;                                     // false (floats)
```

## Creating decimals

`decimal()` builds a decimal from text, an integer, a float or another decimal.

| Call | Result |
|---|---|
| `decimal('12.30')` | `12.30`. Text keeps the scale it was written with |
| `decimal('1.5e3')` | `1500`. Exponents are accepted |
| `decimal(42)` | `42` |
| `decimal(0.1)` | `0.1`. A float becomes the decimal it prints as |
| `decimal('2.345', 2)` | `2.35`. The second argument sets the scale, rounding half away from zero |
| `decimal(7, 2)` | `7.00` |
| `decimal('abc')` | `null`, as `toFloat('abc')` is |
| `decimal(null)` | `null` |

A decimal holds 38 significant digits, so `decimal('1', 38)` (39 digits) is an
error, as are a scale outside 0–38 and a float too large to represent, such as
`decimal(1.0e300)`.

`decimal.scale(d)` returns the digits after the point (`2` for `12.30`) and
`decimal.precision(d)` the significant digits (`4`).

## Equality and ordering

- **Scale is how a decimal prints, not what it is.** `decimal('12.30') =
  decimal('12.3')` is `true`, `count(DISTINCT …)` counts them once, and they
  are the same index key. `toString` keeps the scale: `'12.30'`.
- **Integers** compare exactly: `decimal('5.00') = 5`.
- **Floats** compare as the decimal they print as: `decimal('0.1') = 0.1` is
  `true`.
- `ORDER BY`, `min` and `max` sort decimals together with integers and floats.
- An inline property map compares by type, as it does for integers and floats:
  `MATCH (i {price: decimal('12.3')})` finds a stored `decimal('12.30')`. For
  equality across numeric types, use `WHERE i.price = 12.3`.

## Arithmetic

| Expression | Result |
|---|---|
| `decimal('19.99') * 3` | `59.97`. An integer operand keeps the result a decimal |
| `decimal('10.00') / 4` | `2.50` |
| `decimal('1') / 3` | `0.3333333333333333333` |
| `decimal('10.5') % 3` | `1.5`. The remainder takes the sign of the dividend |
| `-decimal('2.50')` | `-2.50` |
| `decimal('1.5') + 0.25` | `1.75` as a float. A float operand gives a float, as with integers |
| `'£' + decimal('9.99')` | `'£9.99'` |

- Addition, subtraction and multiplication are exact up to 38 digits. Beyond
  that, digits after the point are rounded half away from zero. A result that
  needs more than 38 digits before the point is an error:
  `decimal out of range: needs more than 38 digits`.
- Division keeps about 20 significant digits, at least as many decimal places
  as the larger operand and at most 38. It rounds half away from zero, then
  drops trailing zeros down to the larger operand's scale.
- Dividing by zero is an error (`/ by zero`, `% by zero`), as it is for
  integers, so a decimal is always a finite number.

## Functions

| Function | With a decimal |
|---|---|
| `abs`, `ceil`, `floor` | Return a decimal |
| `round(d)`, `round(d, places)` | Return a decimal, rounded half away from zero; a negative `places` rounds to tens, hundreds and so on |
| `round(d, places, mode)` | Return a decimal, rounded in one of the [rounding modes](../cypher/functions.md#rounding). From version 2.1.3 |
| `sign(d)` | Returns the integer `-1`, `0` or `1` |
| `toInteger(d)` | Truncates toward zero; a value outside the INTEGER range is an error |
| `toFloat(d)` | Returns the nearest float |
| `toString(d)` | Returns the exact text with its scale |
| `sqrt`, `exp`, `log`, trigonometry, similarity | Read the decimal as a float and return a float |
| `apoc.convert.toJson` | Writes a JSON number with the exact digits: `{"price":12.30}` |
| `apoc.meta.type` | Returns `'DECIMAL'` |

## Aggregates

| Values | `sum` | `avg` |
|---|---|---|
| Decimals, with or without integers | Exact decimal | Exact decimal, by the division rule above |
| Any float among them | Float | Float |
| Integers only | Integer | Float |

`min`, `max`, `collect` and the `DISTINCT` forms take decimals too. The
`apoc.coll` numeric helpers such as `apoc.coll.sum` work on integers and
floats.

## Properties, indexes and constraints

- Store decimals as properties on nodes and relationships, on their own or in
  lists and maps.
- Hash, range and composite indexes key decimals by value, so `WHERE i.price =
  12.3`, `WHERE i.price = decimal('12.30')` and `WHERE i.price > 7` all use the
  index, whether the stored values are decimals, integers or floats.
- A unique constraint treats `10.50` and `10.5` as the same value.
- Property type constraints accept `DECIMAL` (or `NUMERIC`) and
  `LIST<DECIMAL NOT NULL>`:

```cypher
CREATE CONSTRAINT FOR (p:Payment) REQUIRE p.amount IS :: DECIMAL;
CREATE (:Payment {amount: decimal('9.99')});   // stored
CREATE (:Payment {amount: 9.99});              // refused: the value is a FLOAT
```

## Drivers and Bolt

A decimal reaches your driver as a string holding its exact digits (`"12.30"`),
so every Bolt driver reads it without losing a digit. Send decimals the same
way, as strings, and convert them in the query:

```cypher
CREATE (:Payment {amount: decimal($amount)})   // $amount = "19.99"
```

## Upgrading and storage

Upgrading to 1.7.2 needs no steps, and existing databases open as they are. A
database starts using the 1.7.2 storage format when it first stores a decimal,
from which point it opens on 1.7.2 and later, and any replica of it or restore
target runs 1.7.2 or later too. A database that never stores a decimal keeps
its earlier format.

## Related articles

[The value type system](../cypher/data-model.md#the-value-type-system) · [Scalar functions](../cypher/functions.md) · [Property type constraints](../indexes/constraints.md#property-type-constraints)