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.
Converting a string to a binary value produces a binary value
containing the bytes of the encoding of the characters of the string.
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 |
LEFT ( binary, count ) | Returns first count bytes in the byte array. If any argument is NULL, return NULL. | left(x'abcdef', 2) => abcd |
MD5(binary) | Calculates an MD5 128-bit checksum of the argument and returns it as a hex VARCHAR value.
If the input is NULL, NULL is returned. | SELECT md5(x'0123456789ABCDEF') => |
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 |
RIGHT ( binary, count ) | Returns last count bytes in the byte array. If any argument is NULL, return NULL. | right(x'abcdef', 2) => cdef |
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. Negative lengths or offsets larger than length return an empty result. | 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 |