Utility Functions

Scalar Utility Functions

The functions below are difficult to categorize into specific function types and are broadly useful.

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; rows contribute 1 if `x` is `true` or a non-zero number, else 0.
`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 memory.
`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 main.
`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.
`hash(value)`	Returns a `UBIGINT` with a hash of `value`. The used hash function may change across Goose versions.
`icu_sort_key(string, collator)`	Surrogate sort key used to sort special characters according to the specific locale. Collator parameter is optional. Only available when the ICU extension is installed.
`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`.
`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`.
`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 Goose in this format.

`alias(column)`

`can_cast_implicitly(source_value, target_value)`

`checkpoint(database)`

`coalesce(expr, ...)`

| Description | 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. | | Example | coalesce(NULL, NULL, 'default_string') | | Result | default_string |

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

| Description | 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. | | Example | equi_width_bins(0.1, 2.7, 4, true) | | Result | [0.5, 1.0, 1.5, 2.0, 2.5, 3.0] |

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

| Description | 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. | | Example | is_histogram_other_bin('') | | Result | true |

`md5(string)`

`md5_number(string)`

`md5_number_lower(string)`

`md5_number_upper(string)`

`nextval('sequence_name')`

`nullif(a, b)`

`pg_typeof(expression)`

`query(query_string)`

| Description | 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. | | Example | query('SELECT 42 AS x') | | Result | 42 |

`query_table(tbl_name)`

`query_table(tbl_names, [by_name])`

| Description | 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. | | Example | query_table(['t1', 't2']) | | Result | (the union of the two tables) |

`read_blob(source)`

`read_text(source)`

| Description | 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. | | Example | read_text('hello.txt') | | Result | hello\n |

`sha1(string)`

`sha256(string)`

`stats(expression)`

`txid_current()`

`typeof(expression)`

`uuid()`

`uuidv4()`

`uuidv7()`

`uuid_extract_timestamp(uuidv7)`

`uuid_extract_version(uuid)`

`version()`

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

`repeat_row(varargs, num_rows)`

Scalar Utility Functions​

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)​

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)​

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)​