Floating-point types

real info

double precision info

Syntax

<int> [.<frac>]

Details

Literals

Materialize assumes untyped numeric literals containing decimal points are numeric; to use float, you must explicitly cast them as we’ve done below.

Special values

Floating-point numbers have three special values, as specified in IEEE 754:

To input these special values, write them as a string and cast that string to the desired floating-point type. For example:

SELECT 'NaN'::real AS nan

 nan
-----
 NaN

The strings are recognized case insensitively.

Aggregate precision

To support incremental updates, including retractions, Materialize accumulates sum over real and double precision values in a fixed-point representation. Aggregates computed from sum inherit its behavior: avg, stddev, stddev_pop, stddev_samp, variance, var_pop, and var_samp. Aggregates that do not sum their inputs, such as min and max, return exact input values.

When these aggregates run in a dataflow, for example in an index, a materialized view, a subscription, or a SELECT query that reads from a source or table, each input value is truncated toward zero to a multiple of 2^-24 (approximately 6E-8) before it is added:

• Values with a magnitude smaller than 2^-24 contribute 0.
• Values with fractional parts that are not multiples of 2^-24 lose precision, for example 0.1 contributes 0.09999996423721313.
• The result is incorrect if the magnitude of the final sum reaches 2¹⁰³ (approximately 1E+31).

Queries that Materialize evaluates entirely during planning, such as aggregates over constant VALUES lists, use floating-point addition and are not subject to this truncation.

If your application requires exact sums of fractional values, use numeric, which is not subject to this truncation.

Valid casts

In addition to the casts listed below, real and double precision values can be cast to and from one another. The cast from real to double precision is implicit and the cast from double precision to real is by assignment.

From real

You can cast real or double precision to:

• int (by assignment)
• numeric (by assignment)
• text (by assignment)

To real

You can cast to real or double precision from the following types:

• int (implicitly)
• numeric (implicitly)
• text (explicitly)

Examples

SELECT 1.23::real AS real_v;

 real_v
---------
    1.23