Text functions

Text functions are functions for working with text strings (data of type VARCHAR).

`array_extract()`

Description	Extracts a character from the specified string or an item from the specified list.
Usage	`array_extract(list, index)`

Extracts a character from the specified string or an element from the specified list at the specified index. Negative values are allowed for index.

The numbering of elements starts with 1.

See example

SELECT
    array_extract('Tengri', 1)  AS result_1,
    array_extract('Tengri', 3)  AS result_2,
    array_extract('Tengri', -1) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| T        | n        | i        |
+----------+----------+----------+

SELECT
    array_extract([1,2,3,4], 1)  AS result_1,
    array_extract([1,2,3,4], 3)  AS result_2,
    array_extract([1,2,3,4], -1) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 3        | 4        |
+----------+----------+----------+

`array_slice()`

Description	Extracts a substring from the specified string or a list from the specified list.
Usage	`array_slice(list, begin, end)`
Aliases	`list_slice()`

Extracts a substring from the specified string or a list from the specified list at the given start and end coordinates. Negative values are allowed for the coordinates.

Numbering of elements starts with 1.

See example

SELECT
    array_slice('Tengri', 3, 4)  AS result_1,
    array_slice('Tengri', 0, 1)  AS result_2,
    array_slice('Tengri', 0, -2) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| ng       | T        | Tengr    |
+----------+----------+----------+

SELECT
    array_slice([1,2,3,4], 3, 4)  AS result_1,
    array_slice([1,2,3,4], 0, 1)  AS result_2,
    array_slice([1,2,3,4], 0, -2) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| {3,4}    | {1}      | {1,2,3}  |
+----------+----------+----------+

`bar()`

Description	Draws a bar at the height of the string.
Usage	`bar(x, min, max[, width])`

Draws a black bar at the height of the string. The width of the bar is proportional to x - min and equal to width when x = max.

The default is width = 80.

It is convenient to use for simulation of scale display in text form.

See example

SELECT
    t.generate_series AS value,
    bar(t.generate_series, 0, 5, 10) AS progress_bar
FROM generate_series(0, 5) AS t;

+-------+--------------+
| value | progress_bar |
+-------+--------------+
| 0     |              |
+-------+--------------+
| 1     | ██           |
+-------+--------------+
| 2     | ████         |
+-------+--------------+
| 3     | ██████       |
+-------+--------------+
| 4     | ████████     |
+-------+--------------+
| 5     | ██████████   |
+-------+--------------+

`bin()`

Description	Converts each character of a string to its binary representation.
Usage	`bin(string)`
Aliases	`to_binary()`

Inverse function: unbin()

See example

SELECT
    bin('T') AS result_1,
    bin('t') AS result_2,
    bin(' ') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 01010100 | 01110100 | 00100000 |
+----------+----------+----------+

`bit_length()`

Description	Returns the number of bits the string occupies in memory.
Usage	`bit_length(string)`

See example

SELECT
    bit_length('T')  AS result_latin,
    bit_length('Т')  AS result_cyr,
    bit_length('🔥') AS result_emoji;

+--------------+------------+--------------+
| result_latin | result_cyr | result_emoji |
+--------------+------------+--------------+
| 8            | 16         | 32           |
+--------------+------------+--------------+

`chr()`

Description	Returns the character corresponding to the value of the code ASCII or code Unicode, given in `argument`.
Usage	`chr(argument)`

See example

SELECT
    chr(84) || chr(78) || chr(71) || chr(82) || chr(105) AS chr;

+-------+
|  chr  |
+-------+
| TNGRi |
+-------+

`concat()`

Description	Concatenates multiple strings, arrays or binary values.
Usage	`concat(argument1, argument2, {…)`.

Empty values (NULL) are ignored.

`concat_ws()`

Description	Concatenates strings over a separator.
Usage	`concat_ws(separator, string1, string2, ...)`.

Concatenates multiple strings over the given separator.

If a NULL value is passed as a string, it is ignored. If a single string is passed, the same string is returned without adding a delimiter.

See example

SELECT
    concat_ws(' ', 'I', 'love', 'Tengri')       AS result_1,
    concat_ws(' ', 'I', NULL, 'love', 'Tengri') AS result_2,
    concat_ws('!', 'Tengri')                    AS result_3;

+---------------+---------------+----------+
| result_1      | result_2      | result_3 |
+---------------+---------------+----------+
| I love Tengri | I love Tengri | Tengri   |
+---------------+---------------+----------+

`contains()`

Description	Returns `true` if the specified string `string` contains the searched substring `search_string`.
Usage	`contains(string, search_string)`

See example

