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:
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, 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.
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.
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
Alias of theAlias of the
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
-128 to +127
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.
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
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.
NUMBER[(p[,s])]
Alias of the NUMERIC class.
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.
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
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.
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;
Top comments (0)