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

overlay

Added in v2.5.0.

Replaces a substring from a binary string with another bytea value starting at position start_int (>0) for count_int bytes.

overlay (bytea_value PLACING new_value FROM start_int [FOR count_int]) -> bytea

Example A

SELECT overlay('\x616263646566'::bytea placing '\x313233'::bytea from 2 for 4);
------RESULT
\x6131323366

Example B

-- If `count_int` is 0, insert without removing any bytes.
SELECT overlay('\x616263646566'::bytea placing '\x313233'::bytea from 4 for 0);
------RESULT
\x616263313233646566

Example C

-- If `count_int` < 0, insert and append trailing content starting from `start_int` + `count_int`.
SELECT overlay('\x616263646566'::bytea placing '\x313233'::bytea from 4 for -2);
------RESULT
\x6162633132336263646566

Example D

-- If `count_int` is omitted, the length of new_value is used.
SELECT overlay('\x616263646566'::bytea placing '\x9999'::bytea from 3);
------RESULT
\x616299996566

position

Added in v2.5.0.

Returns the index of the first occurrence of a bytea substring, or zero if not found.

position ( substring bytea IN string bytea ) -> integer

SELECT position('\x6c6f'::bytea in '\x68656c6c6f2c20776f726c64'::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

||

Added in v2.5.0.

Concatenates two binary strings.

bytea || bytea -> bytea

SELECT '\x123456'::bytea || '\x789a00bcde'::bytea;
------RESULT
\x123456789a00bcde