Edit this page

Utility Functions

Scalar Utility Functions

The functions below are difficult to categorize into specific function types and are broadly useful. The table-function examples share the tables created in Setup.

Name	Description
`alias(column)`	Return the name of the column.
`can_cast_implicitly(source_value, target_value)`	Whether or not we can implicitly cast from the types of the source value to the target value.
`checkpoint(database)`	Synchronize WAL with file for (optional) database without interrupting transactions.
`coalesce(expr, ...)`	Return the first expression that evaluates to a non-`NULL` value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others.
`constant_or_null(arg1, arg2)`	If `arg2` is `NULL`, return `NULL`. Otherwise, return `arg1`.
`count_if(x)`	Aggregate function; counts the rows for which the `BOOLEAN` argument `x` is `true`.
`create_sort_key(parameters...)`	Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers.
`current_catalog()`	Return the name of the currently active catalog. Default is postgres.
`current_database()`	Return the name of the currently active database.
`current_query()`	Return the current query as a string.
`current_schema()`	Return the name of the currently active schema. Default is public.
`current_schemas(boolean)`	Return list of schemas. Pass a parameter of `true` to include implicit schemas.
`current_setting('setting_name')`	Return the current value of the configuration setting.
`currval('sequence_name')`	Return the current value of the sequence. Note that `nextval` must be called at least once prior to calling `currval`.
`error(message)`	Throws the given error `message`.
`equi_width_bins(min, max, bincount, nice := false)`	Returns the upper boundaries of a partition of the interval `[min, max]` into `bin_count` equal-sized subintervals (for use with, e.g., `histogram`). If `nice = true`, then `min`, `max` and `bincount` may be adjusted to produce more aesthetically pleasing results.
`force_checkpoint(database)`	Synchronize WAL with file for (optional) database interrupting transactions.
`gen_random_uuid()`	Return a random UUID similar to this: `eeccb8c5-9943-b2bb-bb5e-222f4e14b687`.
`getenv(var)`	Returns the value of the environment variable `var`. Only available in the command line client (`serened shell`).
`hash(value)`	Returns a `UBIGINT` with a hash of `value`. The used hash function may change across SereneDB versions.
`icu_sort_key(string, collator)`	Surrogate sort key used to sort special characters according to the specific locale. Collator parameter is optional.
`if(a, b, c)`	Ternary conditional operator.
`ifnull(expr, other)`	A two-argument version of coalesce.
`is_histogram_other_bin(arg)`	Returns `true` when `arg` is the "catch-all element" of its datatype for the purpose of the `histogram_exact` function, which is equal to the "right-most boundary" of its datatype for the purpose of the `histogram` function.
`md5(string)`	Returns the MD5 hash of the `string` as a `VARCHAR`.
`md5_number(string)`	Returns the MD5 hash of the `string` as a `UHUGEINT`.
`md5_number_lower(string)`	Returns the lower 64-bit segment of the MD5 hash of the `string` as a `UBIGINT`.
`md5_number_upper(string)`	Returns the upper 64-bit segment of the MD5 hash of the `string` as a `UBIGINT`.
`nextval('sequence_name')`	Return the following value of the sequence.
`nullif(a, b)`	Return `NULL` if `a = b`, else return `a`. Equivalent to `CASE WHEN a = b THEN NULL ELSE a END`.
`parse_formatted_bytes(string)`	Parse a human-readable byte size string (e.g., `'16 KiB'`) into a `UBIGINT` number of bytes. Throws an error on invalid input.
`pg_typeof(expression)`	Returns the lower case name of the data type of the result of the expression. For PostgreSQL compatibility.
`query(``query_string``)`	Table function that parses and executes the query defined in `query_string`. Only constant strings are allowed. Warning: this function allows invoking arbitrary queries, potentially altering the database state.
`query_table(``tbl_name``)`	Table function that returns the table given in `tbl_name`.
`query_table(``tbl_names``, [``by_name``])`	Table function that returns the union of tables given in `tbl_names`. If the optional `by_name` parameter is set to `true`, it uses `UNION ALL BY NAME` semantics.
`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.
`read_text(source)`	Returns the content from `source` (a filename, a list of filenames, or a glob pattern) as a `VARCHAR`. The file content is first validated to be valid UTF-8. If `read_text` attempts to read a file with invalid UTF-8 an error is thrown suggesting to use `read_blob` instead. See the `read_text` guide for more details.
`sha1(string)`	Returns a `VARCHAR` with the SHA-1 hash of the `string`.
`sha256(string)`	Returns a `VARCHAR` with the SHA-256 hash of the `string`.
`sleep_ms(milliseconds)`	Pause execution for the specified number of milliseconds. Returns `NULL`.
`stats(expression)`	Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
`txid_current()`	Returns the current transaction's identifier, a `BIGINT` value. It will assign a new one if the current transaction does not have one already.
`typeof(expression)`	Returns the name of the data type of the result of the expression.
`uuid()`	Return a random UUID (UUIDv4) similar to this: `eeccb8c5-9943-b2bb-bb5e-222f4e14b687`.
`uuidv4()`	Return a random UUID (UUIDv4) similar to this: `eeccb8c5-9943-b2bb-bb5e-222f4e14b687`.
`uuidv7()`	Return a random UUIDv7 similar to this: `81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5`.
`uuid_extract_timestamp(uuidv7)`	Extracts `TIMESTAMP WITH TIME ZONE` from a UUIDv7 value.
`uuid_extract_version(uuid)`	Extracts UUID version (`4` or `7`).
`version()`	Return the currently active version of SereneDB in this format.

Setup

Query

CREATE TABLE t1 (x INTEGER);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (x INTEGER);
INSERT INTO t2 VALUES (3);

`alias(column)`

Return the name of the column.

Query

SELECT alias(column1) FROM (SELECT 1 AS column1) t;

Result

 alias--------- column1

`can_cast_implicitly(source_value, target_value)`

Whether or not we can implicitly cast from the types of the source value to the target value.

Query

SELECT can_cast_implicitly(1::BIGINT, 1::SMALLINT) AS can_cast_implicitly;

Result

 can_cast_implicitly--------------------- f

`checkpoint(database)`

Synchronize the WAL with the file for the (optional) database without interrupting transactions.

Query

SELECT checkpoint();

`coalesce(expr, ...)`

Return the first expression that evaluates to a non-NULL value. Accepts 1 or more parameters. Each expression can be a column, literal value, function result, or many others.

Query

SELECT coalesce(NULL, NULL, 'default_string') AS coalesce;

Result

 coalesce---------------- default_string

`constant_or_null(arg1, arg2)`

If arg2 is NULL, return NULL. Otherwise, return arg1.

Query

SELECT constant_or_null(42, NULL) AS constant_or_null;

Result

 constant_or_null------------------ NULL

`count_if(x)`

Aggregate function; counts the rows for which the BOOLEAN argument x is true.

Query

SELECT count_if(x > 1) AS count_if FROM (VALUES (1), (2), (3)) t(x);

Result

 count_if----------        2

`create_sort_key(parameters...)`

Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers.

Query

SELECT create_sort_key('abc', 'ASC NULLS FIRST') AS create_sort_key;

Result

 create_sort_key----------------- \\x0262636400

`current_catalog()`

Return the name of the currently active catalog. Default is postgres.

Query

SELECT current_catalog();

Result

 current_catalog----------------- postgres

`current_database()`

Return the name of the currently active database.

Query

SELECT current_database();

Result

 current_database------------------ postgres

`current_query()`

Return the current query as a string.

Query

SELECT current_query() AS current_query;

Result

 current_query------------------------------------------ SELECT current_query() AS current_query;

`current_schema()`

Return the name of the currently active schema. Default is public.

Query

SELECT current_schema() AS current_schema;

Result

 current_schema---------------- public

`current_schemas(boolean)`

Return list of schemas. Pass a parameter of true to include implicit schemas.

Query

SELECT current_schemas(true) AS current_schemas;

Result

 current_schemas--------------------- {pg_catalog,public}

`current_setting('setting_name')`

Return the current value of the configuration setting.

Query

SELECT current_setting('access_mode') AS current_setting;

Result

 current_setting----------------- automatic

`currval('sequence_name')`

Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.

Query

SELECT currval('seq_currval') AS currval;

Result

 currval---------       1

`error(message)`

Throws the given error message.

Query

SELECT error('access_mode');

Result

db error: ERROR: access_mode

`equi_width_bins(min, max, bincount, nice := false)`

Returns the upper boundaries of a partition of the interval [min, max] into bin_count equal-sized subintervals (for use with, e.g., histogram). If nice = true, then min, max and bincount may be adjusted to produce more aesthetically pleasing results.

Query

SELECT equi_width_bins(0.1, 2.7, 4, true) AS equi_width_bins;

Result

 equi_width_bins--------------------- {0.5,1,1.5,2,2.5,3}

`force_checkpoint(database)`

Synchronize the WAL with the file for the (optional) database, interrupting transactions.

Query

SELECT force_checkpoint();

`gen_random_uuid()`

Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.

Query

SELECT gen_random_uuid();

Result

 gen_random_uuid-------------------------------------- 6b8b6e47-bbf9-4808-a8ef-725a1469564d

`getenv(var)`

Returns the value of the environment variable var. Only available in the command line client (serened shell).

Query

SELECT getenv('HOME');

Result

 getenv-------------------- /path/to/user/home

`hash(value)`

Returns a UBIGINT with the hash of the value. The used hash function may change across SereneDB versions.

Query

SELECT hash('🌊') AS hash;

Result

 hash---------------------- 12255790945742965276

`icu_sort_key(string, collator)`

Surrogate sort key used to sort special characters according to the specific locale. The collator parameter is optional.

Query

SELECT icu_sort_key('a', 'DE') < icu_sort_key('b', 'DE') AS icu_sort_key;

Result

 icu_sort_key-------------- t

`if(a, b, c)`

Ternary conditional operator; returns b if a, else returns c. Equivalent to CASE WHEN a THEN b ELSE c END.

Query

SELECT if(2 > 1, 3, 4) AS if;

Result

 if----  3

`ifnull(expr, other)`

A two-argument version of coalesce.

Query

SELECT ifnull(NULL, 'default_string') AS ifnull;

Result

 ifnull---------------- default_string

`is_histogram_other_bin(arg)`

Returns true when arg is the "catch-all element" of its datatype for the purpose of the histogram_exact function, which is equal to the "right-most boundary" of its datatype for the purpose of the histogram function.

Query

SELECT is_histogram_other_bin('') AS is_histogram_other_bin;

Result

 is_histogram_other_bin------------------------ t

`md5(string)`

Returns the MD5 hash of the string as a VARCHAR.

Query

SELECT md5('abc') AS md5;

Result

 md5---------------------------------- 900150983cd24fb0d6963f7d28e17f72

`md5_number(string)`

Returns the MD5 hash of the string as a UHUGEINT.

Query

SELECT md5_number('abc');

Result

 md5_number----------------------------------------- 152195979970564155685860391459828531600

`md5_number_lower(string)`

Returns the lower 8 bytes of the MD5 hash of string as a UBIGINT.

Query

SELECT md5_number_lower('abc') AS md5_number_lower;

Result

 md5_number_lower--------------------- 8250560606382298838

`md5_number_upper(string)`

Returns the upper 8 bytes of the MD5 hash of string as a UBIGINT.

Query

SELECT md5_number_upper('abc') AS md5_number_upper;

Result

 md5_number_upper---------------------- 12704604231530709392

`nextval('sequence_name')`

Return the following value of the sequence.

Query

SELECT nextval('seq_nextval') AS nextval;

Result

 nextval---------       2

`nullif(a, b)`

Return NULL if a = b, else return a. Equivalent to CASE WHEN a = b THEN NULL ELSE a END.

Query

SELECT nullif(1 + 1, 2) AS nullif;

Result

 nullif-------- NULL

`parse_formatted_bytes(string)`

Parse a human-readable byte size string (e.g., '16 KiB') into a UBIGINT number of bytes. Throws an error on invalid input.

Query

SELECT parse_formatted_bytes('1.5 GiB') AS parse_formatted_bytes;

Result

 parse_formatted_bytes-----------------------            1610612736

`pg_typeof(expression)`

Returns the lower case name of the data type of the result of the expression. For PostgreSQL compatibility.

Query

SELECT pg_typeof('abc') AS pg_typeof;

Result

 pg_typeof----------- text

`query(query_string)`

Table function that parses and executes the query defined in query_string. Only constant strings are allowed. Warning: this function allows invoking arbitrary queries, potentially altering the database state.

Query

SELECT * FROM query('SELECT 42 AS x');

Result

 x---- 42

`query_table(tbl_name)`

Table function that returns the table given in tbl_name.

Query

SELECT * FROM query_table('t1') ORDER BY x;

Result

 x--- 1 2

`query_table(tbl_names, [by_name])`

Table function that returns the union of tables given in tbl_names. If the optional by_name parameter is set to true, it uses UNION ALL BY NAME semantics.

Query

SELECT * FROM query_table(['t1', 't2'], true) ORDER BY x;

Result

 x--- 1 2 3

`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

