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

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

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

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

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)