DOUBLE PRECISION

REAL and DOUBLE PRECISION are floating-point data types. These data types store numeric values with variable precision. Some values may be stored as approximations; you may see slight discrepancies between a value that is loaded and value that is returned. The DECIMAL data type provides more exact results.

The DOUBLE PRECISION data type stores 64-bit floating-point values (8 bytes). The precision of a DOUBLE PRECISION column is 15 digits.

FLOAT and FLOAT8 and are valid synonyms for DOUBLE PRECISION.
Note: FLOAT8 and FLOAT(8) are not synonyms. Yellowbrick permits the float(p) syntax but will promote it to the next floating-point number into which it will fit. This behavior is different than PostgreSQL.

Leading zeroes and whitespace characters are allowed. Trailing whitespace characters are also allowed.

Note: Yellowbrick supports NaN (not a number) and Infinity as floating-point values. The system can store and operate on these values.
  • Conversion of NaN, Infinity, +inf, and -inf to string types produces the appropriate string.
  • DECIMAL and INTEGER types do not support these values for storage or conversion.
  • When NaN is compared to a number, the result of the comparison is always false.
  • When an ORDER BY clause sorts NaN with numbers, NaN always sorts as greater than those numbers.

The following example demonstrates the values that you can load into a DOUBLE PRECISION column. Note that floating-point values with excess digits on the right side of the decimal point are rounded. Values with excess digits on the left side of the decimal point are converted to scientific notation; these values are not rejected.

premdb=# create table floattest(c1 float);
CREATE TABLE
premdb=# \d floattest
       Table "public.floattest"
 Column |       Type       | Modifiers 
--------+------------------+-----------
 c1     | double precision | 

Distribution: Hash (c1)

premdb=# insert into floattest values(1000000000000.123);
INSERT 0 1

premdb=# select * from floattest;
        c1        
------------------
 1000000000000.12
(1 row)

premdb=# insert into floattest values(1000000000000000);
INSERT 0 1
premdb=# select * from floattest;
        c1        
------------------
 1000000000000.12
            1e+15
(2 rows)

extra_float_digits

The extra_float_digits parameter controls the number of extra significant digits that are included when a floating-point value is converted to text for output. The default value is 0. Increasing the number to 1 or greater produces output that more accurately represents the stored value.

For example, note the difference in query results against a REAL column when extra_float_digits is set to 3 versus 0:
premdb=# reset extra_float_digits;
RESET
premdb=# create table extra_float as select avg_att::real from team where avg_att>0;
SELECT 20
premdb=# select * from extra_float;
 avg_att 
---------
  35.776
  20.594
  24.631
  34.91
  59.944
  33.69
  11.189
  ...
(20 rows)

premdb=# set extra_float_digits to 3;
SET
premdb=# create table extra_float_3 as select avg_att::real from team where avg_att>0;
SELECT 20
premdb=# select * from extra_float_3;
  avg_att   
------------
 35.776001
 20.5939999
 24.6310005
 34.9099998
 59.9440002
 33.6899986
 11.1890001
 ...
(20 rows)