`read_text(source)`

Returns the content from source (a filename, a list of filenames, or a glob pattern) as a VARCHAR. The file content is first validated to be valid UTF-8. If read_text attempts to read a file with invalid UTF-8 an error is thrown suggesting to use read_blob instead. See the read_text guide for more details.

Query

SELECT content FROM read_text('hello.txt');

Result

 content--------- hello

`sha1(string)`

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

Query

SELECT sha1('🌊') AS sha1;

Result

 sha1------------------------------------------ be25fe9cf30cf5c01ae2054b60a5e5511e966d98

`sha256(string)`

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

Query

SELECT sha256('🌊') AS sha256;

Result

 sha256------------------------------------------------------------------ 8db6bed4743d5a6159e0742df0fded8377d1250420aeda292e41ffa20eba806e

`sleep_ms(milliseconds)`

Pause execution for the specified number of milliseconds. Returns NULL.

Query

SELECT sleep_ms(1) AS sleep_ms;

Result

 sleep_ms---------- NULL

`stats(expression)`

Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.

Query

SELECT stats(5) AS stats;

Result

 stats------------------------------------------------------------------------- {"approx_unique":1,"has_no_null":true,"has_null":false,"max":5,"min":5}

`txid_current()`

Returns the current transaction's identifier, a BIGINT value. It will assign a new one if the current transaction does not have one already.

