PolarDB:Binary types

A binary string is a sequence of octets or bytes. Binary strings are distinguished from character strings in two ways. First, binary strings can store octets of value zero and other non-printable octets. In most cases, non-printable octets indicate octets outside the decimal range 32 to 126. Character strings cannot store octets of value zero. Character strings also cannot store octet values and sequences of octet values that are invalid based on the selected character set encoding of the database. Second, operations on binary strings process the actual bytes. The processing of character strings depends on locale settings. In short, binary strings are suitable for storing data that programmers consider as raw bytes, whereas character strings are suitable for storing text.

The BYTEA type supports two formats for input and output: hex format and escape format. Both formats are always accepted on input. The bytea_output parameter specifies the output format. The default output format is hex.

The SQL standard defines a different binary string type, which is called BLOB or BINARY LARGE OBJECT. The BLOB input format is different from BYTEA, but the functions and operators that they provide are mostly the same.

Hex format of BYTEA

The hex format encodes binary data as two hexadecimal digits per byte, with the most significant nibble first. The entire string is preceded by the sequence \x, which distinguishes the string from that encoded by the escape format. In some scenarios, the initial backslash (\) may need to be escaped by doubling it. For input, the hexadecimal digits can be uppercase or lowercase. Whitespace characters are allowed between digit pairs and disallowed within a digit pair or in the \x sequence. The hex format is compatible with a wide range of external applications and protocols and provides faster conversion than the escape format. Therefore, the hex format is preferred.

Example:

    select '\xDEADBEEF';

Escape format of BYTEA

The escape format is the traditional format for the BYTEA type. The escape format represents a binary string as a sequence of ASCII characters and converts those bytes that cannot be represented as an ASCII character into special escape sequences. From the point of view of applications, if representing bytes as characters makes sense, this representation can be convenient. However, in practice, it is confusing in most cases because this mechanism fuzzes up the distinction between binary strings and character strings, and the special escape mechanism is difficult to be processed. Therefore, the escape format may not be applied to most new applications.

When you enter BYTEA values in the escape format, octets of specific values must be escaped. All octet values can be escaped. In most cases, to escape an octet, convert the octet into its three-digit octal value and precede the value by a backslash (\). The backslash (octet decimal value 92) can be represented by double backslashes.

Literal escaped octets of BYTEA

The requirement for escaping non-printable octets varies based on locale settings. In some cases, you can leave them unescaped.

A single quotation mark (') must be doubled because this rule is applicable to all string constants in an SQL statement. The generic string literal parser consumes the outermost single quotation marks and reduces a pair of single quotation marks to one data character. The BYTEA input function recognizes a single quotation mark as a common data character. However, the BYTEA input function recognizes backslashes as special characters.

In some cases, a backslash must be doubled because the generic string literal parser also reduces a pair of backslashes to one data character. The preceding table provides an example.

By default, BYTEA octets are generated in the hex format. If you change the value of the bytea_output parameter to escape, non-printable octets are converted to their equivalent three-digit octal values and the values are preceded by one backslash. Most printable octets are generated in their standard representation format in the client character set. The following sample code provides an example:

    set bytea_output = 'escape';

    select 'abc \153\154\155 \052\251\124'::bytea;
         bytea
    ----------------
     abc klm *\251T

The octet with decimal value 92 (backslash) is doubled in the output.

Output escaped octets of BYTEA

Based on the frontend that you use, you may need to do more to process escaped and unescaped BYTEA strings. For example, you may need to escape line feeds and carriage returns even if your interface automatically translates them.

BINARY and VARBINARY

BINARY data is a binary value with a fixed length of N bytes. Valid values of N: 1 to 8300.

The BINARY data requires N bytes of storage. Trailing zeros are added to the BINARY data to meet the maximum column size.

VARBINARY data is a binary value with a maximum length of N bytes. Valid values of N: 1 to 4194304.

Example

create table bvar (col1 BINARY (10), col2 VARBINARY (10));
create table t (col1 BINARY(10), COL2 VARBINARY(10));
insert into t values('0x4D795','0x39274D');
select * from t;
    col1    |   col2   
------------+----------
 0x4D795    | 0x39274D
(1 row)

BLOB

The BLOB type is used to store unstructured binary large objects. BLOB data can be up to 16 MB in size.

You do not need to define the maximum number of characters for BLOB data in columns in the same way as defining the maximum number of characters for VARBINARY data or data of other variable-length types. The following sample code provides an example on how to define a BLOB column:

create table blob_content ( 
  id NUMBER PRIMARY KEY, 
  blob_column BLOB );

You can use the following functions to perform operations on BLOB data:

• To initialize empty BLOB data, use the EMPTY_BLOB function. You can also initialize BLOB data by directly inserting BLOB data into a table.

• To convert a binary value to a value of the BLOB type, use the TO_LOB or TO_BLOB function.