SELECT
    contains('I love Tengri', 'Tengri') AS check_name,
    contains('I love Tengri', 'TNGRi')  AS check_nickname;

+------------+----------------+
| check_name | check_nickname |
+------------+----------------+
| true       | false          |
+------------+----------------+

`ends_with()`

Description	Returns `true` if the string ends with the searched string.
Usage	`ends_with(string, search_string)`
Aliases	`suffix()`

See example

SELECT
    ends_with('I love Tengri', 'I')      AS result_1,
    ends_with('I love Tengri', 'Tengri') AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| false    | true     |
+----------+----------+

`format()`

Description	Formats a string using the fmt syntax.
Usage	`format(string, ...)`

Formats a string using the fmt syntax.

See example

SELECT
    format('I love {}', 'Tengri')         AS result_1,
    format('{0} love {1}', 'I','Tengri' ) AS result_2,
    format('Today is {}', today())        AS result_3;

+---------------+---------------+---------------------+
| result_1      | result_2      | result_3            |
+---------------+---------------+---------------------+
| I love Tengri | I love Tengri | Today is 2026-05-06 |
+---------------+---------------+---------------------+

`formatReadableDecimalSize()`

Description	Forms a text representation from a number of bytes in 1000 increments.
Usage	`formatReadableDecimalSize(num)`

Forms a human-readable text representation from a number of bytes in suitable units (kB, MB, GB, etc.) in 1000 increments.

See example

SELECT
    formatReadableDecimalSize(1000)         AS result_1,
    formatReadableDecimalSize(1000000)      AS result_2,
    formatReadableDecimalSize(111111111111) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1.0 kB   | 1.0 MB   | 111.1 GB |
+----------+----------+----------+

`formatReadableSize()`

Description	Forms a text representation from a number of bytes in 1024 increments.
Usage	`formatReadableSize(num)`
Aliases	`format_bytes()`, `pg_size_pretty()`

Forms a human-readable text representation from a number of bytes in suitable units (KiB, MiB, GiB, etc.) in 1024 increments.

See example

SELECT
    formatReadableSize(1000)         AS result_1,
    formatReadableSize(1000000)      AS result_2,
    formatReadableSize(111111111111) AS result_3;

+------------+-----------+-----------+
| result_1   | result_2  | result_3  |
+------------+-----------+-----------+
| 1000 bytes | 976.5 KiB | 103.4 GiB |
+------------+-----------+-----------+

`from_base64()`

Description	Converts a Base64 encoded string into a character string (`BLOB`).
Usage	`from_base64(string)`

Inverse function: to_base64()

See example

SELECT
    from_base64('QQ==')     AS result_1,
    from_base64('Qg==')     AS result_2,
    from_base64('VGVuZ3Jp') AS result_3;

+----------+----------+-----------+
| result_1 | result_2 | result_3  |
+----------+----------+-----------+
| b'A'     | b'B'     | b'Tengri' |
+----------+----------+-----------+

`greatest()`

Description	Returns the largest of the specified arguments.
Usage	`greatest(argument[, argument, ...])`

When used with numbers, returns the largest number.
When used with strings — returns the last of the strings in alphabetical order. In this case, upper case precedes lower case.
When used with types of date and time — returns the most recent object.

See examples

SELECT
    greatest(1, 2, 3, 4, 4) AS result_1,
    greatest(1, -1)         AS result_2,
    greatest(0)             AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 1        | 0        |
+----------+----------+----------+

SELECT
    greatest('a', 'z', 'A', 'Z') AS result_1,
    greatest('Tengri', 'TNGRi')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| z        | Tengri   |
+----------+----------+

