Skip to main content

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 with 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'.

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.

OperationDescriptionExamples
||Concatenation of binary valuesx'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 binaryOCTET_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 returnedPOSITION(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 1SUBSTRING(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 1SUBSTRING(x'1234567890' FROM 3 FOR 2) => x'5678'
TO_HEX(binary)Generate a VARCHAR string describing the value in hexadecimalTO_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 MSBTO_INT(x'0abc') => 2748