Floating Point

The Floating Point data types (REAL and DOUBLE) store extremely close value approximations, and are therefore recommended for values that tend to be inexact, such as Scientific Notation. While Floating Point generally runs faster than Numeric, it has a lower precision of 9 (REAL) or 17 (DOUBLE) compared to Numeric’s 38. For operations that require a higher level of precision, using Numeric is recommended.

The floating point representation is based on IEEE 754.

Floating Point Types

The following table describes the Floating Point data types.

Name

Details

Data Size (Not Null, Uncompressed)

Example

REAL

Single precision floating point (inexact)

4 bytes

3.141

DOUBLE

Double precision floating point (inexact)

8 bytes

0.000003

The following table shows information relevant to the Floating Point data types.

Aliases

Syntax

Data Size (Not Null, Uncompressed)

DOUBLE is also known as FLOAT.

A double precision floating point can be entered as a regular literal, such as 3.14, 2.718, .34, or 2.71e-45. To enter a REAL floating point number, cast the value. For example, (3.14 :: REAL).

Floating point types are either 4 or 8 bytes, but size could be lower after compression.

Floating Point Examples

The following are examples of the Floating Point syntax:

CREATE TABLE cool_numbers (a REAL NOT NULL, b DOUBLE);

INSERT INTO cool_numbers VALUES (1,2), (3.14159265358979, 2.718281828459);

SELECT * FROM cool_numbers;
1.0,2.0
3.1415927,2.718281828459

Note

Most SQL clients control display precision of floating point numbers, and values may appear differently in some clients.

Floating Point Casts and Conversions

The following table shows the possible Floating Point value conversions:

Type

Details

BOOL

1.0true, 0.0false

TINYINT, SMALLINT, INT, BIGINT

2.02, 3.141592653589793, 2.7182818284592, 0.50, 1.51

TEXT

1'1.0000', 3.14159265358979'3.1416'

Note

As shown in the above examples, casting real to int rounds down.