SELECT
    greatest(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    greatest(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    greatest(DATE '2024-02-29', now())                           AS result_3;

+---------------------+------------+----------------------------------+
| result_1            | result_2   | result_3                         |
+---------------------+------------+----------------------------------+
| 2024-02-29T00:00:01 | 2029-02-24 | 2026-05-06T13:05:46.173330+03:00 |
+---------------------+------------+----------------------------------+

`hex()`

Description	Converts a string to hexadecimal representation.
Usage	`hex(string)`
Aliases	`to_hex()`

Inverse function: unhex()

See example

SELECT
    hex('A') AS result_1,
    hex('B') AS result_2,
    hex('C') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 41       | 42       | 43       |
+----------+----------+----------+

`ilike_escape()`

Description	Checks if a string matches a case-insensitive pattern.
Usage	`ilike_escape(string, pattern, escape_character)`

Checks if a string matches a case-insensitive pattern, with the option to specify an escape character. The escape character is ignored during matching and cancels the following special character in the pattern.

Reverse function: not_ilike_escape()

See example

SELECT
    ilike_escape('ten%gri', 'TEN$%GRI', '$') AS result_1,
    ilike_escape('tengri', 'TEN$%GRI', '$')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| true     | false    |
+----------+----------+

`instr()`

Description	Returns the position of the searched string in the string.
Usage	`instr(string, search_string)`
Aliases	`strpos()`

Returns the position of the first occurrence of the searched string in the string, starting with 1. Returns 0 if no occurrences are found.

See example

SELECT
    instr('TENGRI', 'E')        AS result_1,
    instr('TENGRI TENGRI', 'E') AS result_2,
    instr('TENGRI', 'A')        AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2        | 2        | 0        |
+----------+----------+----------+

`least()`

Description	Returns the smallest of the specified arguments.
Usage	`least(argument[, argument, ...])`

When used with numbers, returns the smallest number.
When used with strings — returns the first of the strings in alphabetical order. In this case, upper case precedes lower case.
When used with types date and time — returns the earliest object.

See examples

SELECT
    least(1, 1, 2, 3, 4) AS result_1,
    least(1, -1)         AS result_2,
    least(0)             AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | -1       | 0        |
+----------+----------+----------+

SELECT
    least(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    least(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    least(DATE '2024-02-29', now())                           AS result_3;

+---------------------+------------+---------------------------+
| result_1            | result_2   | result_3                  |
+---------------------+------------+---------------------------+
| 2024-02-29T00:00:00 | 2024-02-29 | 2024-02-29T00:00:00+03:00 |
+---------------------+------------+---------------------------+

SELECT
    least('a', 'z', 'A', 'Z') AS result_1,
    least('Tengri', 'TNGRi')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| A        | TNGRi    |
+----------+----------+

`left()`

Description	Extracts a substring of the given length from the string, starting from the left edge.
Usage	`left(string, length)`

See example

SELECT
    left('I love Tengri', 6)   AS result_1,
    left('I love Tengri', 100) AS result_2;

+----------+---------------+
| result_1 | result_2      |
+----------+---------------+
| I love   | I love Tengri |
+----------+---------------+

`left_grapheme()`

Description	Extracts a substring of the given length from a string, starting from the left edge and counting by grapheme clusters.
Usage	`left_grapheme(string, length)`

Extracts a substring of the given length from a string, starting from the left edge and counting by grapheme clusters — visible single characters.

See example

SELECT
    left('Те́нгри', 2),
    left_grapheme('Те́нгри', 2);

+------+---------------+
| left | left_grapheme |
+------+---------------+
| Те   | Те́            |
+------+---------------+

`length()`

Description	Returns the number of characters in a string.
Usage	`length(string)`
Aliases	`char_length()`, `character_length()`, `len()`

See example

SELECT
    length('I love Tengri 💙') AS length;

+--------+
| length |
+--------+
| 15     |
+--------+

`length_grapheme()`

Description	Returns the number of grapheme clusters in the string.
Usage	`length_grapheme(string)`

Returns the number of grapheme clusters in the string — the number of visible single characters.

See example

SELECT
    length('Те́нгри'),
    length_grapheme('Те́нгри');

+--------+-----------------+
| length | length_grapheme |
+--------+-----------------+
| 7      | 6               |
+--------+-----------------+

`like_escape()`

Description	Checks if a string matches a case-sensitive pattern.
Usage	`like_escape(string, pattern, escape_character)`

Checks if a string matches a case-sensitive pattern, with the option to specify an escape character. The escape character is ignored during matching and overrides the following wildcard in the pattern.

Inverse function: not_like_escape()

See example

SELECT
    like_escape('TEN%GRI', 'TEN$%GRI', '$') AS result_1,
    like_escape('TENGRI', 'TEN$%GRI', '$')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| true     | false    |
+----------+----------+

`lower()`

Description	Converts a string to lower case.
Usage	`lower(string)`
Aliases	`lcase()`

See example

SELECT
    lower('TNGRi') AS lower;

+-------+
| lower |
+-------+
| tngri |
+-------+

`lpad()`

Description	Adds characters up to the given length to the string on the left.
Usage	`lpad(string, count, character)`

Adds characters to the string on the left so that the total number of characters is equal to count. If the string contains more characters than count, it is truncated on the right.

See example

SELECT
    lpad('Tengri', 10, '.') AS result_1,
    lpad('Tengri', 7, '.')  AS result_2,
    lpad('Tengri', 3, '.')  AS result_3;

+------------+----------+----------+
| result_1   | result_2 | result_3 |
+------------+----------+----------+
| ....Tengri | .Tengri  | Ten      |
+------------+----------+----------+

`ltrim()`

Description	Removes all occurrences of any of the specified characters at the beginning of a string.
Usage	`ltrim(string[, characters])`

If no characters to be deleted are specified, the default character is a space.

See examples

SELECT
    '"' || ltrim(' Tengri ') || '"' AS ltrim;

+-----------+
|   ltrim   |
+-----------+
| "Tengri " |
+-----------+

SELECT
    ltrim('{{([Tengri])}}', '{([])}') AS ltrim_brackets;

+----------------+
| ltrim_brackets |
+----------------+
| Tengri])}}     |
+----------------+

