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 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.
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 SingleStoreDB 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 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 | +---------+