Numeric Types

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

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

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.
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.
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
The float4(p[,s]) mode is added, which is equivalent to dec(p[,s]).
The double data type is added, which is the alias of float8.
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.
For the decimal data type, 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.
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.
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.

Table 1 Integer types

Name	Description	Storage Space	Value Range
TINYINT(n)	Tiny integer, also called INT1. n has no actual meaning and does not affect any performance.	1 byte	0 ~ 255
SMALLINT(n)	Small integer, also called INT2. n has no actual meaning and does not affect any performance.	2 bytes	-32,768 ~ +32,767
INTEGER(n)	Typical choice for integers, also called INT4. n has no actual meaning and does not affect any performance.	4 bytes	-2,147,483,648 ~ +2,147,483,647
MEDIUMINT(n)	Alias of INT4. n is meaningless and does not affect any performance.	4 bytes	-2,147,483,648 ~ +2,147,483,647
BIGINT(n)	Big integer, also called INT8. n has no actual meaning and does not affect any performance.	8 bytes	-9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807
TINYINT(n) UNSIGNED	Tiny integer, also called INT1. n has no actual meaning and does not affect any performance.	1 byte	0 ~ 255
SMALLINT(n) UNSIGNED	Unsigned small integer, also called UINT2. n has no actual meaning and does not affect any performance.	2 bytes	0 ~ +65,535
INTEGER(n) UNSIGNED	Unsigned integer, also called UINT4. n has no actual meaning and does not affect any performance.	4 bytes	0 ~ +4,294,967,295
MEDIUMINT(n) UNSIGNED	Alias of UINT4. n is meaningless and does not affect any performance.	4 bytes	0 ~ +4,294,967,295
BIGINT(n) UNSIGNED	Unsigned large integer, also called UINT8. n has no actual meaning and does not affect any performance.	8 bytes	0 ~ +18,446,744,073,709,551,615

Example:

--Create a table that contains data of the TINYINT(n), SMALLINT(n), MEDIUMINT(n), and BIGINT(n) types.
MogDB=# 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.
MogDB=# \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.
MogDB=# 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.
MogDB=# \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.
MogDB=# DROP TABLE int_type_t1, int_type_t2;

Table 2 Arbitrary precision types

Name	Description	Storage Space	Value Range
NUMERIC[(p[,s])],DECIMAL[(p[,s])]FIXED[(p[,s])]	The value range of p (precision) is [1,1000], and the value range of s (scale) is [0,p]. Note: p indicates the total digits, and s indicates the decimal digits.	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.	If the precision is not specified, the value is equivalent to (10,0), that is, a maximum of 10 digits before the decimal point and 0 digits after the decimal point.

Example:

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

--View the table structure.
MogDB=# \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) |

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

Table 3 Floating-point types

Name	Description	Storage Space	Value Range
FLOAT[(p)],FLOAT4[(p)]	Floating point, which is not very precise. The value range of p (precision) is [1,53].	4 bytes or 8 bytes	When the precision p is between [1,24], the option REAL is used as the internal identifier. When the precision p is between [25,53], the option DOUBLE PRECISION is used as the internal identifier. If no precision is specified, REAL is used as the internal identifier.
DOUBLE PRECISION,FLOAT8,DOUBLE	Double-precision floating point, which is not very precise.	8 bytes	–1.79E+308 to 1.79E+308, 15-bit decimal digits.
FLOAT4(p,s)	The value range of p (precision) is [1,1000], and the value range of s (scale) is [0,p]. Note: p indicates the total digits, and s indicates the decimal digits. It is equivalent to dec(p,s).	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.

Example:

--Create a table that contains data of the float4(p,s), double, float4(n), and float(n) types.
MogDB=# 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.
MogDB=# \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.
MogDB=# DROP TABLE float_type_t1;