Query

SELECT txid_current();

Result

 txid_current--------------         1234

`typeof(expression)`

Returns the name of the data type of the result of the expression.

Query

SELECT typeof('abc') AS typeof;

Result

 typeof--------- VARCHAR

`uuid()`

Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.

Query

SELECT uuid();

Result

 uuid-------------------------------------- eeccb8c5-9943-b2bb-bb5e-222f4e14b687

`uuidv4()`

Return a random UUID (UUIDv4) similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.

Query

SELECT uuidv4();

Result

 uuidv4-------------------------------------- eeccb8c5-9943-b2bb-bb5e-222f4e14b687

`uuidv7()`

Return a random UUIDv7 similar to this: 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5.

Query

SELECT uuidv7();

Result

 uuidv7-------------------------------------- 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5

`uuid_extract_timestamp(uuidv7)`

Extracts a TIMESTAMP WITH TIME ZONE from a UUIDv7 value.

Query

SELECT uuid_extract_timestamp(uuidv7());

Result

 uuid_extract_timestamp--------------------------- 2025-04-19 15:51:20.07+00

`uuid_extract_version(uuid)`

Extracts the UUID version (4 or 7).

Query

SELECT uuid_extract_version(uuidv7()) AS uuid_extract_version;

Result

 uuid_extract_version----------------------                    7

