Numbers

In SurrealDB, numbers can be one of three types: 64-bit integers, 64-bit floating point numbers, or 128-bit decimal numbers. From SurrealDB 4.0, a number can also be one of the sized number types, such as u8 or f32.

Integer numbers

If a numeric value is specified without a decimal point and is within the range -9223372036854775808 to 9223372036854775807 then the value will be parsed, stored, and treated as a 64-bit integer.

CREATE event SET year = 2022;

Floating point numbers

If a number value is specified with a decimal point, then the number will automatically be parsed, stored, and treated as a 64-bit floating point value. An integer outside the range specified above is a parse error (Failed to parse number: number cannot fit within a 64bit signed integer), so write it with an f or dec suffix, or with a u64 suffix from SurrealDB 4.0. This ensures efficiency when performing mathematical calculations within SurrealDB.

CREATE event SET temperature = 41.5;

Decimal numbers

To opt into 128-bit decimal numbers when specifying numeric values, you can use the dec suffix.

CREATE product SET price = 99.99dec;

The dec suffix is an instruction to the parser and not a cast, and is thus preferred when making a decimal.

-- Creates the imprecise float 3.888888888888889 and casts it into a decimal as 3.888888888888889dec
<decimal>3.8888888888888888;
-- Uses the input 3.8888888888888888 to directly create a decimal
3.8888888888888888dec;

A decimal holds up to 28 digits after the decimal point. A dec literal that needs more, in plain or exponent notation, is a parse error. Zeros after the last significant digit do not count, because the value is still exact, so 1.500000000000000000000000000000dec gives 1.5dec. A string cast with <decimal> is rounded to 28 places instead. For a number that needs more digits, use a bigdecimal.

0.0000000000000000000000000000001dec;

Parse error: Failed to parse decimal: Scale exceeds the maximum precision allowed: 31 > 28

<decimal>"0.0000000000000000000000000000001";

0dec

Using a specific numeric type

To use a specific type when specifying numeric values, you can cast the value to a specific numeric type or use the appropriate suffix.

CREATE event SET
	year = <int> 2022,
	temperature = <float> 41.5 + 5f,
	horizon = <decimal> 31 + 3dec
;

Sized numbers

Available since: v4.0.0

As well as int, float and decimal, a number can have a fixed size. A sized number keeps its size when it is stored and when it is used in a calculation, and a field of a sized type refuses a value that does not fit.

i64 and f64 are other names for int and float, so 42i64 is the same value as 42. A sized integer suffix cannot follow a number with a decimal point or an exponent, and a literal that does not fit its type is a parse error.

[type::of(255u8), type::of(-32768i16), type::of(18446744073709551615u64), type::of(1.5f32), type::of(42i64)];

['u8', 'i16', 'u64', 'f32', 'int']

A calculation on two numbers of the same type gives that type, and fails instead of wrapping when the result does not fit. A calculation on two different types gives the smallest type that holds both: 1u8 + 2i16 is 3i16, a sized integer and a sized float give a float, and any number with a decimal gives a decimal.

[1u8 + 2u8, 1u8 + 2i16, 200u8 + 100u16];
//- [3u8, 3i16, 300u16]

255u8 + 1u8;
//- "Cannot perform addition with '255u8' and '1u8'"

A number written without a suffix is always an int. Unlike in Rust, its type is not inferred from the other side of a calculation, so 1u8 + 1 adds a u8 and an int and gives an int. To keep a calculation in a sized type, give each literal in it the suffix:

[1u8 + 1, 1u8 + 1u8, 1u8 + 1.5];
//- [2, 2u8, 2.5f]

[type::of(1u8 + 1), type::of(1u8 + 1u8)];
//- ['int', 'u8']

A field of a sized type converts a number that fits exactly, and refuses a number that is out of range or has a fractional part. A cast such as <u8> and the functions type::u8() to type::f32() do the same, and type::is_u8() to type::is_f32() check for each type. A sized number is equal to a number of any other type with the same value, so 5u8 == 5 is true, and a GROUP BY, a set or a UNIQUE index treats the two as one value.

DEFINE FIELD level ON reading TYPE u8;
CREATE reading:1 SET level = 200;
CREATE reading:2 SET level = 256;

-------- Query 1 --------

NONE

-------- Query 2 --------

[{ id: reading:1, level: 200u8 }]

-------- Query 3 --------

"Couldn't coerce value for field `level` of `reading:2`: Expected `u8` but found `256`"

A client keeps the size of a number only when it uses a protocol that carries it, such as CBOR version 2. The original CBOR protocol and JSON send a sized number as an ordinary integer or float.

Big decimal numbers

Available since: v4.0.0

A bigdecimal is an exact decimal number with no fixed limit on its digits. Use it for values that a decimal cannot hold, which keeps at most 28 digits after the decimal point, such as the 38 significant digits of a DynamoDB number or a MySQL DECIMAL(65,30). The literal suffix is bigdec, and the type name is bigdecimal.

12345678901234567890123456789012345.678901234567890123456789012345bigdec;

12345678901234567890123456789012345.678901234567890123456789012345bigdec

