Numeric Types

For details about arithmetic operators and related built-in functions, see Arithmetic Functions and Operators.

Compared with the original openGauss, Dolphin modifies the arithmetic types as follows:

1. The INT, TINYINT, SMALLINT, and BIGINT support the optional modifier (n), that is, the usage of TINYINT(n), SMALLINT(n), and BIGINT(n) is supported. n is meaningless and does not affect any performance.
2. The MEDIUMINT(n) data type is added, which is the alias of INT4. n is meaningless and does not affect any performance. The storage space is 4 bytes, and the data ranges from -2,147,483,648 to +2,147,483,647.
3. The FIXED[(p[,s])] data type is added, which is the alias of the NUMERIC type. The precision is specified by users. Two bytes are occupied for every four decimals of precision. An extra eight-byte overhead is added for numbers of this type. Up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point when no precision is specified
4. The float4(p[,s]) mode is added, which is equivalent to dec(p[,s]).
5. The double data type is added, which is the alias of float8.
6. The new float4 and float support the modifier (n). That is, float4(n) and float(n) are supported. When the value range of n is [1,24], float4(n) and float(n) indicate a single-precision floating point number. If the value range of n is [25,53], float4(n) and float(n) indicate a double-precision floating point number.
7. For the decimal, dec, fixed, and numeric data types, if the precision is not specified, the default precision is (10,0). That is, the total number of digits is 10 and the number of decimal places is 0.
8. The UNSIGNED INT, TINYINT, SMALLINT, and BIGINT types are added. Compared with a common integer, the most significant bit of the UNSIGNED INT, TINYINT, SMALLINT, BIGINT type is a digit bit instead of a sign bit.
9. The zerofill attribute is added, which is supported only in syntax and does not have the effect of filling zeros. It is equivalent to UNSIGNED.
10. The cast function parameters SIGNED and UNSIGNED are added. CAST AS UNSIGNED converts the type to uint8, and CAST AS SIGNED converts the type to int8.

Table 1 Integer types

Example:

--Create a table that contains data of the TINYINT(n), SMALLINT(n), MEDIUMINT(n), and BIGINT(n) types.
openGauss=# CREATE TABLE int_type_t1
           (
            IT_COL1 TINYINT(10),
            IT_COL2 SMALLINT(20),
            IT_COL3 MEDIUMINT(30),
            IT_COL4 BIGINT(40),
            IT_COL5 INTEGER(50)
           );

--View the table structure.
openGauss=# \d int_type_t1
   Table "public.int_type_t1"
 Column  |   Type   | Modifiers
---------+----------+-----------
 it_col1 | tinyint  |
 it_col2 | smallint |
 it_col3 | integer  |
 it_col4 | bigint   |
 it_col5 | integer  |

--Create a table with the zerofill attribute column.
openGauss=# CREATE TABLE int_type_t2
           (
            IT_COL1 TINYINT(10) zerofill,
            IT_COL2 SMALLINT(20) unsigned zerofill,
            IT_COL3 MEDIUMINT(30) unsigned,
            IT_COL4 BIGINT(40) zerofill,
            IT_COL5 INTEGER(50) zerofill
           );

--View the table structure.
openGauss=# \d int_type_t2
   Table "public.int_type_t2"
 Column  | Type  | Modifiers
---------+-------+-----------
 it_col1 | uint1 |
 it_col2 | uint2 |
 it_col3 | uint4 |
 it_col4 | uint8 |
 it_col5 | uint4 |

--Delete a table.
openGauss=# DROP TABLE int_type_t1, int_type_t2;

--Use CAST UNSIGNED to convert an expression to the uint8 type.
openGauss=# select cast(1 - 2 as unsigned);
        uint8
----------------------
 18446744073709551615
(1 row)

--Use CAST SIGNED to convert an expression to the int8 type.
openGauss=# select cast(1 - 2 as signed);
 int8
------
   -1
(1 row)

Table 2 Arbitrary precision types

Example:

--Create a table with FIXED(p,s), FIXED, decimal, and number data.
openGauss=# CREATE TABLE dec_type_t1
           (
            DEC_COL1 FIXED,
            DEC_COL2 FIXED(20,5),
            DEC_COL3 DECIMAL,
            DEC_COL4 NUMBER
           );

--View the table structure.
openGauss=# \d dec_type_t1
      Table "public.dec_type_t1"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 dec_col1 | numeric(10,0) |
 dec_col2 | numeric(20,5) |
 dec_col3 | numeric(10,0) |
 dec_col4 | numeric       |

--Delete a table.
openGauss=# DROP TABLE dec_type_t1;

Table 3 Floating-point types

Example:

--Create a table that contains data of the float4(p,s), double, float4(n), and float(n) types.
openGauss=# CREATE TABLE float_type_t1
           (
            F_COL1 FLOAT4(10,4),
            F_COL2 DOUBLE,
            F_COL3 float4(10),
            F_COL4 float4(30),
            F_COL5 float(10),
            F_COL6 float(30)
           );

--View the table structure.
openGauss=# \d float_type_t1
     Table "public.float_type_t1"
 Column |       Type       | Modifiers
--------+------------------+-----------
 f_col1 | numeric(10,4)    |
 f_col2 | double precision |
 f_col3 | real             |
 f_col4 | double precision |
 f_col5 | real             |
 f_col6 | double precision |

--Delete a table.
openGauss=# DROP TABLE float_type_t1;