Binary (Byte Array) Operations
The BINARY and VARBINARY data types allows storage of binary strings.
A binary string is a sequence of octets (or bytes). Binary strings are
distinguished from character strings in two ways. First, binary
strings specifically allow storing octets of value zero and other
“non-printable” octets (usually, octets outside the decimal range 32
to 126). Character strings disallow zero octets, and also disallow
any other octet values and sequences of octet values that are invalid
according to the database's selected character set encoding. Second,
operations on binary strings process the actual bytes, whereas the
processing of character strings depends on locale settings. In short,
binary strings are appropriate for storing data that the programmer
thinks of as “raw bytes”, whereas character strings are appropriate
for storing text.
Binary literals
BINARY and VARBINARY literals are specified by string literals of
hexadecimal digits with an x prefix: x'45F0AB'. Such a literal
must have an even number of characters, and all characters must be
legal hexadecimal digits. A multi-part literal can be specified as
the concatenation of multiple literals, e.g.: x'AB' 'CD'.
Converting a string to a binary value produces a binary value
containing the bytes of the encoding of the characters of the string.
Binary value operations
Binary values support bitwise operations, although there is no syntax
yet to express these operations. They can be aggregated using the
aggregation functions BIT_AND, BIT_OR, and BIT_XOR.
| Operation | Description | Examples |
|---|
|| | Concatenation of binary values | x'ab' || x'cd' => x'ABCD' |
GUNZIP(binary) | Decompresses a binary string using the GZIP algorithm.
If the input data is not in the gzip format this function fails at runtime.
The output is the decompressed data as a VARCHAR string.
If the input is NULL, NULL is returned. | SELECT gunzip(x'1f8b08000000000000ff4b4bcd49492d4a0400218115ac07000000') => feldera |
OCTET_LENGTH(binary) | Returns the number of bytes in the binary | OCTET_LENGTH(x'0abc') => 2 |
OVERLAY(binary1 PLACING binary2 FROM integer1 [ FOR integer2 ]) | Generate a binary string that replaces substring of binary1 with binary2.
The substring of binary1 starts at the byte specified by integer1 and extends for integer2 bytes.
If integer1 is greater than the byte length of binary1, concatenation is performed.
If integer2 is smaller than the byte length of binary2, the substring still gets replaced by the entirety of binary2, producing a binary string with greater byte length than binary1 | SELECT overlay(x'1234567890'::bytea placing x'0203' from 2 for 3) => x'12020390' |
POSITION(binary1 IN binary2) | Returns the position of the first occurrence of binary1 in binary2. The first offset is 1. If binary1 isn't found in binary2, 0 is returned | POSITION(x'20' IN x'102023') => 2 |
SUBSTRING(binary FROM integer) | Generate a substring of binary starting at the given offset in bytes. The first offset is 1. If the start position integer is less than 1, it is treated as 1 | SUBSTRING(x'123456', 3) => x'56' |
SUBSTRING(binary FROM integer1 FOR integer2) | Generate a substring of binary starting at the given offset in bytes with the given length. The first offset is 1. If the start position integer is less than 1, it is treated as 1 | SUBSTRING(x'1234567890' FROM 3 FOR 2) => x'5678' |
TO_HEX(binary) | Generate a VARCHAR string describing the value in hexadecimal | TO_HEX(x'0abc') => '0ABC' |
TO_INT(binary) | Generate an INT value from the first 4 bytes of the binary, where the 0-th byte is the MSB | TO_INT(x'0abc') => 2748 |