PolarDB for PostgreSQL (Compatible with Oracle) supports binary data types for storing sequences of raw bytes.
Binary large objects
| Data type | Storage size | Description |
|---|---|---|
| BINARY | Same as the binary string length | Fixed-length binary string. Must be 1–8,300 bytes. |
| BLOB | Actual length + 1 byte (if < 127 bytes); actual length + 4 bytes (if ≥ 127 bytes) | Variable-length binary string. |
| VARBINARY | Same as the binary string length | Variable-length binary string. Must be 1–8,300 bytes. |
| BYTEA | 1 or 4 bytes plus the actual binary string length | Variable-length binary string. |
Binary strings differ from character strings in two ways:
Binary strings can store zero-value octets and other non-printable octets outside the range 32–126.
Operations on binary strings work directly on the raw bytes, whereas character string encoding and processing depend on locale settings.
BYTEA type
The BYTEA type supports two external input and output formats: hex and escape.
Hex format (recommended)
The hex format encodes each byte as two hexadecimal digits, with the most significant bits first. A hex-encoded binary string starts with \x to distinguish it from the escape format.
Escape format
The escape format is the traditional PostgreSQL representation. It encodes binary strings as ASCII character sequences, using special escape sequences for bytes that cannot be represented as printable ASCII.
The escape format blurs the distinction between binary strings and character strings, and its escape rules add complexity. Avoid this format for new applications.