Binary types-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for Oracle supports four binary data types: BINARY, VARBINARY, BLOB, and BYTEA. Each stores raw byte sequences without character set interpretation, making them suitable for images, serialized objects, encrypted data, and other non-text content.

Type	Storage size	Description
`BINARY`	N bytes (fixed)	Fixed-length binary string. Valid values of N: 1–8300.
`VARBINARY`	Actual length in bytes	Variable-length binary string. Valid values of N: 1–4,194,304.
`BLOB`	Actual length + 1 byte (if < 127 bytes) or + 4 bytes (if ≥ 127 bytes)	Variable-length binary large object, up to 16 MB.
`BYTEA`	1 or 4 bytes + actual length	Variable-length binary string (PostgreSQL-native type).

Binary strings differ from character strings in two key ways:

Zero bytes and non-printable bytes: Binary strings store any byte value, including zero (0x00) and non-printable bytes (outside the decimal range 32–126). Character strings cannot.
Byte-level operations: Operations on binary strings compare and process raw bytes. Character string operations depend on locale settings and character set encoding.

Use binary types when your application treats data as raw bytes — for example, storing file content, cryptographic hashes, or protocol buffers. Use character types when storing human-readable text.

BINARY

BINARY(N) stores a fixed-length binary value of exactly N bytes, where N is between 1 and 8300.

Syntax:

BINARY(N)

When a stored value is shorter than N bytes, PolarDB right-pads it with trailing zero bytes to meet the maximum column size.

Example:

CREATE TABLE t (col1 BINARY(10), col2 VARBINARY(10));
INSERT INTO t VALUES ('0x4D795', '0x39274D');
SELECT * FROM t;

Expected output:

    col1    |   col2
------------+----------
 0x4D795    | 0x39274D
(1 row)

VARBINARY

VARBINARY(N) stores a variable-length binary value of up to N bytes, where N is between 1 and 4,194,304.

Syntax:

VARBINARY(N)

BLOB

BLOB stores binary large objects (BLOBs) of up to 16 MB. No length parameter is required in the column definition.

Syntax:

BLOB

Define a BLOB column:

CREATE TABLE blob_content (
  id     NUMBER PRIMARY KEY,
  blob_column BLOB
);

Work with BLOB data:

To initialize an empty BLOB, use EMPTY_BLOB(). Alternatively, insert data directly.
To convert a binary value to BLOB, use TO_LOB() or TO_BLOB().

The BLOB type corresponds to the SQL standard BINARY LARGE OBJECT. Its input format differs from BYTEA, but most functions and operators are interchangeable.

BYTEA

BYTEA is the PostgreSQL-native variable-length binary type. It accepts two input formats — hex format and escape format — and outputs in hex format by default.

Syntax:

BYTEA

Use BYTEA for PostgreSQL-native workflows. Use BLOB when working with Oracle-compatible code.

Hex format

Hex format encodes each byte as two hexadecimal characters, most significant nibble first. The string is prefixed with \x.

Hex digits are case-insensitive (\xDEADBEEF and \xdeadbeef are equivalent).
Whitespace is allowed between digit pairs, but not within a pair or inside the \x prefix.
Hex format converts faster than escape format and is compatible with a wider range of external applications and protocols. It is the recommended format for new applications.

Example:

SELECT '\xDEADBEEF';

Escape format

Escape format represents binary data as a sequence of ASCII characters. Bytes that have no printable ASCII representation are written as three-digit octal escape sequences preceded by a backslash.

Escape format can be convenient when inspecting binary data manually, but it blurs the distinction between binary strings and character strings. Prefer hex format in new applications.

Required escapes for BYTEA input:

Decimal value	Description	Escaped input	Example	Hex
0	Zero byte	`'\000'`	`'\000'::bytea`	`\x00`
39	Single quotation mark	`''''` or `'\047'`	`''''::bytea`	`\x27`
92	Backslash	`'\\'` or `'\134'`	`'\\'::bytea`	`\x5c`
0–31, 127–255	Non-printable bytes	`'\xxx'` (octal)	`'\001'::bytea`	`\x01`

Single quotation marks must be doubled because SQL's string literal parser consumes the outermost pair. Backslashes must also be doubled for the same reason. Non-printable bytes may optionally be left unescaped depending on locale settings.

Example — setting escape output format:

SET bytea_output = 'escape';
SELECT 'abc \153\154\155 \052\251\124'::bytea;

Expected output:

     bytea
----------------
 abc klm *\251T

BYTEA output representation in escape format:

Decimal value	Description	Output representation	Example	Output
92	Backslash	`\\`	`'\134'::bytea`	`\\`
0–31, 127–255	Non-printable bytes	`\xxx` (octal)	`'\001'::bytea`	`\001`
32–126	Printable bytes	Client character set representation	`'\176'::bytea`	`~`

Depending on the client or interface you use, you may need to escape additional characters such as line feeds and carriage returns, even if your interface translates them automatically.

Choose a binary type

Scenario	Recommended type
Oracle-compatible code, large binary objects up to 16 MB	`BLOB`
Fixed-length binary values, e.g., hash digests of a known size	`BINARY`
Variable-length binary values with a bounded maximum size	`VARBINARY`
PostgreSQL-native binary data, integration with PostgreSQL tools	`BYTEA`