`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 |
+----------------------------------+

`md5_number_lower()`

Description	Returns the low-order (rightmost) 64-bit MD5 hash segment of a string as a number.
Usage	`md5_number_lower(string)`

See example

SELECT
    md5_number_lower('Tengri');

+----------------------+
| md5_number_lower     |
+----------------------+
| 17247597492360163000 |
+----------------------+

`md5_number_upper()`

Description	Returns the upper (left) 64-bit MD5 hash segment of a string as a number.
Usage	`md5_number_upper(string)`

See example

SELECT
    md5_number_upper('Tengri');

+--------------------+
| md5_number_upper   |
+--------------------+
| 838004958291979100 |
+--------------------+

`nfc_normalize()`

Description	Converts a string to a normalised Unicode NFC string.
Usage	`nfc_normalise(string)`

Useful for cases where raw text data contains both Unicode NFC normalised strings and non-normalized strings.

See example

SELECT
    'Mu' || chr(776) || 'nchen'                        AS result_1,
    nfc_normalize('Mu' || chr(776) || 'nchen')         AS result_2,
    length('Mu' || chr(776) || 'nchen')                AS result_3,
    length(nfc_normalize('Mu' || chr(776) || 'nchen')) AS result_4;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| München  | München  | 8        | 7        |
+----------+----------+----------+----------+

`not_ilike_escape()`

Description	Checks if a string does not match a pattern without regard to character case.
Usage	`not_ilike_escape(string, pattern, escape_character)`

Checks that the string does not match a case-insensitive pattern, with the option to specify an escape character. The escape character is ignored during matching and cancels the following escape character in the pattern.

Reverse function: ilike_escape()

See example

SELECT
    not_ilike_escape('ten%gri', 'TEN$%GRI', '$') AS result_1,
    not_ilike_escape('tengri', 'TEN$%GRI', '$')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| false    | true     |
+----------+----------+

`not_like_escape()`

Description	Checks if a string does not match a case-sensitive pattern.
Usage	`not_like_escape(string, pattern, escape_character)`

Checks that a string does not match a case-sensitive pattern, with the option to specify an escape character. The escape character is ignored during matching and overrides the following wildcard in the pattern.

Inverse function: like_escape()

See example

SELECT
    not_like_escape('TEN%GRI', 'TEN$%GRI', '$') AS result_1,
    not_like_escape('TENGRI', 'TEN$%GRI', '$')  AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| false    | true     |
+----------+----------+

`parse_dirname()`

Description	Returns the root directory name from the specified path.
Usage	`parse_dirname(string[, separator])`

Separator (separator) options:

system — system
both_slash — any slash (default)
forward_slash — forward slash
backslash — backslash

See example

SELECT
    parse_dirname('path/to/file.csv')                        AS result_1,
    parse_dirname('path/to/file.csv', 'forward_slash')       AS result_2,
    parse_dirname('path\to\file.csv')                        AS result_3,
    parse_dirname('path/to/folder')                          AS result_4;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| path     | path     | path     | path     |
+----------+----------+----------+----------+

`parse_dirpath()`

Description	Returns the full path to the last separator.
Usage	`parse_dirpath(string[, separator])`

Separator (separator) options:

system — system
both_slash — any slash (default)
forward_slash — forward slash
backslash — backslash

See example

SELECT
    parse_dirpath('path/to/file.csv')                        AS result_1,
    parse_dirpath('path/to/file.csv', 'forward_slash')       AS result_2,
    parse_dirpath('path\to\file.csv')                        AS result_3,
    parse_dirpath('path/to/folder')                          AS result_4;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| path/to  | path/to  | path\to  | path/to  |
+----------+----------+----------+----------+

`parse_filename()`

Description	Returns the name of the file at the specified path.
Usage	`parse_filename(string[, trim_extension][, separator])`

Returns the name of the file at the specified path — the last element of the path.

If trim_extension is true, the file extension is removed (default — false).

Separator options:

system — system
both_slash — any slash (default)
forward_slash — forward slash
backslash — backslash

See example

SELECT
    parse_filename('path/to/file.csv')                        AS result_1,
    parse_filename('path/to/file.csv', true, 'forward_slash') AS result_2,
    parse_filename('path\to\file.csv')                        AS result_3,
    parse_filename('path/to/folder')                          AS result_4;

+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| file.csv | file     | file.csv | folder   |
+----------+----------+----------+----------+

`parse_path()`

Description	Returns a list of elements of the specified path.
Usage	`parse_path(string[, separator])`

Returns a list of elements (directories and filename) in the specified path.

Separator options:

system — system
both_slash — any slash (default)
forward_slash — forward slash
backslash — backslash

See example

SELECT
    parse_path('path/to/file.csv')                  AS result_1,
    parse_path('path/to/file.csv', 'forward_slash') AS result_2,
    parse_path('path\to\file.csv')                  AS result_3,
    parse_path('path/to/folder')                    AS result_4;

+--------------------+--------------------+--------------------+------------------+
| result_1           | result_2           | result_3           | result_4         |
+--------------------+--------------------+--------------------+------------------+
| {path,to,file.csv} | {path,to,file.csv} | {path,to,file.csv} | {path,to,folder} |
+--------------------+--------------------+--------------------+------------------+

`prefix()`

Description	Checks if the string being searched is a prefix of another string.
Usage	`prefix(string, search_string)`

See example

SELECT
    prefix('Tengri', 'Te')     AS result_1,
    prefix('Tengri', 'TE')     AS result_2,
    prefix('Tengri', 'Tengri') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | false    | true     |
+----------+----------+----------+

`printf()`

Description	Formats a string using the printf syntax.
Usage	`printf(string, ...)`

Formats a string using the syntax printf.

See example

SELECT
    printf('I love %s', 'Tengri')    AS result_1,
    printf('Tengri is number %d', 1) AS result_2,
    printf('Today is %s', today())   AS result_3;

+---------------+--------------------+---------------------+
| result_1      | result_2           | result_3            |
+---------------+--------------------+---------------------+
| I love Tengri | Tengri is number 1 | Today is 2026-06-03 |
+---------------+--------------------+---------------------+

`position()`

Description	Returns the position of the searched string in the string.
Usage	`position('search_string' IN 'string')`

Returns the position of the first occurrence of the searched string in the string, starting at 1. Returns 0 if no occurrences are found.

See example

SELECT
    position('Tengri' IN 'I love Tengri') AS result_1,
    position('like' IN 'I love Tengri')   AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| 8        | 0        |
+----------+----------+

`read_text()`

Description	Returns the content from the source as a table of read text.
Usage	`read_text(path)`

Returns the content from the source (file, list of files) as a table with the read text (content column) and technical information.

See examples

SELECT * FROM read_text('s3://prostore/Stage/abogdanov/f1b3a43ec11a_test.txt');

+-----------------------------------------------------+---------------+------+---------------------------+
| filename                                            | content       | size | last_modified             |
+-----------------------------------------------------+---------------+------+---------------------------+
| s3://prostore/Stage/abogdanov/f1b3a43ec11a_test.txt | I love Tengri | 14   | 2026-06-05T13:18:03+03:00 |
+-----------------------------------------------------+---------------+------+---------------------------+

SELECT * FROM read_text(['s3://prostore/Stage/abogdanov/f1b3a43ec11a_test.txt', 's3://prostore/Stage/abogdanov/b51ed4ce91d0_test2.txt']);

+------------------------------------------------------+---------------+------+---------------------------+
| filename                                             | content       | size | last_modified             |
+------------------------------------------------------+---------------+------+---------------------------+
| s3://prostore/Stage/abogdanov/f1b3a43ec11a_test.txt  | I love Tengri | 14   | 2026-06-05T13:18:03+03:00 |
+------------------------------------------------------+---------------+------+---------------------------+
| s3://prostore/Stage/abogdanov/b51ed4ce91d0_test2.txt | I like Tengri | 14   | 2026-06-05T13:20:47+03:00 |
+------------------------------------------------------+---------------+------+---------------------------+

`repeat()`

Description	Forms a string from the given string repeated the given number of times.
Usage	`repeat(string, num)`

See example

SELECT
    repeat('Tengri ', 3);

+----------------------+
| repeat               |
+----------------------+
| Tengri Tengri Tengri |
+----------------------+

`replace()`

Description	Replaces all substrings with the specified string.
Usage	`replace(string, source, target)`

Replaces all source substrings in a string string with target.

See example

SELECT
    replace('Tengri', 'engr', 'NGR')       AS result_1,
    replace('my name is Tengri', 'n', 'N') AS result_2;

+----------+-------------------+
| result_1 | result_2          |
+----------+-------------------+
| TNGRi    | my Name is TeNgri |
+----------+-------------------+

`reverse()`

Description	Reverses the order of characters in a string.
Usage	`reverse(string)`

See example

SELECT
    reverse('Tengri');

+---------+
| reverse |
+---------+
| irgneT  |
+---------+

`right()`

Description	Extracts a substring of the given length from a string, starting at the right edge.
Usage	`right(string, length)`

See example

SELECT
    right('I love Tengri', 6)   AS result_1,
    right('I love Tengri', 100) AS result_2;

+----------+---------------+
| result_1 | result_2      |
+----------+---------------+
| Tengri   | I love Tengri |
+----------+---------------+

`right_grapheme()`

Description	Extracts a substring of the given length from a string, starting at the right edge and counting by grapheme clusters.
Usage	`right_grapheme(string, length)`

Extracts a substring of the given length from a string, starting at the right edge and counting by grapheme clusters — visible single characters.

See example

SELECT
    right('Те́нгри', 5),
    right_grapheme('Те́нгри', 5);

+-------+----------------+
| right | right_grapheme |
+-------+----------------+
| ́нгри  | е́нгри          |
+-------+----------------+

`rpad()`

Description	Adds characters up to the given length to the string on the right.
Usage	`rpad(string, count, character)`

Adds characters to the string on the right side so that the total number of characters is equal to count. If the string contains more characters than count, it is truncated to the right.

See example

SELECT
    rpad('Tengri', 10, '.') AS result_1,
    rpad('Tengri', 7, '.')  AS result_2,
    rpad('Tengri', 3, '.')  AS result_3;

+------------+----------+----------+
| result_1   | result_2 | result_3 |
+------------+----------+----------+
| Tengri.... | Tengri.  | Ten      |
+------------+----------+----------+

`rtrim()`

Description	Removes all occurrences of any of the specified characters at the end of a string.
Usage	`rtrim(string[, characters])`

If no characters to be deleted are specified, the default character is a space.

See examples

SELECT
    '"' || rtrim(' Tengri ') || '"' AS ltrim;

+-----------+
|   ltrim   |
+-----------+
| " Tengri" |
+-----------+

SELECT
    rtrim('{{([Tengri])}}', '{([])}') AS rtrim_brackets;

+----------------+
| rtrim_brackets |
+----------------+
| {{([Tengri     |
+----------------+

`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 |
+------------------------------------------------------------------+

