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

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:

SQL

CREATE TABLE rn_t (id float);
INSERT INTO rn_t VALUES (1234567);
INSERT INTO rn_t VALUES (123.4567);

SQL

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.

Real Numbers

On this page

Working with Floats and Doubles

Was this article helpful?

On this page

Was this article helpful?