CockroachDB supports various inexact, floating-point number data types with up to 17 digits of decimal precision.
They are handled internally using the standard double-precision (64-bit binary-encoded) IEEE754 format.
Names and Aliases
| Name | Aliases |
|---|---|
FLOAT | None |
REAL | FLOAT4 |
DOUBLE PRECISION | FLOAT8 |
Syntax
A constant value of type FLOAT can be entered as a numeric literal. For example: 1.414 or -1234.
The special IEEE754 values for positive infinity, negative infinity and NaN (Not-a-Number) cannot be entered using numeric literals directly and must be converted using an interpreted literal or an explicit conversion from a string literal instead.
The following values are recognized:
| Syntax | Value |
|---|---|
inf, infinity, +inf, +infinity | +∞ |
-inf, -infinity | -∞ |
nan | NaN (Not-a-Number) |
For example:
FLOAT '+Inf''-Inf'::FLOATCAST('NaN' AS FLOAT)
Size
A FLOAT column supports values up to 8 bytes in width, but the total storage size is likely to be larger due to CockroachDB metadata.
Examples
> CREATE TABLE floats (a FLOAT PRIMARY KEY, b REAL, c DOUBLE PRECISION); > SHOW COLUMNS FROM floats; +-------+------------------+---------+---------+-------------+ | Field | Type | Null | Default | Indices | +-------+------------------+---------+---------+-------------+ | a | FLOAT | false | NULL | {"primary"} | | b | REAL | true | NULL | {} | | c | DOUBLE PRECISION | true | NULL | {} | +-------+------------------+---------+---------+-------------+ (3 rows) > INSERT INTO floats VALUES (1.012345678901, 2.01234567890123456789, CAST('+Inf' AS FLOAT)); > SELECT * FROM floats; +----------------+--------------------+------+ | a | b | c | +----------------+--------------------+------+ | 1.012345678901 | 2.0123456789012346 | +Inf | +----------------+--------------------+------+ (1 row) # Note that the value in "b" has been limited to 17 digits. Supported Casting & Conversion
FLOAT values can be cast to any of the following data types:
| Type | Details |
|---|---|
INT | Truncates decimal precision and requires values to be between -2^63 and 2^63-1 |
DECIMAL | Causes an error to be reported if the value is NaN or +/- Inf. |
BOOL | 0 converts to false; all other values convert to true |
STRING | -- |