`split()`

Description	Splits a string into two parts by the given separator.
Usage	`split(string, separator)`
Aliases	`str_split()`, `string_split()`, `string_to_array()`

See example

SELECT
    split('I love Tengri', ' ') AS words;

+-----------------+
|      words      |
+-----------------+
| {I,love,Tengri} |
+-----------------+

`split_part()`

Description	splits a string into two parts at the given separator and returns the part at the given index.
Usage	`split_part(string, separator, index)`

Splits a string into two parts by the given separator and returns the part at the given index.

The numbering of the parts starts with 1. In case of specifying a non-existing index, an empty string is returned.

See example

SELECT
    split_part('I love Tengri', ' ', 3)  AS result_1,
    split_part('I love Tengri', ' ', 10) AS result_2,
    split_part('I love Tengri', '|', 1)  AS result_3;

+----------+---------------+----------+
| result_1 | result_2      | result_3 |
+----------+---------------+----------+
| Tengri   | I love Tengri |          |
+----------+---------------+----------+

`starts_with()`

Description	Returns `true` if the string starts with the searched string.
Usage	`starts_with(string, search_string)`

See example

SELECT
    starts_with('I love Tengri', 'I')      AS result_1,
    starts_with('I love Tengri', 'Tengri') AS result_2;

+----------+----------+
| result_1 | result_2 |
+----------+----------+
| true     | false    |
+----------+----------+

