Functions for binary data

Functions for binary data are functions for working with values of binary type (BLOB).

concat()

Description

Concatenates multiple strings, arrays or binary values.
Usage

concat(argument1, argument2, {…​).

Empty values (NULL) are ignored.

See also Operator ||.

See example 
SELECT
    concat('\xAA'::BLOB, '\xff'::BLOB) as result_blob,
    concat('I', ' ', 'love', ' ', 'Tengri') as result_string,
    concat(['T', 'e'], ['n', 'g', 'r', 'i']) as result_array;
+-------------+---------------+---------------+
| result_blob | result_string |  result_array |
+-------------+---------------+---------------+
| \xAA\xFF    | I love Tengri | {T,e,n,g,r,i} |
+-------------+---------------+---------------+

md5()

Description

Returns a hash MD5 of data from argument as a string (VARCHAR).
Usage

md5(argument)

The argument can be binary data or a string.

See examples 
SELECT
    md5('\xAA\xFF'::BLOB) as md5_hash;
+----------------------------------+
|             md5_hash             |
+----------------------------------+
| 1fab7f7621f5ddc051ebd1f2c63c4665 |
+----------------------------------+
SELECT
    md5('Tengri') as md5_hash;
+----------------------------------+
|             md5_hash             |
+----------------------------------+
| 846b02d31131a10bd6ac0ba189c65bef |
+----------------------------------+

sha1()

Description

Returns a hash SHA-1 of the data from argument as a string (VARCHAR).
Usage

sha1(argument)

The argument can be binary data or a string.

See examples 
SELECT
    sha1('\xAA\xFF'::BLOB) as sha1_hash;
+------------------------------------------+
|                 sha1_hash                |
+------------------------------------------+
| e89b0db325637edfacde04a76005c492e2c5aeca |
+------------------------------------------+
SELECT
    sha1('Tengri') as sha1_hash;
+------------------------------------------+
|                 sha1_hash                |
+------------------------------------------+
| b514525a19995a2442d7565bfd9bb42d9dc71a13 |
+------------------------------------------+

sha256()

Description

Returns a hash SHA-256 of the data from argument as a string (VARCHAR).
Usage

sha256(argument)

The argument can be binary data or a string.

See example 
SELECT
    sha256('\xAA\xFF'::BLOB) as sha256_hash;
+------------------------------------------------------------------+
|                            sha256_hash                           |
+------------------------------------------------------------------+
| 768318522cac43261e8ef4946c2296a3643d523a8d5bda8ff5b82aa64470421a |
+------------------------------------------------------------------+
SELECT
    sha256('Tengri') as sha256_hash;
+------------------------------------------------------------------+
|                            sha256_hash                           |
+------------------------------------------------------------------+
| 8aaacef66663b14ee7c5a03dbaec7b40f0f3bf17bd12d2ed4f9aaad0e10a0d77 |
+------------------------------------------------------------------+

Operator ||

Description

Concatenates multiple strings, arrays or binary values.
Usage

argument1 || argument2 || argument2 || ....

Empty values (NULL) are ignored.

See also concat().

See examples 
SELECT
    '\xAA'::BLOB || '\xff'::BLOB as result_blob,
    'I' || ' ' || 'love' || ' ' || 'Tengri' as result_string,
    ['T', 'e'] || ['n', 'g', 'r', 'i'] as result_array;
+-------------+---------------+---------------+
| result_blob | result_string |  result_array |
+-------------+---------------+---------------+
|             | I love Tengri | {T,e,n,g,r,i} |
+-------------+---------------+---------------+

Note that the values in the result_blob column are not displayed in the output (because they are of type BLOB).

Using the DESCRIBE expression, output the data types for all columns in the table created in the same way as in the previous example:

CREATE TABLE concat AS

SELECT
    '\xAA'::BLOB || '\xff'::BLOB as result_blob,
    'I' || ' ' || 'love' || ' ' || 'Tengri' as result_string,
    ['T', 'e'] || ['n', 'g', 'r', 'i'] as result_array;

DESCRIBE TABLE concat;
+---------------+-------------+------+------+---------+-------+
|  column_name  | column_type | null |  key | default | extra |
+---------------+-------------+------+------+---------+-------+
| result_blob   | BLOB        | YES  | null | null    | null  |
+---------------+-------------+------+------+---------+-------+
| result_string | VARCHAR     | YES  | null | null    | null  |
+---------------+-------------+------+------+---------+-------+
| result_array  | VARCHAR[]   | YES  | null | null    | null  |
+---------------+-------------+------+------+---------+-------+