SingleStore DB

Real Numbers

Data Type

Size

Size (Not Null)

Synonyms

Precision

Syntax

FLOAT

4 bytes

4 bytes

23 bits

FLOAT

DOUBLE

8 bytes

8 bytes

REAL

53 bits

DOUBLE

DECIMAL

see note

see note

DEC, FIXED, NUMERIC

65 digits

Notice

The space consumed by the DECIMAL datatype varies with the precision and scale arguments. The syntax for defining a DECIMAL (or its synonyms DEC, FIXED, and NUMERIC) is in the form DECIMAL(P,S) where P is the total number of digits (precision) and S (scale) is how many of those digits appear after the decimal point. For example, DECIMAL(10,5) will store numbers with 5 digits before the decimal and 5 after.

For optimal query performance, use 18 total digits or less for the DECIMAL or NUMERIC data types. The correct syntax is DECIMAL(18,S) or NUMERIC(18,S).

The formula for the number of bytes used to store a decimal value is (ceil((P - S) / 9) + ceil(S / 9)) * 4. So, for our example, it would be (ceil((10 - 5) / 9) + ceil(5 / 9)) * 4, or 8 bytes.

Notice

For FLOAT and DOUBLE, precision refers to the number of bits in the significand.

Working with Floats and Doubles

FLOAT and DOUBLE represent approximate values and SingleStore DB follows IEEE 754 standards (see FLOAT | see DOUBLE) for floating point numbers.

The precision listed in the Real Numbers table refers to the number of bits used to store the significand. For FLOAT, 23 bits is about 6 digits. For larger numbers (up to 53 bits) use DOUBLE, which stores up to 14 digits.

Numbers exceeding the precision of the data type are rounded according to the IEEE 754 standard when entered. For example:

CREATE TABLE t (id float);
INSERT INTO t VALUES (1234567);
INSERT INTO t VALUES (123.4567);
SELECT * FROM t;

****
+---------+
| id      |
+---------+
| 123456  |
| 123.457 |
+---------+