`strip_accents()`

Description	Removes diacritical marks from characters in a string.
Usage	`strip_accents(string)`

See example

SELECT
    strip_accents('Те́нгри')  AS result_1,
    strip_accents('Ёлка')    AS result_2,
    strip_accents('München') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| Тенгри   | Елка     | Munchen  |
+----------+----------+----------+

`strlen()`

Description	Returns the number of bytes in the string.
Usage	`strlen(string)`

See example

SELECT
    strlen('Tengri 💙') AS strlen;

+--------+
| strlen |
+--------+
| 11     |
+--------+

`substring()`

Description	Extracts a substring from a string.
Usage	`substring(string, start[, length])`
Aliases	`substr()`

Extracts a substring from the specified string string, starting at the start character and ending at the end of the string. If the optional length argument is given, a substring of length characters is extracted. Note that the numbering of characters in the source string starts with 1.

See example

SELECT
    substring('I love Tengri', 1)    AS result_1,
    substring('I love Tengri', 3)    AS result_2,
    substring('I love Tengri', 3, 4) AS result_3;

+---------------+-------------+----------+
| result_1      | result_2    | result_3 |
+---------------+-------------+----------+
| I love Tengri | love Tengri | love     |
+---------------+-------------+----------+

`substring_grapheme()`

Description	Extracts a substring from a string by grapheme clusters.
Usage	`substring_grapheme(string, start[, length])`

