REAL ​

REAL and FLOAT 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 the value that is returned. The DECIMAL data type provides more exact results.

The REAL data type stores 32-bit floating-point values (4 bytes). The precision of a REAL column is 6 digits.

FLOAT4 is a valid synonym for REAL.

Note: FLOAT4 and FLOAT(4) 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 from the PostgreSQL behavior.

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 REAL 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 realtest(c1 real);
CREATE TABLE
premdb=# \d realtest
  Table "public.realtest"
 Column | Type | Modifiers 
--------+------+-----------
 c1     | real | 

Distribution: Hash (c1)

premdb=# insert into realtest values(100.1238);
INSERT 0 1
premdb=# select * from realtest;
   c1    
---------
 100.124
(1 row)

premdb=# insert into realtest values(100000);
INSERT 0 1
premdb=# select * from realtest;
   c1    
---------
 100.124
  100000
(2 rows)

premdb=# insert into realtest values(1000000);
INSERT 0 1
premdb=# select * from realtest;
   c1    
---------
 100.124
  100000
   1e+06
(3 rows)

You can insert Nan and Infinity values into a REAL or DOUBLE PRECISION column (and convert them to character strings):

premdb=# insert into realtest values('-inf');
INSERT 0 1
premdb=# insert into realtest values('+inf');
INSERT 0 1
premdb=# insert into realtest values('NaN');
INSERT 0 1
premdb=# select * from realtest order by c1;
   c1     
-----------
 -Infinity
   100.124
   100000
    1e+06
  Infinity
      NaN
(6 rows)

premdb=# select c1::varchar from realtest order by c1;
   c1     
-----------
 -Infinity
 100.124
 100000
 1e+06
 Infinity
 NaN
(6 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)

Parent topic:SQL Data Types