Blob Functions
This section describes functions and operators for examining and manipulating BLOB values.
| Function | Description |
|---|---|
| [`arg1 | |
base64(blob) | Alias for to_base64. |
decode(blob) | Converts blob to VARCHAR. Fails if blob is not valid UTF-8. |
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
| Description | Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(arg1, arg2, ...) and list_concat(list1, list2, ...). |
| Example 1 | 'Duck' || 'DB' |
| Result | Goose |
| Example 2 | [1, 2, 3] || [4, 5, 6] |
| Result | [1, 2, 3, 4, 5, 6] |
| Example 3 | '\xAA'::BLOB || '\xBB'::BLOB |
| Result | \xAA\xBB |
decode(blob)
| Description | Converts blob to VARCHAR. Fails if blob is not valid UTF-8. |
| Example | decode('\xC3\xBC'::BLOB) |
| Result | ü |
encode(string)
| Description | Converts the string to BLOB. Converts UTF-8 characters into literal encoding. |
| Example | encode('my_string_with_ü') |
| Result | my_string_with_\xC3\xBC |
from_base64(string)
| Description | Converts a base64 encoded string to a character string (BLOB). |
| Example | from_base64('QQ==') |
| Result | A |
hex(blob)
| Description | Converts blob to VARCHAR using hexadecimal encoding. |
| Example | hex('\xAA\xBB'::BLOB) |
| Result | AABB |
| Alias | to_hex |
md5(blob)
| Description | Returns the MD5 hash of the blob as a VARCHAR. |
| Example | md5('\xAA\xBB'::BLOB) |
| Result | 58cea1f6b2b06520613e09af90dc1c47 |
md5_number(blob)
| Description | Returns the MD5 hash of the blob as a HUGEINT. |
| Example | md5_number('\xAA\xBB'::BLOB) |
| Result | 94525045605907259200829535064523132504 |
octet_length(blob)
| Description | Number of bytes in blob. |
| Example | octet_length('\xAA\xBB'::BLOB) |
| Result | 2 |
read_blob(source)
| Description | 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. |
| Example | read_blob('hello.bin') |
| Result | hello\x0A |
repeat(blob, count)
| Description | Repeats the blob count number of times. |
| Example | repeat('\xAA\xBB'::BLOB, 5) |
| Result | \xAA\xBB\xAA\xBB\xAA\xBB\xAA\xBB\xAA\xBB |
sha1(blob)
| Description | Returns a VARCHAR with the SHA-1 hash of the blob. |
| Example | sha1('\xAA\xBB'::BLOB) |
| Result | 65b1e351a6cbfeb41c927222bc9ef53aad3396b0 |
sha256(blob)
| Description | Returns a VARCHAR with the SHA-256 hash of the blob. |
| Example | sha256('\xAA\xBB'::BLOB) |
| Result | d798d1fac6bd4bb1c11f50312760351013379a0ab6f0a8c0af8a506b96b2525a |
to_base64(blob)
| Description | Converts a blob to a base64 encoded string. |
| Example | to_base64('A'::BLOB) |
| Result | QQ== |
| Alias | base64 |
unbin(value)
| Description | Converts a value from binary representation to a blob. |
| Example | unbin('0110') |
| Result | \x06 |
| Alias | from_binary |
unhex(value)
| Description | Converts a value from hexadecimal representation to a blob. |
| Example | unhex('2A') |
| Result | * |
| Alias | from_hex |