`version()`

Return the currently active version of SereneDB.

Query

SELECT version();

Result

 version------------------------------------ PostgreSQL 18.3 (SereneDB 26.06.0)

Utility Table Functions

A table function is used in place of a table in a FROM clause.

Name	Description
`glob(search_path)`	Return filenames found at the location indicated by the search_path in a single column named `file`. The search_path may contain glob pattern matching syntax.
`repeat_row(varargs, num_rows)`	Returns a table with `num_rows` rows, each containing the fields defined in `varargs`.

`glob(search_path)`

Return filenames found at the location indicated by the search_path in a single column named file. The search_path may contain glob pattern matching syntax.

Query

SELECT * FROM glob('*');

Result

 file-------------- data.csv data.parquet

`repeat_row(varargs, num_rows)`

Returns a table with num_rows rows, each containing the fields defined in varargs.

Query

SELECT * FROM repeat_row(1, 2, 'foo', num_rows = 3);

Result

 column0 | column1 | column2---------+---------+---------       1 |       2 | foo       1 |       2 | foo       1 |       2 | foo

Scalar Utility Functions​

Setup​

alias(column)​

can_cast_implicitly(source_value, target_value)​

checkpoint(database)​

coalesce(expr, ...)​

constant_or_null(arg1, arg2)​

