Binary Types

Table 1 lists the binary data types supported by openGauss.

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

1. The BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, and LONGBLOB types are added.
2. The input function of the BLOB type is modified. When dolphin.b_compatibility_mode is set to on, the input function is compatible with the common character string input of the MySQL database. The corresponding character string can be output only when bytea_output is set to escape; otherwise, the value will be converted into a hexadecimal character string for output.
3. For the TINYBLOB, MEDIUMBLOB, and LONGBLOB types, if dolphin.b_compatibility_mode is set to off, the input function is still compatible with the common character string input of the MySQL database. The corresponding character string can be output only when bytea_output is set to escape; otherwise, the character string will be converted into a hexadecimal character string for output.
4. The input function of the BINARY type is modified to support the identification of escape characters in the MySQL database.
5. The BIANRY EXPR is added. The BINARY keyword before any expression indicates that the expression is converted to the binary type.

Table 1 Binary data types

NOTE:

• In addition to the size limit of each column, the total size of each tuple cannot exceed 1 GB – 8203 bytes (that is, 1073733621 bytes).

• BYTEAWITHOUTORDERWITHEQUALCOL, BYTEAWITHOUTORDERCOL, _BYTEAWITHOUTORDERWITHEQUALCOL, and _BYTEAWITHOUTORDERCOL cannot be directly used to create a table.

Example:

--Create a table.
openGauss=#  CREATE TABLE blob_type_t1 
(
    BT_COL1 INTEGER,
    BT_COL2 BLOB,
    BT_COL3 RAW,
    BT_COL4 BYTEA
) ;

--Insert data.
openGauss=#  INSERT INTO blob_type_t1 VALUES(10,empty_blob(),
HEXTORAW('DEADBEEF'),E'\\xDEADBEEF');

--Query data in the table.
openGauss=#  SELECT * FROM blob_type_t1;
 bt_col1 | bt_col2 | bt_col3  |  bt_col4   
---------+---------+----------+------------
      10 |         | DEADBEEF | \xdeadbeef
(1 row)

--Delete the table.
openGauss=#  DROP TABLE blob_type_t1;

--Use BINARY to convert data.
openGauss=# select 'a\t'::binary;
 binary
--------
 \x6109
(1 row)

openGauss=# select binary 'a\b';
 binary
--------
 \x6108
(1 row)