Updated on 2024-05-07 GMT+08:00

Binary Types

Table 1 lists the binary data types supported by GaussDB.

Table 1 Binary data types

Name

Description

Storage Space

BLOB

Binary large object (BLOB).

Currently, BLOB only supports the following external access APIs:

  • DBE_LOB.GET_LENGTH
  • DBE_LOB.READ
  • DBE_LOB.WRITE
  • DBE_LOB.WRITE_APPEND
  • DBE_LOB.COPY
  • DBE_LOB.ERASE

For details about the APIs, see DBE_LOB.

For Astore, the maximum size is 32 TB minus 1 byte. However, the size of the column description header and the size of the tuple (less than 32 TB minus 1 byte) where the column is located must also be considered. Therefore, the maximum size of the BLOB type may be less than 32 TB minus 1 byte.

For Ustore, the maximum size is 1 GB minus 1 byte. However, the size of the column description header and the size of the tuple (less than 1 GB minus 1 byte) where the column is located must also be considered. Therefore, the maximum size of the BLOB type may be less than 1 GB minus 1 byte.

RAW

Variable-length hexadecimal string.

4 bytes plus the actual binary string. The maximum size is 1 GB minus 1 byte. However, the size of the column description header and the size of the tuple (less than 1 GB minus 1 byte) where the column is located must also be considered. Therefore, the maximum size of this type may be less than 1 GB minus 1 byte.

BYTEA

Variable-length binary string.

4 bytes plus the actual binary string. The maximum size is 1 GB minus 1 byte. However, the size of the column description header and the size of the tuple (less than 1 GB minus 1 byte) where the column is located must also be considered. Therefore, the maximum size of this type may be less than 1 GB minus 1 byte.

BYTEAWITHOUTORDERWITHEQUALCOL

Variable-length binary character string (new type for the encryption feature. If the encryption type of an encrypted column is specified as deterministic encryption, the column type is BYTEAWITHOUTORDERWITHEQUALCOL). The original data type is displayed when an encrypted table is printed by running the meta command.

4 bytes plus the actual binary string. The maximum value is 1073741771 bytes (1 GB – 53 bytes).

BYTEAWITHOUTORDERCOL

Variable-length binary character string (new type for the encryption feature. If the encryption type of an encrypted column is specified as random encryption, the column type is BYTEAWITHOUTORDERCOL). The original data type is displayed when an encrypted table is printed by running the meta command.

4 bytes plus the actual binary string. The maximum value is 1073741771 bytes (1 GB – 53 bytes).

_BYTEAWITHOUTORDERWITHEQUALCOL

Variable-length binary string, which is a new type for the encryption feature.

4 bytes plus the actual binary string. The maximum value is 1073741771 bytes (1 GB – 53 bytes).

_BYTEAWITHOUTORDERCOL

Variable-length binary string, which is a new type for the encryption feature.

4 bytes plus the actual binary string. The maximum value is 1,073,741,771 bytes (1 GB minus 53 bytes).

  • In addition to the size limit of each column, the total size of each tuple cannot exceed 1 GB minus 1 byte.
  • BYTEAWITHOUTORDERWITHEQUALCOL, BYTEAWITHOUTORDERCOL, _BYTEAWITHOUTORDERWITHEQUALCOL, and _BYTEAWITHOUTORDERCOL cannot be directly used to create a table.
  • RAW(n), where n indicates the recommended byte length and is not used to verify the byte length of the input raw type.
  • GaussDB supports a maximum of 1 GB data transfer, and the maximum size of the result string returned by the function is 1 GB.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Create a table.
gaussdb=# CREATE TABLE blob_type_t1 
(
    BT_COL1 INTEGER,
    BT_COL2 BLOB,
    BT_COL3 RAW,
    BT_COL4 BYTEA
) ;

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

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

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