Search Shortcut cmd + k | ctrl + k
Search cmd+k ctrl+k
0.10 (stable)
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.
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) Returns 1 if x is true or a non-zero number.
current_catalog() Return the name of the currently active catalog. Default is memory.
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.
force_checkpoint(database) Synchronize WAL with file for (optional) database interrupting transactions.
gen_random_uuid() Alias of uuid. Return a random UUID similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
hash(value) Returns a UBIGINT with the hash of the value.
icu_sort_key(string, collator) Surrogate key used to sort special characters according to the specific locale. Collator parameter is optional. Valid only when ICU extension is installed.
ifnull(expr, other) A two-argument version of coalesce.
md5(string) Return an MD5 hash of the string.
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.
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.
sha256(value) Returns a VARCHAR with the SHA-256 hash of the value.
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 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
version() Return the currently active version of DuckDB in this format.

alias(column)

Description Return the name of the column.
Example alias(column1)
Result column1

checkpoint(database)

Description Synchronize WAL with file for (optional) database without interrupting transactions.
Example checkpoint(my_db)
Result success boolean

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)

Description If arg2 is NULL, return NULL. Otherwise, return arg1.
Example constant_or_null(42, NULL)
Result NULL

count_if(x)

Description Returns 1 if x is true or a non-zero number.
Example count_if(42)
Result 1

current_catalog()

Description Return the name of the currently active catalog. Default is memory.
Example current_catalog()
Result memory

current_schema()

Description Return the name of the currently active schema. Default is main.
Example current_schema()
Result main

current_schemas(boolean)

Description Return list of schemas. Pass a parameter of true to include implicit schemas.
Example current_schemas(true)
Result ['temp', 'main', 'pg_catalog']

current_setting('setting_name')

Description Return the current value of the configuration setting.
Example current_setting('access_mode')
Result automatic

currval('sequence_name')

Description Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
Example currval('my_sequence_name')
Result 1

error(message)

Description Throws the given error message.
Example error('access_mode')

force_checkpoint(database)

Description Synchronize WAL with file for (optional) database interrupting transactions.
Example force_checkpoint(my_db)
Result success boolean

gen_random_uuid()

Description Alias of uuid. Return a random UUID similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
Example gen_random_uuid()
Result various

hash(value)

Description Returns a UBIGINT with the hash of the value.
Example hash('🦆')
Result 2595805878642663834

icu_sort_key(string, collator)

Description Surrogate key used to sort special characters according to the specific locale. Collator parameter is optional. Valid only when ICU extension is installed.
Example icu_sort_key('ö', 'DE')
Result 460145960106

ifnull(expr, other)

Description A two-argument version of coalesce.
Example ifnull(NULL, 'default_string')
Result default_string

md5(string)

Description Return an MD5 hash of the string.
Example md5('123')
Result 202cb962ac59075b964b07152d234b70

nextval('sequence_name')

Description Return the following value of the sequence.
Example nextval('my_sequence_name')
Result 2

nullif(a, b)

Description Return null if a = b, else return a. Equivalent to CASE WHEN a = b THEN NULL ELSE a END.
Example nullif(1+1, 2)
Result NULL

pg_typeof(expression)

Description Returns the lower case name of the data type of the result of the expression. For PostgreSQL compatibility.
Example pg_typeof('abc')
Result varchar

read_blob(source)

Description 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.
Example read_blob('hello.bin')
Result hello\x0A

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

sha256(value)

Description Returns a VARCHAR with the SHA-256 hash of the value.
Example sha256('🦆')
Result d7a5c5e0d1d94c32218539e7e47d4ba9c3c7b77d61332fb60d633dde89e473fb

stats(expression)

Description Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression.
Example stats(5)
Result '[Min: 5, Max: 5][Has Null: false]'

txid_current()

Description Returns the current transaction’s identifier, a BIGINT value. It will assign a new one if the current transaction does not have one already.
Example txid_current()
Result various

typeof(expression)

Description Returns the name of the data type of the result of the expression.
Example typeof('abc')
Result VARCHAR

uuid()

Description Return a random UUID similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687.
Example uuid()
Result various

version()

Description Return the currently active version of DuckDB in this format.
Example version()
Result various

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)

Description 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.
Example glob('*')
Result (table of filenames)

repeat_row(varargs, num_rows)

Description Returns a table with num_rows rows, each containing the fields defined in varargs.
Example repeat_row(1, 2, 'foo', num_rows = 3)
Result 3 rows of 1, 2, 'foo'
About this page

Last modified: 2024-03-29