bytea type

The bytea data type allows the storage of binary strings or what is typically thought of as “raw bytes”. Materialize supports both the typical formats for input and output: the hex format and the historical PostgreSQL escape format. The hex format is preferred.

Hex format strings are preceded by \x and escape format strings are preceded by \.

For more information about bytea, see the PostgreSQL binary data type documentation.

Detail Info
Quick Syntax '\xDEADBEEF' (hex), '\000' (escape)
Size 1 or 4 bytes plus the actual binary string
Catalog name pg_catalog.bytea
OID 17


Hex format

' \x binary string '

In some cases, the initial backslash may need to be escaped by doubling it (\\). For more information, see the PostgreSQL documentation on string constants.

Escape format

' \ binary string '

In the escape format, octet values can be escaped by converting them into their three-digit octal values and preceding them with backslashes; the backslash itself can be escaped as a double backslash. While any octet value can be escaped, the values in the table below must be escaped.

Decimal octet value Description Escaped input representation Example Hex representation
0 zero octet '\000' '\000'::bytea \x00
39 single quote '''' or '\047' ''''::bytea \x27
92 backslash '\\' or '\134' '\\'::bytea \x5c
0 to 31 and 127 to 255 “non-printable” octets '\xxx' (octal value) '\001'::bytea \x01


Valid casts

From bytea

You can cast bytea to text by assignment.

WARNING! Casting a bytea value to text unconditionally returns a hex-formatted string, even if the byte array consists entirely of printable characters. See handling character data for alternatives.

To bytea

You can explicitly cast text to bytea.

Handling character data

Unless a text value is a hex-formatted string, casting to bytea will encode characters using UTF-8:

SELECT 'hello 👋'::bytea;

The reverse, however, is not true. Casting a bytea value to text will not decode UTF-8 bytes into characters. Instead, the cast unconditionally produces a hex-formatted string:

SELECT '\x68656c6c6f20f09f918b'::bytea::text

To decode UTF-8 bytes into characters, use the convert_from function instead of casting:

SELECT convert_from('\x68656c6c6f20f09f918b', 'utf8') AS text;
 hello 👋


SELECT '\xDEADBEEF'::bytea AS bytea_val;

SELECT '\000'::bytea AS bytea_val;
Back to top ↑