Binary string functions and operators

bit_length

Returns number of bits in the binary string, which is 8 times the octet_length.

bit_length ( bytea ) -> integer

SELECT bit_length('😇'::bytea);
------RESULT
32

md5

Returns the MD5 hash of the binary string as a hexadecimal.

md5 ( bytea ) -> varchar

SELECT md5('risingwave'::bytea);
------RESULT
7ad0245ddf6c26f4f6ae269a644ac00a

octet_length, length

Returns number of bytes in the binary string.

octet_length ( bytea ) -> integer
-- or
length ( bytea ) -> integer

SELECT octet_length('😇'::bytea);
------RESULT
4

sha1

Returns the SHA-1 hash of the binary string.

sha1 ( bytea ) -> bytea

SELECT sha1('risingwave'::bytea);
------RESULT
\x48d3478f8350c86fa2e6f65a5883c2046523c8bb

sha224

Returns the SHA-224 hash of the binary string.

sha224 ( bytea ) -> bytea

SELECT sha224('risingwave'::bytea);
------RESULT
\xb898defab7c2e2f41c9a494a22e3567274b48123625f96008439e0bb

sha256

Returns the SHA-256 hash of the binary string.

sha256 ( bytea ) -> bytea

SELECT sha256('risingwave'::bytea);
------RESULT
\x73ab8557da7bd59f798600fb1d18d18967bc763638fc456f477799437f229e06

sha384

Returns the SHA-384 hash of the binary string.

sha384 ( bytea ) -> bytea

SELECT sha384('risingwave'::bytea);
------RESULT
\x7f6f71a068f04e3ed6338e06fec75941b48a2dadff58dffc4c39211b1dcc4a5f000168d1be49fd7b7e44094e7a7e627e

sha512

Returns the SHA-512 hash of the binary string.

sha512 ( bytea ) -> bytea

SELECT sha512('risingwave'::bytea);
------RESULT
\x3d6d6078c75ad459cdc689216d5de35bd6d9a9a50b9bed96417aaf7ad25057b37460564f0ad23a589c655eda45026096a6bab08b3c863f0425cbfea64b5f84a8

substr

Extracts a substring from a binary string (bytea) starting at position start_int for count_int bytes. If count_int is omitted, the substring extends to the end of the bytea value.

substr ( bytea_value, start_int, [, count_int] ) -> bytea

SELECT substr('abcde'::bytea, 2, 7);
------RESULT
\x62636465

SELECT substr('abcde'::bytea, -2, 5);
------RESULT
\x6162