Floating-Point Types

The floating-point types are used to store approximate numeric values. Table 1 lists the floating-point types supported by M-compatible databases.

**Table 1** Floating-Point Types
Name	Description	Storage Space	Range
FLOAT4[(p, s)] [ZEROFILL]	Single-precision floating-point number, which has errors. The value range of the precision p is [1,255], and the value range of the scale s is [0,min(p, 30)].	4 bytes	–3.402E+38 to 3.402E+38, 6-bit decimal digits
FLOAT8[(p,s)] [ZEROFILL]	Double-precision floating-point number, which has errors. The value range of the precision p is [1,255], and the value range of the scale s is [0,min(p, 30)].	8 bytes	–1.79E+308 to 1.79E+308, 15-bit decimal digits
FLOAT[(p)] [ZEROFILL]	The value range of p is [0,53]. When p is set to [0, 25), FLOAT(p) is the same as FLOAT4. When p is set to [25, 54), FLOAT(p) is the same as FLOAT8.	4 bytes when p is set to [0, 25); 8 bytes when p is set to [25, 54)	When p is set to [0, 25), the value range is the same as that of FLOAT4. When p is set to [25, 54), the value range is the same as that of FLOAT8.
REAL[(p, s)] [ZEROFILL]	The behavior is the same as that of DOUBLE[(p, s)]. Data is stored by DOUBLE[(p, s)]. When sql_mode is set to REAL_AS_FLOAT, data is stored by FLOAT[(p, s)]. The behavior is the same as that of FLOAT8[(p, s)].	8 bytes 4 bytes when sql_mode is set to REAL_AS_FLOAT	–1.79E+308 to 1.79E+308, 15-bit decimal digits –3.402E+38 to 3.402E+38, 6-bit decimal digits when sql_mode is set to REAL_AS_FLOAT
FLOAT[(p, s)] [ZEROFILL]	This type maps to FLOAT4[(p,s)]. The value range of the precision p is [1,255], and the value range of the scale s is [0,min(p, 30)].	4 bytes	–3.402E+38 to 3.402E+38, 6-bit decimal digits
DOUBLE[(p,s)] [ZEROFILL]	This type maps to FLOAT8[(p,s)]. The value range of the precision p is [1,255], and the value range of the scale s is [0,min(p, 30)].	8 bytes	–1.79E+308 to 1.79E+308, 15-bit decimal digits
DOUBLE PRECISION[(p,s)] [ZEROFILL]	This type maps to FLOAT8[(p,s)]. The value range of the precision p is [1,255], and the value range of the scale s is [0,min(p, 30)].	8 bytes	–1.79E+308 to 1.79E+308, 15-bit decimal digits

The precision p indicates the maximum number of digits, and the scale s indicates the number of decimal digits.
If the input value exceeds the scale, the truncated value is returned.
If the precision is not set, the column range is the same as that specified in Table 1. If the precision is set, the column range is subject to the configured precision.
If the value of sql_mode contains strict_trans_tables, an ERROR message is reported if the input value is invalid or exceeds the range implied by the precision.
If the value of sql_mode does not contain strict_trans_tables and the input value is invalid or exceeds the range implied by the precision, the truncated value or the maximum value within the range implied by the precision is returned.
If the value of sql_mode contains real_as_float, columns of the REAL type are stored as the FLOAT type, and the behavior is the same as that of the FLOAT type.
If the value of sql_mode does not contain real_as_float, columns of the REAL type are stored as the DOUBLE type, and the behavior is the same as that of the DOUBLE type.

Examples

    
     
       
       
         -- Create a table whose columns are of floating-point type.
m_db=# CREATE TABLE float_tab1
(
    a FLOAT(6,2), 
    b DOUBLE(6,2)
) DISTRIBUTE BY REPLICATION;

-- Insert data within the range implied by the specified precision and scale.
m_db=# INSERT INTO float_tab1 VALUES(1234.56, 1234.56);

-- If the inserted data exceeds the range implied by the specified scale, the value truncated based on the specified scale is returned.
m_db=# INSERT INTO float_tab1 VALUES(12.3456789, 12.3456789);

-- With the strict mode enabled, an error is reported if the inserted data exceeds the range implied by the specified precision.
m_db=# SET SQL_MODE = 'strict_trans_tables,only_full_group_by,no_zero_in_date,no_zero_date,error_for_division_by_zero,
no_auto_create_user,no_engine_substitution';
SET
m_db=# INSERT INTO float_tab1 VALUES(12345678999, 12345678999);
ERROR:  float field overflow
DETAIL:  A field with precision 6, scale 2 must round to an absolute value less than 9.9999900e+03.
CONTEXT:  referenced column: a


-- With the strict mode disabled, if the inserted data exceeds the range implied by the specified precision, the maximum value within the range implied by the specified precision is returned.
m_db=# SET SQL_MODE = '';
SET
m_db=# INSERT INTO float_tab1 VALUES(12345678999, 12345678999);
INSERT 0 1

-- Query data in the table.
m_db=# SELECT * FROM float_tab1;
    a    |    b
---------+---------
 1234.56 | 1234.56
   12.35 |   12.35
 9999.99 | 9999.99
(3 rows)

-- Drop the table.
m_db=# DROP TABLE float_tab1;

-- When SQL_MODE does not contain REAL_AS_FLOAT, create a table whose columns are of REAL type.
m_db=# SET SQL_MODE='';
SET
m_db=# CREATE TABLE real_tab1(a REAL) DISTRIBUTE BY REPLICATION;

-- Insert data.
m_db=# INSERT INTO real_tab1 VALUES(12345.678999999999);

-- Query data in the table. The result is the same as that of the DOUBLE type and is accurate to 12 digits.
m_db=# SELECT * FROM real_tab1;
    a
---------
 12345.678999999998
 (1 row)

-- Drop the table.
m_db=# DROP TABLE real_tab1;

-- When SQL_MODE contains REAL_AS_FLOAT, create a table whose columns are of REAL type.
m_db=# SET SQL_MODE='REAL_AS_FLOAT';
SET
m_db=# CREATE TABLE real_tab2(a REAL) DISTRIBUTE BY REPLICATION;

-- Insert data.
m_db=# INSERT INTO real_tab2 VALUES(12345.678999999999);

-- Query data in the table. The result is the same as that of the FLOAT type and is accurate to six digits.
m_db=# SELECT * FROM real_tab2;
    a
---------
 12345.7
 (1 row)

-- Set the zerofill attribute.
m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
m_db=# CREATE TABLE t1 (a float zerofill) DISTRIBUTE BY REPLICATION;
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(1);
INSERT 0 1
m_db=# SELECT * FROM t1;
      a       
--------------
 000000000001
(1 row)

-- Drop the table.
m_db=# DROP TABLE real_tab2;

        

      

    
   