Blob Functions

This section describes functions and operators for examining and manipulating BLOB values.

Function	Description
arg1 || arg2	Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(arg1, arg2, ...) and list_concat(list1, list2, ...).
base64(blob)	Alias for to_base64.
decode(blob[, on_error])	Converts blob to VARCHAR. The optional on_error parameter controls handling of invalid UTF-8: 'strict' (default, throws error), 'replace' (replaces invalid characters with ?), or 'ignore' (removes invalid characters).
encode(string)	Converts the string to BLOB. Converts UTF-8 characters into literal encoding.
from_base64(string)	Converts a base64 encoded string to a character string (BLOB).
from_binary(value)	Alias for unbin.
from_hex(value)	Alias for unhex.
hex(blob)	Converts blob to VARCHAR using hexadecimal encoding.
md5(blob)	Returns the MD5 hash of the blob as a VARCHAR.
md5_number(blob)	Returns the MD5 hash of the blob as a HUGEINT.
octet_length(blob)	Number of bytes in blob.
read_blob(source)	Returns the content from source (a filename, a list of filenames, or a glob pattern) as a BLOB. See the read_blob guide for more details.
repeat(blob, count)	Repeats the blob count number of times.
sha1(blob)	Returns a VARCHAR with the SHA-1 hash of the blob.
sha256(blob)	Returns a VARCHAR with the SHA-256 hash of the blob.
to_base64(blob)	Converts a blob to a base64 encoded string.
to_hex(blob)	Alias for hex.
unbin(value)	Converts a value from binary representation to a blob.
unhex(value)	Converts a value from hexadecimal representation to a blob.

arg1 || arg2

Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(arg1, arg2, ...) and list_concat(list1, list2, ...).

Query

SELECT 'Serene' || 'DB' AS string_concat, [1, 2, 3] || [4, 5, 6] AS list_concat, '\xAA'::BLOB || '\xBB'::BLOB AS blob_concat;

Result

 string_concat | list_concat   | blob_concat---------------+---------------+------------- SereneDB      | {1,2,3,4,5,6} | \\xaabb

decode(blob[, on_error])

Converts blob to VARCHAR. The optional on_error parameter controls handling of invalid UTF-8: 'strict' (default, throws error), 'replace' (replaces invalid characters with ?), or 'ignore' (removes invalid characters).

Query

SELECT decode('\xC3BC'::BLOB) AS decode, decode('\xAA'::BLOB, 'replace') AS decode_replace;

Result

 decode | decode_replace--------+---------------- ü      | ?

encode(string)

Converts the string to BLOB. Converts UTF-8 characters into literal encoding.

Query

SELECT encode('my_string_with_ü') AS encode;

Result

 encode--------------------------------------- \\x6d795f737472696e675f776974685fc3bc

from_base64(string)

Converts a base64 encoded string to a character string (BLOB).

Query

SELECT from_base64('QQ==') AS from_base64;

Result

 from_base64------------- \\x41

hex(blob)

Converts blob to VARCHAR using hexadecimal encoding. Alias: to_hex.

Query

SELECT hex('\xAABB'::BLOB) AS hex;

Result

 hex------ AABB

md5(blob)

Returns the MD5 hash of the blob as a VARCHAR.

Query

SELECT md5('\xAABB'::BLOB) AS md5;

Result

 md5---------------------------------- 58cea1f6b2b06520613e09af90dc1c47

md5_number(blob)

Returns the MD5 hash of the blob as a HUGEINT.

Query

SELECT md5_number('\xAABB'::BLOB) AS md5_number;

Result

 md5_number---------------------------------------- 94525045605907259200829535064523132504

octet_length(blob)

Number of bytes in blob.

Query

SELECT octet_length('\xAABB'::BLOB) AS octet_length;

Result

 octet_length--------------            2

read_blob(source)

Returns the content from source (a filename, a list of filenames, or a glob pattern) as a BLOB. See the read_blob guide for more details.

Query

SELECT content FROM read_blob('hello.bin');

Result

 content---------------- \x68656c6c6f0a

repeat(blob, count)

Repeats the blob count number of times.

Query

SELECT repeat('\xAABB'::BLOB, 5) AS repeat;

Result

 repeat------------------------- \\xaabbaabbaabbaabbaabb

sha1(blob)

Returns a VARCHAR with the SHA-1 hash of the blob.

Query

SELECT sha1('\xAABB'::BLOB) AS sha1;

Result

 sha1------------------------------------------ 65b1e351a6cbfeb41c927222bc9ef53aad3396b0

sha256(blob)

Returns a VARCHAR with the SHA-256 hash of the blob.

Query

SELECT sha256('\xAABB'::BLOB) AS sha256;

Result

 sha256------------------------------------------------------------------ d798d1fac6bd4bb1c11f50312760351013379a0ab6f0a8c0af8a506b96b2525a

to_base64(blob)

Converts a blob to a base64 encoded string. Alias: base64.

Query

SELECT to_base64('A'::BLOB) AS to_base64;

Result

 to_base64----------- QQ==

unbin(value)

Converts a value from binary representation to a blob. Alias: from_binary.

Query

SELECT unbin('0110') AS unbin;

Result

 unbin------- \\x06

unhex(value)

Converts a value from hexadecimal representation to a blob. Alias: from_hex.

Query

SELECT unhex('2A') AS unhex;

Result

 unhex------- \\x2a