Introduction to GBase 8c B Compatibility Library (2)

With the support of the Dolphin plugin, the GBase 8c B Compatibility Database (dbcompatibility='B', hereafter referred to as the B compatibility library) has greatly enhanced its compatibility with MySQL in terms of data types. Here is a look at the common data types:

Numerical Types

Compared to the native GBase 8c syntax, Dolphin makes several modifications to numerical types:

1. INT/TINYINT/SMALLINT/BIGINT:

   • Added support for optional modifiers (n), allowing the usage of TINYINT(n)/SMALLINT(n)/BIGINT(n). The modifier n has no practical significance and does not affect any behavior.

2. MEDIUMINT(n):

   • A new data type added as an alias for INT4. The modifier n has no practical effect. It occupies 4 bytes of storage space with a range of -2,147,483,648 to +2,147,483,647.

3. FIXED[(p[,s])]:

   • Introduced as an alias for the NUMERIC type. Users can specify precision. Every four decimal digits occupy two bytes, plus an additional eight-byte overhead for the entire data. Without specified precision, it supports a maximum of 131,072 digits before the decimal point and 16,383 digits after.

4. float4(p[,s]):

   • A new addition, equivalent to dec(p[,s]).

5. double:

   • Introduced as an alias for float8.

6. float4/float:

   • Added support for optional modifiers (n), allowing the usage of float4(n)/float(n). When n is between [1,24], it represents a single-precision floating point; when n is between [25,53], it represents a double-precision floating point.

7. decimal/dec/fixed/numeric:

   • When precision is not specified, the default precision is (10,0), meaning 10 total digits with 0 decimal places.

8. UNSIGNED INT/TINYINT/SMALLINT/BIGINT:

   • Compared to regular integers, the highest bit is a digit bit rather than a sign bit. Additionally, in GBase 8c, TINYINT is unsigned by default, whereas in the B library, it is signed by default.

9. zerofill attribute modifier:

   • Only syntactically supported with no actual zero-filling effect. Equivalent to the UNSIGNED modifier.

10. cast function type conversion:

    • Added parameters signed/unsigned. cast as unsigned converts the type to uint8, and cast as signed converts the type to int8.

11. float(p,s), double(p,s), real(p,s), double precision(p,s):

    • These syntaxes are roughly equivalent to dec(p,s). Unlike dec(p,s), the p and s for float(p,s), real(p,s), and double precision(p,s) must be integers, while double(p,s) is entirely equivalent to dec(p,s). The rounding method used is round-half-up.

Table 1 Integer Types

Name	Description	Storage Space	Range
TINYINT(n)	Tiny integer, alias for INT1. n has no practical effect.	1 byte	-128 to +127
SMALLINT(n)	Small integer, alias for INT2. n has no practical effect.	2 bytes	-32768 to +32767
INTEGER(n)	Common integer, alias for INT4. n has no practical effect.	4 bytes	-2147483648 to [phone]
MEDIUMINT(n)	Alias for INT4. n has no practical effect.	4 bytes	-2147483648 to [phone]
BIGINT(n)	Large integer, alias for INT8. n has no practical effect.	8 bytes	-9223372036854775808 to +9223372036854775807
TINYINT(n) UNSIGNED	Unsigned tiny integer, alias for UINT1. n has no practical effect.	1 byte	0 to 255
SMALLINT(n) UNSIGNED	Unsigned small integer, alias for UINT2. n has no practical effect.	2 bytes	0 to +65535

Examples

1. Creating a table with TINYINT(n), SMALLINT(n), MEDIUMINT(n), BIGINT(n) data types

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)
);

2. Viewing the table structure

\d int_type_t1

Result:

Table"public.int_type_t1"
Column|Type|Modifiers
---------+----------+-----------
IT_COL1|tinyint|
IT_COL2|smallint|
IT_COL3|integer|
IT_COL4|bigint|
IT_COL5|integer|

3. Creating a table with zerofill attribute fields

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
);

4. Viewing the table structure

\d int_type_t2

Result:

Table"public.int_type_t2"
Column|Type|Modifiers
---------+-------+-----------
IT_COL1|uint1|
IT_COL2|uint2|
IT_COL3|uint4|
IT_COL4|uint8|
IT_COL5|uint4|

5. Using cast unsigned to convert an expression to uint8 type

SELECT CAST(1-2 AS unsigned);

Result:

uint8
----------------------
18446744073709551615
(1row)

6. Using cast signed to convert an expression to int8 type

SELECT CAST(1-2 AS signed);

Result:

int8
------
-1
(1row)

Table 2: Arbitrary Precision Types

