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) |
Aggregate function; rows contribute 1 if x is true or a non-zero number, else 0. |
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. |
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() |
Alias of 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 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. |
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 HUGEINT. |
md5_number_lower(string) |
Returns the lower 64-bit segment of the MD5 hash of the string as a BIGINT. |
md5_number_higher(string) |
Returns the higher 64-bit segment of the MD5 hash of the string as a BIGINT. |
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_literal) |
Table function that parses and executes the query defined in query_string_literal. Only literal 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. |
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 | Aggregate function; rows contribute 1 if x is true or a non-zero number, else 0. |
| 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') |
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)
| 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 |
getenv(var)
| Description | Returns the value of the environment variable var. Only available in the command line client. |
| Example | getenv('HOME') |
| Result | /path/to/user/home |
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 |
if(a, b, c)
| Description | Ternary conditional operator; returns b if a, else returns c. Equivalent to CASE WHEN a THEN b ELSE c END. |
| Example | if(2 > 1, 3, 4) |
| Result | 3 |
ifnull(expr, other)
| Description | A two-argument version of coalesce. |
| Example | ifnull(NULL, 'default_string') |
| Result | default_string |
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)
| Description | Returns the MD5 hash of the string as a VARCHAR. |
| Example | md5('123') |
| Result | 202cb962ac59075b964b07152d234b70 |
md5_number(string)
| Description | Returns the MD5 hash of the string as a HUGEINT. |
| Example | md5_number('123') |
| Result | 149263671248412135425768892945843956768 |
md5_number_lower(string)
| Description | Returns the lower 8 bytes of the MD5 hash of string as a BIGINT. |
| Example | md5_number_lower('123') |
| Result | 8091599832034528150 |
md5_number_higher(string)
| Description | Returns the higher 8 bytes of the MD5 hash of string as a BIGINT. |
| Example | md5_number_higher('123') |
| Result | 6559309979213966368 |
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 |
query(query_string_literal)
| Description | Table function that parses and executes the query defined in query_string_literal. Only literal 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)
| Description | Table function that returns the table given in tbl_name. |
| Example | query('t1') |
| Result | (the rows of t1) |
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(['t1', 't2']) |
| Result | (the union of the two tables) |
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' |