Decimal numbers

Wiki / Spatial, temporal and decimal values

On this page

Creating decimals
Equality and ordering
Arithmetic
Functions
Aggregates
Properties, indexes and constraints
Drivers and Bolt
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.

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

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:

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.

The value type system · Scalar functions · Property type constraints