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

Note

The space consumed by the DECIMAL data type 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.

Note

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 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 17 digits.

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

CREATE TABLE rn_t (id float);
INSERT INTO rn_t VALUES (1234567);
INSERT INTO rn_t VALUES (123.4567);
SELECT * FROM rn_t;
+---------+
| id      |
+---------+
| 123456  |
| 123.457 |
+---------+

Warning

Avoid using the = or != comparison operators on FLOAT columns in WHERE clauses due to the potential inaccuracy of the FLOAT data type. It's best to limit using > or < comparison operators on FLOAT columns.

Last modified: March 8, 2024

Was this article helpful?