Extracts a substring from the specified string string, starting at the start character and ending at the end of the string by grapheme clusters — visible single characters. If the optional length argument is given, a substring of length characters is extracted. Note that the numbering of characters in the source string starts with 1.

See example

SELECT
    substring('Те́нгри', 3)             AS result_1,
    substring_grapheme('Те́нгри', 3)    AS result_2,
    substring_grapheme('Те́нгри', 3, 2) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| ́нгри     | нгри     | нг       |
+----------+----------+----------+

`to_base()`

Description	Translates an integer into the given number system and returns as a string.
Usage	`to_base(number, radix[, min_length])`

If the min_length parameter is given, the string is appended with zeros on the left up to the given length.

See example

SELECT
    to_base(12345, 36)      AS result_1,
    to_base(9999999, 36)    AS result_2,
    to_base(9999999, 36, 8) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 9IX      | 5YC1R    | 0005YC1R |
+----------+----------+----------+

`to_base64()`

Description	Encodes binary data into a Base64 text string.
Usage	`to_base64(argument)`
Aliases	`base64()`

Inverse function: from_base64()

See example

SELECT
    to_base64('A'::BLOB) AS result_1,
    to_base64('B'::BLOB) AS result_2,
    to_base64('C'::BLOB) AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| QQ==     | Qg==     | Qw==     |
+----------+----------+----------+

`translate()`

Description	Performs character-by-character replacement in a string according to the given character sets.
Usage	`translate(string, source, target))`

The character set to be replaced is specified in the source parameter. The set of characters to replace is specified in the target parameter in the same order. If the number of characters in target is larger, the extra characters (on the right) are ignored. If the number of characters in source is greater, the extra characters (on the right) are replaced by an empty string — deleted.

See example

SELECT
    translate('Tengri', 'gn', 'GN')             AS result_1,
    translate('QWERTY', 'QWERTY', 'ЙЦУКЕН')     AS result_2,
    translate('+7 (999) 123-45-67', ' ()-', '') AS result_3;

+----------+----------+--------------+
| result_1 | result_2 | result_3     |
+----------+----------+--------------+
| TeNGri   | ЙЦУКЕН   | +79991234567 |
+----------+----------+--------------+

`trim()`

Description	Removes all occurrences of any of the specified characters on both sides of the string.
Usage	`trim(string[, characters])`

If no characters to be deleted are specified, the default character is a space.

See examples

SELECT
    '"' || trim('  Tengri ') || '"' AS trim;

+----------+
|   trim   |
+----------+
| "Tengri" |
+----------+

SELECT
    trim('[Tengri]', '{([])}') AS trim_brackets;

+---------------+
| trim_brackets |
+---------------+
| Tengri        |
+---------------+

`unbin()`

Description	Converts a value from a binary representation to a binary object (`BLOB`).
Usage	`unbin(argument)`
Aliases	`from_binary()`

Inverse function: bin()

See example

SELECT
    unbin('01000001') AS result_1,
    unbin('01000010') AS result_2,
    unbin('01000011') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| b'A'     | b'B'     | b'C'     |
+----------+----------+----------+

`unhex()`

Description	Converts a value from a hexadecimal representation to a binary object (`BLOB`).
Usage	`unhex(argument)`
Aliases	`from_hex()`

Inverse function: hex()

See example

SELECT
    unhex('41') AS result_1,
    unhex('42') AS result_2,
    unhex('43') AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| b'A'     | b'B'     | b'C'     |
+----------+----------+----------+

`unicode()`

Description	Returns the code Unicode of the first character of a string as an integer.
Usage	`unicode(string)`
Aliases	`ascii()`, `ord()`

See example

SELECT
    unicode('Tengri') AS result_1,
    unicode('Тенгри') AS result_2,
    unicode(' ')      AS result_3;

+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 84       | 1058     | 32       |
+----------+----------+----------+

`upper()`

Description	Converts a string to uppercase.
Usage	`upper(string)`
Aliases	`ucase()`

See example

