Skip to main content
SereneDBSereneDB
961Contact Us

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.

NameDescription
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.
lastval()Return the value nextval most recently returned in the current session, for whichever sequence it advanced.
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, in the type a and b are compared in.
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​

SQL
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.

SQL
SELECT alias(column1) FROM (SELECT 1 AS column1) t;
 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.

SQL
SELECT can_cast_implicitly(1::BIGINT, 1::SMALLINT) AS can_cast_implicitly;
 can_cast_implicitly
---------------------
 f

checkpoint(database)​

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

SQL
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.

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

constant_or_null(arg1, arg2)​

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

SQL
SELECT constant_or_null(42, NULL) AS constant_or_null;
 constant_or_null
------------------
 NULL

count_if(x)​

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

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

create_sort_key(parameters...)​

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

SQL
SELECT create_sort_key('abc', 'ASC NULLS FIRST') AS create_sort_key;
 create_sort_key
-----------------
 \\x0262636400

current_catalog()​

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

SQL
SELECT current_catalog();
 current_catalog
-----------------
 postgres

current_database()​

Return the name of the currently active database.

SQL
SELECT current_database();
 current_database
------------------
 postgres

current_query()​

Return the current query as a string.

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

current_schema()​

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

SQL
SELECT current_schema() AS current_schema;
 current_schema
----------------
 public

current_schemas(boolean)​

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

SQL
SELECT current_schemas(true) AS current_schemas;
 current_schemas
---------------------
 {pg_catalog,public}

current_setting('setting_name')​

Return the current value of the configuration setting.

SQL
SELECT current_setting('access_mode') AS current_setting;
 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.

SQL
SELECT currval('seq_currval') AS currval;
 currval
---------
       1

error(message)​

Throws the given error message.

SQL
SELECT error('access_mode');
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.

SQL
SELECT equi_width_bins(0.1, 2.7, 4, true) AS equi_width_bins;
 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.

SQL
FORCE CHECKPOINT;

gen_random_uuid()​

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

SQL
SELECT gen_random_uuid();
 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).

SQL
SELECT getenv('HOME');
 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.

SQL
SELECT hash('🌊') AS hash;
 hash
----------------------
 12255790945742965276

hash_city64(value, ...)​

Returns a UBIGINT with the CityHash64 hash (CityHash v1.1) of the bytes of value: the UTF-8 bytes of a VARCHAR, the bytes of a BLOB, and for a BOOLEAN or a number its little-endian bytes at the width of its type, so hash_city64(1) (an INTEGER) and hash_city64(1::BIGINT) differ. Each further value is hashed with CityHash64WithSeed, seeded with the hash of the values before it. The result is the same in every SereneDB version and matches other CityHash v1.1 implementations. Other types must be cast to one of these first.

SQL
SELECT hash_city64('🌊') AS hash_city64;
 hash_city64
----------------------
 15517191736451674290

icu_sort_key(string, collator)​

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

SQL
SELECT icu_sort_key('a', 'DE') < icu_sort_key('b', 'DE') AS icu_sort_key;
 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.

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

ifnull(expr, other)​

A two-argument version of coalesce.

SQL
SELECT ifnull(NULL, 'default_string') AS ifnull;
 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.

SQL
SELECT is_histogram_other_bin('') AS is_histogram_other_bin;
 is_histogram_other_bin
------------------------
 t

lastval()​

Return the value nextval most recently returned in the current session, for whichever sequence it advanced. It fails until nextval has run in the session, after that sequence is dropped, and without USAGE or SELECT on that sequence.

SQL
SELECT lastval() AS lastval;
 lastval
---------
      42

md5(string)​

Returns the MD5 hash of the string as a VARCHAR.

SQL
SELECT md5('abc') AS md5;
 md5
----------------------------------
 900150983cd24fb0d6963f7d28e17f72

md5_number(string)​

Returns the MD5 hash of the string as a UHUGEINT.

