Describes the Firebolt implementation of the BYTEA
data type
BYTEA
data type.
Not all functions support the BYTEA
data type currently. For more information, see BYTEA functions
BYTEA
data type is a variable length binary string data type, commonly used to store binary data, like images, other multimedia files, or raw bytes of information. A binary string is a sequence of bytes - unlike TEXT
, there is no character set. The BYTEA
data type is nullable.
BYTEA
type can be cast to and from the TEXT
data type. A cast from BYTEA
to TEXT
will interpret the binary string to a hexadecimal representation with \x
as a prefix. For example SELECT 'a'::BYTEA::TEXT
returns \x61
.
Cast from TEXT
to BYTEA
supports two formats, hex and escaped:
HexTEXT
type data must start with \x
. Characters \n
, \t
, \r
and ’ ’ are ignored if they are not in sequence of two characters representing one byte. Each character must be in one of the following ranges: a-f
, A-F
, 0-9
.\x aa
is a valid hex format, but \xa a
is invalid.
Escape\\
-> \
. One backslash must be followed by 3 numbers representing octal value (base 8) in range of 000-377
. For example, a \375
In addition to casting, the ENCODE and DECODE functions can be used to represent TEXT
as BYTEA
and vice versa, but will behave slightly differently. For example, SELECT ENCODE('1'::BYTEA, 'HEX');
returns 31
, while SELECT CAST('1'::BYTEA as TEXT);
returns \x31
, both of type TEXT
.
BYTEA
comparison operator will work as lexicographical comparison but with bytes. Two empty BYTEA
type expressions are equal. If two BYTEA
type expressions have equivalent bytes and are of the same length, then they are equal. In a greater than (>
) or less than (<
) comparison, two BYTEA
type expressions are compared byte by byte, and the first mismatching byte defines which is greater or less than the other.
Examples:
SELECT '\xa3'::BYTEA > '\xa2'::BYTEA;
returns TRUE
.
SELECT '\xa3'::BYTEA = '\xa300'::BYTEA;
returns FALSE
.
SELECT '\xa3'::BYTEA < '\xa300'::BYTEA;
returns TRUE
.
standard_conforming_strings
, which controls whether strings are parsed with or without escaping.
Similar to CAST from TEXT
to BYTEA
, the two text formats hex and escape are supported.
Examples:
BYTEA
is the hexadecimal representation of the bytes in lower case prefixed by \x
(Note: in JSON \
is escaped).
Example:
BYTEA
from external sourceTEXT
data type)
and then cast to type BYTEA
.
For CSV, TSV, or JSON files:
The input data are read exactly as they are in the source, and then cast to data type BYTEA
.
\x
- data starting with the characters \x
will throw an error on ingest. Any data not starting with the characters \x
will be copied as bytes to the column of data type BYTEA
.