SELECT
    upper('Tengri') AS upper;

+--------+
|  upper |
+--------+
| TENGRI |
+--------+

`url_decode()`

Description	Decodes a URL from the Percent-Encoding.
Usage	`url_decode(string)`

Decodes a URL from the Percent-Encoding.

See example

SELECT
    url_decode('%D0%A2%D0%B5%D0%BD%D0%B3%D1%80%D0%B8') AS result_1,
    url_decode('Tengri')                               AS result_2,
    url_decode('https://tengri.postgrespro.ru/documentation/ru/stable/ideology#_%D0%BE%D0%B1%D1%89%D0%B0%D1%8F_%D1%81%D1%85%D0%B5%D0%BC%D0%B0_%D0%B8%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D1%8F_tengri') AS result_3;

+----------+----------+--------------------------------------------------------------------------------------------------+
| result_1 | result_2 | result_3                                                                                         |
+----------+----------+--------------------------------------------------------------------------------------------------+
| Тенгри   | Tengri   | https://tengri.postgrespro.ru/documentation/ru/stable/ideology#_общая_схема_использования_tengri |
+----------+----------+--------------------------------------------------------------------------------------------------+

`url_encode()`

Description	Encodes the URL into a Percent-Encoding.
Usage	`url_encode(string)`

Encodes the URL into a Percent-Encoding.

See example

SELECT
    url_encode('Тенгри') AS result_1,
    url_encode('Tengri') AS result_2,
    url_encode('https://tengri.postgrespro.ru/documentation/ru/stable/ideology#_общая_схема_использования_tengri') AS result_3;

+--------------------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result_1                             | result_2 | result_3                                                                                                                                                                                                                            |
+--------------------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| %D0%A2%D0%B5%D0%BD%D0%B3%D1%80%D0%B8 | Tengri   | https%3A%2F%2Ftengri.postgrespro.ru%2Fdocumentation%2Fru%2Fstable%2Fideology%23_%D0%BE%D0%B1%D1%89%D0%B0%D1%8F_%D1%81%D1%85%D0%B5%D0%BC%D0%B0_%D0%B8%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D1%8F_tengri |
+--------------------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Operator `||`

Description	Concatenates multiple strings, arrays or binary values.
Usage	`argument1 \|\| argument2 \|\| argument2 \|\| ...`.

Empty values (NULL) are ignored.

Text functions

array_extract()

array_slice()

bar()

bin()

bit_length()

chr()

concat()

concat_ws()

contains()

ends_with()

format()

formatReadableDecimalSize()

formatReadableSize()

from_base64()

greatest()

hex()

ilike_escape()

instr()

least()

left()

left_grapheme()

length()

length_grapheme()

like_escape()

lower()

lpad()

ltrim()

md5()

md5_number_lower()

md5_number_upper()

nfc_normalize()

not_ilike_escape()

not_like_escape()

parse_dirname()

parse_dirpath()

parse_filename()

parse_path()

prefix()

printf()

position()

read_text()

repeat()

replace()

reverse()

right()

right_grapheme()

rpad()

rtrim()

sha1()

sha256()

split()

split_part()

starts_with()

strip_accents()

strlen()

substring()

substring_grapheme()

to_base()

to_base64()

translate()

trim()

unbin()

unhex()

unicode()

upper()

url_decode()

url_encode()

Operator ||

`array_extract()`

`array_slice()`

`bar()`

`bin()`

`bit_length()`

`chr()`

`concat()`

`concat_ws()`

`contains()`

`ends_with()`

`format()`

`formatReadableDecimalSize()`

`formatReadableSize()`

`from_base64()`

`greatest()`

`hex()`

`ilike_escape()`

`instr()`

`least()`

`left()`

`left_grapheme()`

`length()`

`length_grapheme()`

`like_escape()`

`lower()`

`lpad()`

`ltrim()`

`md5()`

`md5_number_lower()`

`md5_number_upper()`

`nfc_normalize()`

`not_ilike_escape()`

`not_like_escape()`

`parse_dirname()`

`parse_dirpath()`

`parse_filename()`

`parse_path()`

`prefix()`

`printf()`

`position()`

`read_text()`

`repeat()`

`replace()`

`reverse()`

`right()`

`right_grapheme()`

`rpad()`

`rtrim()`

`sha1()`

`sha256()`

`split()`

`split_part()`

`starts_with()`

`strip_accents()`

`strlen()`

`substring()`

`substring_grapheme()`

`to_base()`

`to_base64()`

`translate()`

`trim()`

`unbin()`

`unhex()`

`unicode()`

`upper()`

`url_decode()`

`url_encode()`

Operator `||`