A bigdecimal keeps the scale it was written with, so 1.50bigdec stays 1.50bigdec, and an exponent is folded into the digits. Values that differ only in scale are equal, so 1.50bigdec == 1.5bigdec is true, and a GROUP BY, a set or a UNIQUE index treats them as one value. A bigdecimal is also equal to a number of any other type with the same exact value.

[1bigdec, 1.50bigdec, 1.5e3bigdec, 1e-5bigdec];
//- [1bigdec, 1.50bigdec, 1500bigdec, 0.00001bigdec]

[1.50bigdec == 1.5bigdec, 1bigdec == 1, 0.1bigdec == 0.1dec, 0.1bigdec == 0.1f];
//- [true, true, true, false]

0.1bigdec == 0.1f is false because the float nearest to 0.1 is not exactly 0.1.

Calculations

A calculation with a bigdecimal on either side gives a bigdecimal, whatever the type of the other number. Addition, subtraction, multiplication, remainder and powers are exact at any size. Division rounds half away from zero, as PostgreSQL and MySQL do, to 24 digits after the decimal point, or to 4 more than the larger scale of the two numbers when that scale is above 20. Division by zero gives NaN.

99999999999999999999.99999999999999999999bigdec * 99999999999999999999.99999999999999999999bigdec;
//- 9999999999999999999999999999999999999998.0000000000000000000000000000000000000001bigdec

[1bigdec + 1, 1bigdec + 0.5f, 1i8 * 2bigdec];
//- [2bigdec, 1.5bigdec, 2bigdec]

[1bigdec / 3, 2bigdec / 3, 1bigdec / 8];
//- [0.333333333333333333333333bigdec, 0.666666666666666666666667bigdec, 0.125000000000000000000000bigdec]

The functions math::abs(), math::ceil(), math::floor(), math::round(), math::fixed(), math::sign(), math::min(), math::max(), math::sum(), math::product() and math::mean() are exact for a bigdecimal, including as aggregates in a GROUP BY. The other math:: functions convert the value to a decimal first, and fail for a value outside the range of a decimal.

math::sqrt(2.25bigdec);
//- 1.5000000000000000000000000000dec

math::sqrt(1e40bigdec);
//- 'Incorrect arguments for function math::sqrt(). The bigdecimal 10000000000000000000000000000000000000000bigdec is outside the range of a decimal, which this function computes with.'

Casts and fields

<bigdecimal> converts any number and any string that holds a number. A cast from a bigdecimal to decimal, int or a sized integer type fails when the value does not fit that type, and a cast to float rounds to the nearest float.

[<bigdecimal> 1, <bigdecimal> 0.1f, <bigdecimal> "1.250"];
//- [1bigdec, 0.1bigdec, 1.250bigdec]

<decimal> 1.50bigdec;
//- 1.5dec

<decimal> 1e40bigdec;
//- 'Could not cast into `decimal` using input `10000000000000000000000000000000000000000bigdec`'

A field of type bigdecimal converts any number written to it, and refuses a string or NaN. An index on the field serves equality and range queries in numeric order.

DEFINE TABLE account SCHEMAFULL;
DEFINE FIELD balance ON account TYPE bigdecimal;

CREATE account:1 SET balance = 1.25dec;
CREATE account:2 SET balance = 10;
CREATE account:3 SET balance = "1.5";

"Couldn't coerce value for field `balance` of `account:3`: Expected `bigdecimal` but found `'1.5'`"

The first two records store 1.25bigdec and 10bigdec. In a record ID, a bigdecimal keeps its scale as part of the key, so rate:[1.50bigdec] and rate:[1.5bigdec] are two different records.

JSON sends a bigdecimal as a string, in the same way as a decimal.

Numeric precision

Different numeric types can be compared and used together in calculations.

The benefits of floating point numeric values are speed and storage size, but there is a limit to the numeric precision.

13.5719384719384719385639856394139476937756394756;

13.571938471938472f

In addition, when using floating point numbers specifically, mathematical operations can result in a loss of precision (as is normal with other databases).

0.3 + 0.3 + 0.3 + 0.1;

0.9999999999999999f

Common rounding errors can be avoided by performing calculations using decimals.

0.3dec + 0.3dec + 0.3dec + 0.1dec;

1.0dec

Underscores

As a convenience, underscores are ignored when using a number. This allows input to be more readable than it would otherwise. Because underscores are ignored, they will not display in the output.

RELATE dr:evil->bribes->other:character SET dollars = 1_000_000.99;
//- [{ dollars: 1000000.99, id: bribes:4bfld2ukwnja24dzrpw9, in: dr:evil, out: other:character }]

-- Input Korean currency counted in units of 10000, not 1000
RELATE korean:purchaser->buys_house_from->korean:seller
              -- 10억 4천만 5천
    SET amount = 10_4000_5000;
//- [{ amount: 1040005000, id: buys_house_from:9070t2ctgwwg202cpw1z, in: korean:purchaser, out: korean:seller }]

Mathematical constants

A set of floating point numeric constants are available in SurrealDB. Constant names are case insensitive, and can be specified with either lowercase or capital letters, or a mixture of both.

CREATE circle SET circumference = 10;
UPDATE circle SET radius = circumference / ( 2 * MATH::PI );