Binary Type
Relyt supports the bytea data type for storing binary data.
| Name | Storage size | Value range | Description |
|---|---|---|---|
bytea | Maximum: 500 MB | Binary data of any length | Stores variable-length binary data. |
Restrictions
Currently, operators such as >, >=, =, !=, <, <=, and others are not supported for the bytea data type.
Usage examples
This section provides examples of creating tables, inserting data, and performing queries with the bytea data type.
Create a table
The following example creates a table called test1, where the col2 column is of the bytea data type.
CREATE TABLE test1 (
col1 bigint,
col2 bytea
)
USING pg_zdb
DISTRIBUTED BY (col1);
Do not use a bytea field directly as the distribution key for DISTRIBUTED BY, as bytea fields are typically large and can lead to inefficient hash calculations, affecting write performance.
Insert data
The bytea data type allows data to be inserted in three formats: hexadecimal, escape sequences, and ASCII string codes.
Insert deadbeef in hexadecimal:
INSERT INTO test1 VALUES(1, '\xdeadbeef'); -- Hexadecimal format (using 0-9 and a-f, with an even number of characters)
INSERT INTO test1 VALUES(1, '\x0badbeef'); -- Hexadecimal format (if the character count is odd, prepend 0, e.g., '\xbadbeef' becomes '\x0badbeef')
Insert deadbeef using escape sequences:
INSERT INTO test1 VALUES(2, '\336\255\276\357');
-- Hexadecimal and octal mappings for 'deadbeef' are as follows:
-- de ad be ef -- Hexadecimal
-- 336 255 276 357 -- Octal
Write deadbeef directly as an ASCII string:
INSERT INTO test1 VALUES(3, 'deadbeef');
-- Directly insert the string 'deadbeef', storing the data in text format, not binary format
Insert an empty string or NULL:
INSERT INTO test1 VALUES(2, '');
-- Insert an empty string, which indicates the field stores a null value (but not NULL)
INSERT INTO test1 VALUES(3, NULL);
-- Insert NULL, indicating the field has no value (which is different from an empty string)
Query data
Queries on bytea type data return results in either hexadecimal or octal format, depending on the bytea_output parameter setting.
To display in hexadecimal:
SET bytea_output='hex';
SELECT * FROM test1;
To display in octal (escaped):
SET bytea_output='escape';
SELECT * FROM test1;
Differences between binary and string types
A binary string is a sequence of bytes. The key differences between binary strings and regular strings (character types) lie primarily in two aspects:
Storage content limitations:
Binary strings operate directly on raw bytes, while regular strings rely on locale settings. For example, sorting and comparing regular strings are affected by character encoding and sorting rules, whereas binary strings are compared byte by byte. As a result, binary strings are ideal for storing raw byte data, while regular strings are used for storing textual information.
Processing differences:
The bytea type supports two input/output formats: 'Hexadecimal (hex)' and PostgreSQL's traditional 'Escape (escape)' format. Both formats can be used for data input, while the output format is controlled by the bytea_output configuration parameter, with the default set to hexadecimal (hex).