count_if(x)​

create_sort_key(parameters...)​

current_catalog()​

current_database()​

current_query()​

current_schema()​

current_schemas(boolean)​

current_setting('setting_name')​

currval('sequence_name')​

error(message)​

equi_width_bins(min, max, bincount, nice := false)​

force_checkpoint(database)​

gen_random_uuid()​

getenv(var)​

hash(value)​

icu_sort_key(string, collator)​

if(a, b, c)​

ifnull(expr, other)​

is_histogram_other_bin(arg)​

md5(string)​

md5_number(string)​

md5_number_lower(string)​

md5_number_upper(string)​

nextval('sequence_name')​

nullif(a, b)​

parse_formatted_bytes(string)​

pg_typeof(expression)​

query(query_string)​

query_table(tbl_name)​

query_table(tbl_names, [by_name])​

read_blob(source)​

read_text(source)​

sha1(string)​

sha256(string)​

sleep_ms(milliseconds)​

stats(expression)​

txid_current()​

typeof(expression)​

uuid()​

uuidv4()​

uuidv7()​

uuid_extract_timestamp(uuidv7)​

uuid_extract_version(uuid)​

version()​

Utility Table Functions​

glob(search_path)​

repeat_row(varargs, num_rows)​

Scalar Utility Functions

Setup

`alias(column)`

`can_cast_implicitly(source_value, target_value)`

`checkpoint(database)`

`coalesce(expr, ...)`

`constant_or_null(arg1, arg2)`

`count_if(x)`

`create_sort_key(parameters...)`

`current_catalog()`

`current_database()`

`current_query()`

`current_schema()`

`current_schemas(boolean)`

`current_setting('setting_name')`

`currval('sequence_name')`

`error(message)`

`equi_width_bins(min, max, bincount, nice := false)`

`force_checkpoint(database)`

`gen_random_uuid()`

`getenv(var)`

`hash(value)`

`icu_sort_key(string, collator)`

`if(a, b, c)`

`ifnull(expr, other)`

`is_histogram_other_bin(arg)`

`md5(string)`

`md5_number(string)`

`md5_number_lower(string)`

`md5_number_upper(string)`

`nextval('sequence_name')`

`nullif(a, b)`

`parse_formatted_bytes(string)`

`pg_typeof(expression)`

`query(query_string)`

`query_table(tbl_name)`

`query_table(tbl_names, [by_name])`

`read_blob(source)`

`read_text(source)`

`sha1(string)`

`sha256(string)`

`sleep_ms(milliseconds)`

`stats(expression)`

`txid_current()`

`typeof(expression)`

`uuid()`

`uuidv4()`

`uuidv7()`

`uuid_extract_timestamp(uuidv7)`

`uuid_extract_version(uuid)`

`version()`

Utility Table Functions

`glob(search_path)`

`repeat_row(varargs, num_rows)`