Use binary functions and operators - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL is compatible with the binary functions and operators of PostgreSQL. This topic describes the binary functions and operators that are supported by AnalyticDB for PostgreSQL and provides examples on how to use the binary functions and operators.

For information about the binary functions and operators of PostgreSQL, see Binary String Functions and Operators.

Binary string functions and operators

Binary operators are operators that operate on two operands. In computer science, binary operators are used to perform bitwise operations, such as bitwise AND (&), bitwise OR (|), bitwise XOR (^), bitwise NOT (~), left shift (<<), and right shift (>>).

Function	Data type of the return value	Description	Example	Sample result
`string \|\| string`	`BYTEA`	Concatenates two strings.	`'\\Post'::bytea \|\| '\047gres\\000'::bytea`	`\\Post'gres\000`
`octet_length(string)`	`INT`	Returns the number of bytes in a binary string.	`octet_length('jo\000se'::bytea)`	`5`
`overlay(string placing string from int [for int])`	`BYTEA`	Replaces a specific substring in a string with another string.	`overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3)`	`T\\002\\003mas`
`position(substring in string)`	`INT`	Returns the starting position of a specific substring in a string.	`position('\000om'::bytea in 'Th\000omas'::bytea)`	`3`
`substring(string [from int] [for int`	`BYTEA`	Extracts a substring from a string.	`substring('Th\000omas'::bytea from 2 for 3)`	`h\000o`
`trim([both] bytes from string)`	`BYTEA`	Removes the longest sequence that contains only the bytes specified by the `bytes` parameter from the start and the end of the string specified by the `string` parameter.	`trim('\000\001'::bytea from '\000Tom\001'::bytea)`	`Tom`

Binary functions

A binary function is a function that takes two input parameters and returns a result. In programming, binary functions are used to represent algorithms that require two input parameters to return one result.

Function	Data type of the return value	Description	Example	Sample result
`bytea(string bytea,bytes bytea)`	`BYTEA`	Removes the longest sequence that contains only bytes specified by the `bytes` parameter from the start and the end of the string specified by the `string` parameter.	`btrim('\000trim\001'::bytea, '\000\001'::bytea)`	`trim`
`decode(string text,format text)`	`BYTEA`	Decodes binary data from a textual representation of a string specified by the `string` parameter. Valid values for the `format` parameter are the same as those in the `encode` function.	`decode('123\000456', 'escape')`	`123\000456`
`encode(data bytea,format text)`	`TEXT`	Encodes binary data into a textual representation. Valid values for the format parameter: `base64`, `hex`, and `escape`. `escape` converts zero bytes and high-bit-set bytes to octal sequences (`\nnn`) and precedes each backslash (\) with a backslash (\) as the escape character.	`encode('123\000456'::bytea, 'escape')`	`123\000456`
`get_bit(string,offset)`	`INT`	Extracts a bit from a string.	`get_bit('Th\000omas'::bytea, 45)`	`1`
`get_byte(string,offset)`	`INT`	Extracts a byte from a string.	`get_byte('Th\000omas'::bytea, 4)`	`109`
`length(string)`	`INT`	Returns the length of a binary string.	`length('jo\000se'::bytea)`	`5`
`md5(string)`	`TEXT`	Calculates the MD5 hash value of a string specified by the `string` parameter and returns the result in hexadecimal format.	`md5('Th\000omas'::bytea)`	`8ab2d3c9689aaf18b4958c334c82d8b1`
`set_bit(string,offset,newvalue)`	`BYTEA`	Sets a bit in a string.	`set_bit('Th\000omas'::bytea, 45, 0)`	`Th\000omAs`
`set_byte(string,offset,newvalue)`	`BYTEA`	Sets a byte in a string.	`set_byte('Th\000omas'::bytea, 4, 64)`	`Th\000o@as`
`sha224(bytea)`	`BYTEA`	Returns the `SHA-224` hash value of a string.	`sha224('abc')`	`\x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7`
`sha256(bytea)`	`BYTEA`	Returns the `SHA-256` hash value of a string.	`sha256('abc')`	`\xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad`
`sha384(bytea)`	`BYTEA`	Returns the `SHA-384` hash value of a string.	`sha384('abc')`	`\xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7`
`sha512(bytea)`	`BYTEA`	Returns the `SHA-512` hash value of a string.	`sha512('abc')`	`\xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f`