Utility Functions
Version current

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'
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'
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
gen_random_uuid() Alias of uuid. Return a random uuid similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687. gen_random_uuid() various
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