Utility Functions
The functions below are difficult to categorize into specific function types and are broadly useful.
| Function | Description | Example | Result |
|---|---|---|---|
alias(column) |
Return the name of the column | alias(column1) |
'column1' |
checkpoint(database) |
Synchronize WAL with file for (optional) database without interrupting transactions. | checkpoint(my_db) |
success boolean |
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. |
coalesce(NULL,NULL,'default_string') |
'default_string' |
ifnull(expr, other) |
A two-argument version of coalesce | ifnull(NULL,'default_string') |
'default_string' |
current_schema() |
Return the name of the currently active schema. Default is main. | current_schema() |
'main' |
current_schemas(boolean) |
Return list of schemas. Pass a parameter of True to include implicit schemas. |
current_schemas(true) |
['temp', 'main', 'pg_catalog'] |
current_setting('setting_name') |
Return the current value of the configuration setting | current_setting('access_mode') |
'automatic' |
currval('sequence_name') |
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval. |
currval('my_sequence_name') |
1 |
force_checkpoint(database) |
Synchronize WAL with file for (optional) database interrupting transactions. | force_checkpoint(my_db) |
success boolean |
gen_random_uuid() |
Alias of uuid. Return a random uuid similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687. |
gen_random_uuid() |
various |
hash(value) |
Returns an integer with the hash of the value | hash('🦆') |
2595805878642663834 |
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. | icu_sort_key('ö','DE') |
460145960106 |
md5(string) |
Return an md5 one-way hash of the string. | md5('123') |
'202cb962ac59075b964b07152d234b70' |
nextval('sequence_name') |
Return the following value of the sequence. | nextval('my_sequence_name') |
2 |
pg_typeof(expression) |
Returns the lower case name of the data type of the result of the expression. For Postgres compatibility. | pg_typeof('abc') |
'varchar' |
stats(expression) |
Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression. | stats(5) |
'[Min: 5, Max: 5][Has Null: false]' |
txid_current() |
Returns the current transaction's ID (a BIGINT). It will assign a new one if the current transaction does not have one already. |
txid_current() |
various |
typeof(expression) |
Returns the name of the data type of the result of the expression. | typeof('abc') |
'VARCHAR' |
uuid() |
Return a random uuid similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687. | uuid() |
various |
version() |
Return the currently active version of DuckDB in this format: v0.3.2 |
version() |
various |
Utility Table Functions
A table function is used in place of a table in a FROM clause.
| Function | Description | Example |
|---|---|---|
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. |
glob('*') |