Name	Description	Storage Space	Range
DECIMAL[(p[,s])] FIXED[(p[,s])] FIXED[(p[,s])]	Precision p ranges from [1,1000], scale s ranges from [0,p]. Note: p is the total number of digits, s is the number of decimal places.	User-defined precision. Each four decimal digits occupy two bytes, plus an additional eight-byte overhead.	If precision is not specified, it defaults to (10,0), meaning a maximum of 10 digits before the decimal point and 0 digits after.
NUMBER[(p[,s])]	Alias for NUMERIC type.	User-defined precision. Each four decimal digits occupy two bytes, plus an additional eight-byte overhead.	If precision is not specified, it allows up to 131,072 digits before the decimal point and up to 16,383 digits after.

Examples

1. Create a table with columns of types FIXED(p,s), FIXED, DECIMAL, and NUMBER.

CREATE TABLE dec_type_t1
(
    DEC_COL1 FIXED,
    DEC_COL2 FIXED(20,5),
    DEC_COL3 DECIMAL,
    DEC_COL4 NUMBER
);

2. View the table structure.

\d dec_type_t1

The result is:

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        |

Table 3 Floating Point Types

Name	Description	Storage Space	Range
FLOAT[(p)] FLOAT4[(p)]	Floating point, not precise. Precision p ranges from [1,53].	4 bytes or 8 bytes	When p is between [1,24], REAL is used internally. When p is between [25,53], DOUBLE PRECISION is used internally. If precision is not specified, REAL is used internally.
DOUBLE PRECISION FLOAT8 DOUBLE	Double precision floating point, not precise.	8 bytes	-79E+308~79E+308, with 15 decimal digits precision.
FLOAT4(p,s)	Precision p ranges from [1,1000], scale s ranges from [0,p]. Note: p is the total number of digits, s is the number of decimal places. Equivalent to dec(p,s).	User-declared precision. Each four decimal digits occupy two bytes, plus an additional overhead of eight bytes for the entire data.	-
FLOAT(p,s) DOUBLE(p,s) REAL(p,s) DOUBLE PRECISION(p,s)	Precision p ranges from [1,1000], scale s ranges from [0,p]. Note: p is the total number of digits, s is the number of decimal places. FLOAT(p,s), REAL(p,s), and DOUBLE PRECISION(p,s) are roughly equivalent to dec(p,s). p and s must be integers, while DOUBLE(p,s) is completely equivalent to dec(p,s). Rounding mode is round half up.	User-declared precision. Each four decimal digits occupy two bytes, plus an additional overhead of eight bytes for the entire data.

Character Types

Compared to the native GBase8c syntax, dolphin has made the following modifications to character types:

1. Modified the meaning of n in CHARACTER/NCHAR types, where n indicates the character length instead of the byte length.

2. All character data types ignore trailing spaces when compared, such as in WHERE clause filtering or JOIN conditions. For example, 'a'::text = 'a'::text evaluates to true. Note that for VARCHAR, VARCHAR2, NVARCHAR, TEXT, and CLOB types, trailing spaces are ignored in hash joins and hash aggregates only when the GUC parameter string_hash_compatible is set to on.

3. Added NATIONAL VARCHAR(n), an alias for the NVARCHAR2(n) type, where n indicates the character length.

4. Added support for an optional modifier (n) in TEXT, allowing the usage of TEXT(n). The n has no practical significance and does not affect any behavior.

5. Added TINYTEXT(n), MEDIUMTEXT(n), and LONGTEXT(n) data types, which are aliases for TEXT. The n has no practical significance and does not affect any behavior.

Table 4 Character Types

Name	Description	Storage Space
CHAR(n) CHARACTER(n) NCHAR(n)	Fixed-length string, padded with spaces if necessary. n specifies the character length. If n is not specified, the default length is 1.	Up to 10MB.
NATIONAL VARCHAR(n)	Variable-length string. Alias for NVARCHAR2(n) type. n specifies the character length.	Up to 10MB.
TEXT(n) TINYTEXT(n) MEDIUMTEXT(n) LONGTEXT(n)	Variable-length string. n has no practical meaning and does not affect any behavior.	Up to 1GB-1, but considering column descriptor header size and tuple size limitations, the maximum size may be less than 1GB-1.

Examples

1. Create a test table and insert data

CREATE TABLE char_type_t1
(
    CT_COL1 CHARACTER(4),
    CT_COL2 TEXT(10),
    CT_COL3 TINYTEXT(11),
    CT_COL4 MEDIUMTEXT(12),
    CT_COL5 LONGTEXT(13)
);

2. Check the table structure

\d char_type_t1

Result:

Table "public.char_type_t1"
Column  | Type         | Modifiers
--------+--------------+-----------
CT_COL1 | character(4) |
CT_COL2 | text         |
CT_COL3 | text         |
CT_COL4 | text         |
CT_COL5 | text         |

3. Insert data into the table

INSERT INTO char_type_t1 VALUES ('4 characters');

4. Query the data

SELECT CT_COL1, length(CT_COL1) FROM char_type_t1;

Result:

CT_COL1       | length
--------------+--------
4 characters  | 12
(1 row)