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. Casts from bytea will error if the string is not valid input for the destination type.

To bytea

You can explicitly cast text to bytea.


SELECT '\xDEADBEEF'::bytea AS bytea_val;

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