SQL
SELECT md5_number('abc');
 md5_number
-----------------------------------------
 152195979970564155685860391459828531600

md5_number_lower(string)​

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

SQL
SELECT md5_number_lower('abc') AS md5_number_lower;
 md5_number_lower
---------------------
 8250560606382298838

md5_number_upper(string)​

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

SQL
SELECT md5_number_upper('abc') AS md5_number_upper;
 md5_number_upper
----------------------
 12704604231530709392

nextval('sequence_name')​

Return the following value of the sequence. sequence_name is read as the name is in SQL text, optionally schema-qualified: an unquoted part folds as an unquoted identifier does, a part in double quotes is kept as written. currval and setval read the name the same way.

SQL
SELECT nextval('seq_nextval') AS nextval;
 nextval
---------
       2

nullif(a, b)​

Return NULL if a = b, else return a. The result has the type a and b are compared in, so nullif(5, 5.5) is a NUMERIC. Each argument is evaluated once.

SQL
SELECT nullif(1 + 1, 2) AS nullif;
 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.

SQL
SELECT parse_formatted_bytes('1.5 GiB') AS parse_formatted_bytes;
 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.

SQL
SELECT pg_typeof('abc') AS pg_typeof;
 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.

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

query_table(tbl_name)​

Table function that returns the table given in tbl_name.

SQL
SELECT * FROM query_table('t1') ORDER BY x;
 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.

SQL
SELECT * FROM query_table(['t1', 't2'], true) ORDER BY x;
 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.

SQL
SELECT content FROM read_blob('hello.bin');
 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.

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

sha1(string)​

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

SQL
SELECT sha1('🌊') AS sha1;
 sha1
------------------------------------------
 be25fe9cf30cf5c01ae2054b60a5e5511e966d98

sha256(string)​

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

SQL
SELECT sha256('🌊') AS sha256;
 sha256
------------------------------------------------------------------
 8db6bed4743d5a6159e0742df0fded8377d1250420aeda292e41ffa20eba806e

sleep_ms(milliseconds)​

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

SQL
SELECT sleep_ms(1) AS sleep_ms;
 sleep_ms
----------
 NULL

stats(expression)​

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

SQL
SELECT stats(5) AS stats;
 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.

SQL
SELECT txid_current();
 txid_current
--------------
         1234

typeof(expression)​

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

SQL
SELECT typeof('abc') AS typeof;
 typeof
---------
 VARCHAR

uuid()​

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

SQL
SELECT uuid();
 uuid
--------------------------------------
 eeccb8c5-9943-b2bb-bb5e-222f4e14b687

uuidv4()​

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

SQL
SELECT uuidv4();
 uuidv4
--------------------------------------
 eeccb8c5-9943-b2bb-bb5e-222f4e14b687

uuidv7()​

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

SQL
SELECT uuidv7();
 uuidv7
--------------------------------------
 81964ebe-00b1-7e1d-b0f9-43c29b6fb8f5

uuid_extract_timestamp(uuidv7)​

Extracts a TIMESTAMP WITH TIME ZONE from a UUIDv7 value.

SQL
SELECT uuid_extract_timestamp(uuidv7());
 uuid_extract_timestamp
---------------------------
 2025-04-19 15:51:20.07+00

uuid_extract_version(uuid)​

Extracts the UUID version (4 or 7).

SQL
SELECT uuid_extract_version(uuidv7()) AS uuid_extract_version;
 uuid_extract_version
----------------------
                    7

version()​

Return the currently active version of SereneDB.

SQL
SELECT version();
 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.

NameDescription
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.

SQL
SELECT * FROM glob('*');
 file
--------------
 data.csv
 data.parquet

repeat_row(varargs, num_rows)​

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

SQL
SELECT * FROM repeat_row(1, 2, 'foo', num_rows = 3);
 column0 | column1 | column2
---------+---------+---------
       1 |       2 | foo
       1 |       2 | foo
       1 |       2 | foo
Edit this page ↗