Functions

left ->> right

x!

string ^@ search_string

Factorial of x. Computes the product of the current integer and all integers below it

@x

Absolute value

Returns true if all elements of l2 are in l1. NULLs are ignored.

Computes x to the power of y

Bitwise AND

Returns true if the lists have any element in common. NULLs are ignored.

Computes x to the power of y

Returns true if string begins with search_string

++operandleft + right

list1 <-> list2

Compute the distance between two lists

l1 <@ l2

Returns true if all elements of l2 are in l1. NULLs are ignored.

Bitwise shift left

Compute the cosine distance between two lists

Bitwise shift right

Bitwise OR

Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(string, ...) and list_concat(list1, list2)

Bitwise NOT

Absolute value

Computes the arccosine of x

acosh(x)

add(…)add(col0)add(col0, col1)

Computes the inverse hyperbolic cos of x

add_parquet_key(col0, col1)

age(timestamp)age(timestamp, timestamp)

aggregate(list, name, …)

Subtract arguments, resulting in the time difference between the two timestamps

Executes the aggregate function name on the elements of list

alias(expr)

Returns the name of a given expression

all_profiling_output()

any_value(arg)

Returns the first non-null value from arg. This function is affected by ordering.

apply(list, lambda)

approx_count_distinct(any)

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details

approx_quantile(x, pos)

Computes the approximate count of distinct elements using HyperLogLog.

Computes the approximate quantile using T-Digest.

approx_top_k(val, k)

Finds the k approximately most occurring values in the data set

arbitrary(arg)

arg_max(arg, val)arg_max(arg, val, col2)

Returns the first value (null or non-null) from arg. This function is affected by ordering.

Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.

arg_max_null(arg, val)

arg_min(arg, val)arg_min(arg, val, col2)

Finds the row with the maximum val. Calculates the arg expression at that row.

Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.

arg_min_null(arg, val)

argmax(arg, val)argmax(arg, val, col2)

Finds the row with the minimum val. Calculates the arg expression at that row.

argmin(arg, val)argmin(arg, val, col2)

Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.

Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.

array_agg(arg)

array_aggr(list, name, …)

Returns a LIST containing all the values of a column.

array_aggregate(list, name, …)

Executes the aggregate function name on the elements of list

Executes the aggregate function name on the elements of list

array_append(arr, el)

array_apply(list, lambda)

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details

array_cat(…)

Concatenates two lists.

array_concat(…)

array_contains(list, element)

Concatenates two lists.

array_cosine_distance(array1, array2)

Returns true if the list contains the element.

array_cosine_similarity(array1, array2)

Compute the cosine distance between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

array_cross_product(array, array)

Compute the cosine similarity between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

array_distance(array1, array2)

Compute the cross product of two arrays of size 3. The array elements can not be NULL.

Compute the distance between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

array_distinct(list)

array_dot_product(array1, array2)

Removes all duplicates and NULLs from a list. Does not preserve the original order

array_extract(list, index)

Compute the inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

array_filter(list, lambda)

Extract the indexth (1-based) value from the array.

array_grade_up(list)array_grade_up(list, col1)array_grade_up(list, col1, col2)

Constructs a list from those elements of the input list for which the lambda function returns true

array_has(list, element)

Returns the index of their sorted position.

Returns true if the list contains the element.

array_has_all(l1, l2)

Returns true if all elements of l2 are in l1. NULLs are ignored.

array_has_any(l1, l2)

array_indexof(list, element)

Returns true if the lists have any element in common. NULLs are ignored.

array_inner_product(array1, array2)

Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.

array_intersect(l1, l2)

Compute the inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

array_length(list)array_length(list, col1)

array_negative_dot_product(array1, array2)

Returns the length of the list.

array_negative_inner_product(array1, array2)

Compute the negative inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

Compute the negative inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

array_pop_back(arr)

array_pop_front(arr)

array_position(list, element)

Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.

array_prepend(el, arr)

array_push_back(arr, e)

array_push_front(arr, e)

array_reduce(list, lambda[)array_reduce(list, lambda[, initial])

array_resize(list, size[)array_resize(list, size[, value])

Returns a single value that is the result of applying the lambda function to each element of the input list, starting with the first element and then repeatedly applying the lambda function to the result of the previous application and the next element of the list. When an initial value is provided, it is used as the first argument to the lambda function

Resizes the list to contain size elements. Initializes new elements with value or NULL if value is not set.

array_reverse(l)

array_reverse_sort(list)array_reverse_sort(list, col1)

array_select(value_list, index_list)

Sorts the elements of the list in reverse order

array_slice(list, begin, end)array_slice(list, begin, end, step)

Returns a list based on the elements selected by the index_list.

array_sort(list)array_sort(list, col1)array_sort(list, col1, col2)

list_slice with added step feature.

Sorts the elements of the list

array_to_json(…)

array_to_string(arr, sep)

array_to_string_comma_default(arr, sep)

array_transform(list, lambda)

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details

array_unique(list)

Counts the unique elements of a list

array_value(…)

array_where(value_list, mask_list)

Create an ARRAY containing the argument values.

Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list.

array_zip(…)

arrow_scan(col0, col1, col2)

Zips k LISTs to a new LIST whose length will be that of the longest list. Its elements are structs of k elements from each list list_1, …, list_k, missing elements are replaced with NULL. If truncate is set, all lists are truncated to the smallest list length.

arrow_scan_dumb(col0, col1, col2)

ascii(string)

Returns an integer that represents the Unicode code point of the first character of the string

asin(x)

Computes the arcsine of x

asinh(x)

Computes the inverse hyperbolic sin of x

atan(x)

Computes the arctangent of x

atan2(y, x)

Computes the arctangent (y, x)

atanh(x)

Computes the inverse hyperbolic tan of x

avg(x)

bar(x, min, max)bar(x, min, max, width)

Calculates the average value for all tuples in x.

Draws a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80

base64(blob)

Converts a blob to a base64 encoded string.

bin(value)

Converts the value to binary representation

bit_and(arg)

Returns the bitwise AND of all bits in a given expression.

bit_count(x)

bit_position(substring, bitstring)

Returns the number of bits that are set

Returns the bitwise OR of all bits in a given expression.

Returns first starting index of the specified substring within bits, or zero if it is not present. The first (leftmost) bit is indexed 1

bit_xor(arg)

bitstring(bitstring, length)

Returns the bitwise XOR of all bits in a given expression.

bitstring_agg(arg)bitstring_agg(arg, col1, col2)

Pads the bitstring until the specified length

Returns a bitstring with bits set for each distinct value.

bool_and(arg)

Returns TRUE if every input value is TRUE, otherwise FALSE.

bool_or(arg)

can_cast_implicitly(source_type, target_type)

Returns TRUE if any input value is TRUE, otherwise FALSE.

Whether or not we can implicitly cast from the source type to the other type

cardinality(map, …)

cast_to_type(param, type)

Returns the size of the map (or the number of entries in the map)

Casts the first argument to the type of the second argument

cbrt(x)

Returns the cube root of x

Rounds the number up

Rounds the number up

Extract the century component from a date or timestamp

char_length(string)

character_length(string)

Number of characters in string.

Number of characters in string.

check_peg_parser(col0)

checkpoint()checkpoint(col0)

chr(code_point)

Returns a character which is corresponding the ASCII code value or Unicode code point

collations()

combine(col0, col1)

concat(string, …)

concat_ws(separator, string, …)

Concatenates many strings together.

constant_or_null(arg1, arg2, …)

Concatenates strings together separated by the specified separator.

contains(list, element)contains(map, key)contains(string, search_string)

If arg2 is NULL, return NULL. Otherwise, return arg1.

copy_database(col0, col1)

Returns true if the list contains the element.

corr(y, x)

Returns the correlation coefficient for non-null pairs in a group.

cos(x)

Computes the cos of x

cosh(x)

Computes the hyperbolic cos of x

cot(x)

Computes the cotangent of x

count()count(arg)

Returns the number of non-null values in arg.

count_if(arg)

Counts the total number of TRUE values for a boolean column

Counts the total number of TRUE values for a boolean column

covar_pop(y, x)

Returns the population covariance of input values.

covar_samp(y, x)

create_sort_key(parameters..., …)

Returns the sample covariance for non-null pairs in a group.

Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers

current_catalog()

current_connection_id()

Get the current connection_id

current_database()

Returns the name of the currently active database

current_date()

current_localtime()

current_localtimestamp()

current_query()

Returns the current query as a string

current_query_id()

current_schemas(include_implicit)

Get the current query_id

Returns the name of the currently active schema. Default is main

current_setting(setting_name)

Returns list of schemas. Pass a parameter of True to include implicit schemas

current_transaction_id()

Returns the current value of the configuration setting

Get the current global transaction_id

current_user()

currval('sequence_name')

damerau_levenshtein(str1, str2)

Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.

date_add(date, interval)

Extension of Levenshtein distance to also include transposition of adjacent characters as an allowed edit operation. In other words, the minimum number of edit operations (insertions, deletions, substitutions or transpositions) required to change one string to another. Different case is considered different

date_diff(part, startdate, enddate)

The number of partition boundaries between the timestamps

date_part(ts, col1)

date_sub(part, startdate, enddate)

Get subfield (equivalent to extract)

date_trunc(part, timestamp)

The number of complete partitions between the timestamps

datediff(part, startdate, enddate)

Truncate to specified precision

The number of partition boundaries between the timestamps

datepart(ts, col1)

datesub(part, startdate, enddate)

Get subfield (equivalent to extract)

datetrunc(part, timestamp)

The number of complete partitions between the timestamps

Truncate to specified precision

day(ts)

Extract the day component from a date or timestamp

dayname(ts)

The (English) name of the weekday

dayofmonth(ts)

Extract the dayofmonth component from a date or timestamp

dayofweek(ts)

Extract the dayofweek component from a date or timestamp

dayofyear(ts)

Extract the dayofyear component from a date or timestamp

decade(ts)

Extract the decade component from a date or timestamp

decode(blob)

Converts blob to VARCHAR. Fails if blob is not valid UTF-8.

degrees(x)

disable_checkpoint_on_shutdown()

Converts radians to degrees

disable_logging()

disable_object_cache()

disable_optimizer()

disable_print_progress_bar()

disable_profile()

disable_profiling()

disable_progress_bar()

disable_verification()

disable_verify_external()

disable_verify_fetch_row()

disable_verify_parallelism()

disable_verify_serializer()

duckdb_constraints()

duckdb_databases()

duckdb_dependencies()

duckdb_extensions()

duckdb_external_file_cache()

duckdb_log_contexts()

duckdb_logs()

duckdb_logs_parsed(log_type)

Table macro

duckdb_memory()

duckdb_optimizers()

duckdb_prepared_statements()

duckdb_schemas()

duckdb_secret_types()

duckdb_secrets(named arguments)

duckdb_table_sample(col0)

duckdb_tables()

duckdb_temporary_files()

editdist3(str1, str2)

element_at(map, key, …)

The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different

enable_checkpoint_on_shutdown()

Returns a list containing the value for a given key or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map’s keys else an error is returned

enable_logging(…)

enable_object_cache()

enable_optimizer()

enable_print_progress_bar()

enable_progress_bar()

enable_verification()

encode(string)

Converts the string to BLOB. Converts UTF-8 characters into literal encoding.

ends_with(col0, col1)

entropy(x)

Returns the log-2 entropy of count input-values.

enum_code(enum)

Returns the numeric value backing the given enum value

enum_first(enum)

Returns the first value of the input enum type

enum_last(enum)

Returns the last value of the input enum type

enum_range(enum)

enum_range_boundary(start, end)

Returns all values of the input enum type as an array

Returns the range between the two given enum values as an array. The values must be of the same enum type. When the first parameter is NULL, the result starts with the first value of the enum type. When the second parameter is NULL, the result ends with the last value of the enum type

epoch(temporal)

Extract the epoch component from a temporal type

epoch_ms(temporal)

Extract the epoch component in milliseconds from a temporal type

epoch_ns(temporal)

Extract the epoch component in nanoseconds from a temporal type

epoch_us(temporal)

equi_width_bins(min, max, bin_count, nice_rounding)

Extract the epoch component in microseconds from a temporal type

Generates bin_count equi-width bins between the min and max. If enabled nice_rounding makes the numbers more readable/less jagged

era(ts)

Extract the era component from a date or timestamp

error(message)

Throws the given error message

even(x)

Rounds x to next even number by rounding away from zero

exp(x)

Computes e to the power of x

extension_versions()

factorial(x)

Factorial of x. Computes the product of the current integer and all integers below it

favg(x)

Calculates the average using a more accurate floating point summation (Kahan Sum)

fdiv(x, y)

filter(list, lambda)

Constructs a list from those elements of the input list for which the lambda function returns true

Returns the first value (null or non-null) from arg. This function is affected by ordering.

flatten(nested_list)

Flatten a nested list by one level

floor(x)

Rounds the number down

fmod(x, y)

force_checkpoint()force_checkpoint(col0)

Pragma Table

format(format, …)

Formats a string using fmt syntax

format_bytes(bytes)

formatReadableDecimalSize(bytes)

Converts bytes to a human-readable presentation (e.g., 16000 -> 15.6 KiB)

formatReadableSize(bytes)

Converts bytes to a human-readable presentation (e.g., 16000 -> 16.0 KB)

Converts bytes to a human-readable presentation (e.g., 16000 -> 15.6 KiB)

from_base64(string)

Converts a base64 encoded string to a character string (BLOB).

from_binary(value)

Converts a value from binary representation to a blob.

from_hex(value)

Converts a value from hexadecimal representation to a blob.

from_json(col0, col1)

from_json_strict(col0, col1)

fsum(arg)

Calculates the sum using a more accurate floating point summation (Kahan Sum).

Interpolation of (x-1) factorial (so decimal inputs are allowed)

gcd(x, y)

Computes the greatest common divisor of x and y

gen_random_uuid()

generate_series(start)generate_series(start, stop)generate_series(start, stop, step)generate_series(col0)generate_series(col0, col1)generate_series(col0, col1, col2)

Returns a random UUID v4 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687

Scalar Table

Create a list of values between start and stop - the stop parameter is inclusive

generate_subscripts(arr, dim)

get_bit(bitstring, index)

get_block_size(db_name)

Extracts the nth bit from bitstring; the first (leftmost) bit is indexed 0

get_current_time()

get_current_timestamp()

grade_up(list)grade_up(list, col1)grade_up(list, col1, col2)

Returns the current timestamp

Returns the index of their sorted position.

greatest(arg1, …)

greatest_common_divisor(x, y)

Returns the highest value of the set of input parameters

group_concat(str)group_concat(str, arg)

Computes the greatest common divisor of x and y

Concatenates the column string values with an optional separator.

hamming(str1, str2)

The number of positions with different characters for 2 strings of equal length. Different case is considered different

hash(param, …)

hex(blob)hex(string)hex(value)

Returns an integer with the hash of the value. Note that this is not a cryptographic hash

histogram(arg)histogram(arg, col1)histogram(source, col_name, technique, bin_count)

Converts blob to VARCHAR using hexadecimal encoding.

Aggregate Table macro

Returns a LIST of STRUCTs with the fields bucket and count.

histogram_exact(arg, bins)

histogram_values(source, col_name, technique, bin_count)

Returns a LIST of STRUCTs with the fields bucket and count matching the buckets exactly.

Table macro

hour(ts)

Extract the hour component from a date or timestamp

icu_calendar_names()

icu_collate_af(col0)

icu_collate_am(col0)

icu_collate_ar(col0)

icu_collate_ar_sa(col0)

icu_collate_as(col0)

icu_collate_az(col0)

icu_collate_be(col0)

icu_collate_bg(col0)

icu_collate_bn(col0)

icu_collate_bo(col0)

icu_collate_br(col0)

icu_collate_bs(col0)

icu_collate_ca(col0)

icu_collate_ceb(col0)

icu_collate_chr(col0)

icu_collate_cs(col0)

icu_collate_cy(col0)

icu_collate_da(col0)

icu_collate_de(col0)

icu_collate_de_at(col0)

icu_collate_dsb(col0)

icu_collate_dz(col0)

icu_collate_ee(col0)

icu_collate_el(col0)

icu_collate_en(col0)

icu_collate_en_us(col0)

icu_collate_eo(col0)

icu_collate_es(col0)

icu_collate_et(col0)

icu_collate_fa(col0)

icu_collate_fa_af(col0)

icu_collate_ff(col0)

icu_collate_fi(col0)

icu_collate_fil(col0)

icu_collate_fo(col0)

icu_collate_fr(col0)

icu_collate_fr_ca(col0)

icu_collate_fy(col0)

icu_collate_ga(col0)

icu_collate_gl(col0)

icu_collate_gu(col0)

icu_collate_ha(col0)

icu_collate_haw(col0)

icu_collate_he(col0)

icu_collate_he_il(col0)

icu_collate_hi(col0)

icu_collate_hr(col0)

icu_collate_hsb(col0)

icu_collate_hu(col0)

icu_collate_hy(col0)

icu_collate_id(col0)

icu_collate_id_id(col0)

icu_collate_ig(col0)

icu_collate_is(col0)

icu_collate_it(col0)

icu_collate_ja(col0)

icu_collate_ka(col0)

icu_collate_kk(col0)

icu_collate_kl(col0)

icu_collate_km(col0)

icu_collate_kn(col0)

icu_collate_ko(col0)

icu_collate_kok(col0)

icu_collate_ku(col0)

icu_collate_ky(col0)

icu_collate_lb(col0)

icu_collate_lkt(col0)

icu_collate_ln(col0)

icu_collate_lo(col0)

icu_collate_lt(col0)

icu_collate_lv(col0)

icu_collate_mk(col0)

icu_collate_ml(col0)

icu_collate_mn(col0)

icu_collate_mr(col0)

icu_collate_ms(col0)

icu_collate_mt(col0)

icu_collate_my(col0)

icu_collate_nb(col0)

icu_collate_nb_no(col0)

icu_collate_ne(col0)

icu_collate_nl(col0)

icu_collate_nn(col0)

icu_collate_noaccent(col0)

icu_collate_om(col0)

icu_collate_or(col0)

icu_collate_pa(col0)

icu_collate_pa_in(col0)

icu_collate_pl(col0)

icu_collate_ps(col0)

icu_collate_pt(col0)

icu_collate_ro(col0)

icu_collate_ru(col0)

icu_collate_sa(col0)

icu_collate_se(col0)

icu_collate_si(col0)

icu_collate_sk(col0)

icu_collate_sl(col0)

icu_collate_smn(col0)

icu_collate_sq(col0)

icu_collate_sr(col0)

icu_collate_sr_ba(col0)

icu_collate_sr_me(col0)

icu_collate_sr_rs(col0)

icu_collate_sv(col0)

icu_collate_sw(col0)

icu_collate_ta(col0)

icu_collate_te(col0)

icu_collate_th(col0)

icu_collate_tk(col0)

icu_collate_to(col0)

icu_collate_tr(col0)

icu_collate_ug(col0)

icu_collate_uk(col0)

icu_collate_ur(col0)

icu_collate_uz(col0)

icu_collate_vi(col0)

icu_collate_wae(col0)

icu_collate_wo(col0)

icu_collate_xh(col0)

icu_collate_yi(col0)

icu_collate_yo(col0)

icu_collate_yue(col0)

icu_collate_yue_cn(col0)

icu_collate_zh(col0)

icu_collate_zh_cn(col0)

icu_collate_zh_hk(col0)

icu_collate_zh_mo(col0)

icu_collate_zh_sg(col0)

icu_collate_zh_tw(col0)

icu_collate_zu(col0)

icu_sort_key(col0, col1)

ilike_escape(string, like_specifier, escape_character)

Returns true if the string matches the like_specifier (see Pattern Matching) using case-insensitive matching. escape_character is used to search for wildcard characters in the string.

import_database(col0)

in_search_path(database_name, schema_name)

instr(haystack, needle)

Returns whether or not the database/schema are in the search path

is_histogram_other_bin(val)

Returns location of first occurrence of needle in haystack, counting from 1. Returns 0 if no match found

Whether or not the provided value is the histogram "other" bin (used for values not belonging to any provided bin)

isfinite(x)

Returns true if the floating point value is finite, false otherwise

isinf(x)

Returns true if the floating point value is infinite, false otherwise

isnan(x)

Returns true if the floating point value is not a number, false otherwise

isodow(ts)

Extract the isodow component from a date or timestamp

isoyear(ts)

Extract the isoyear component from a date or timestamp

jaccard(str1, str2)

The Jaccard similarity between two strings. Different case is considered different. Returns a number between 0 and 1

jaro_winkler_similarity(str1, str2)jaro_winkler_similarity(str1, str2, score_cutoff)

The Jaro similarity between two strings. Different case is considered different. Returns a number between 0 and 1

json_array_length(col0)json_array_length(col0, col1)

The Jaro-Winkler similarity between two strings. Different case is considered different. Returns a number between 0 and 1

json_contains(col0, col1)

json_deserialize_sql(col0)

json_each(col0)json_each(col0, col1)

json_execute_serialized_sql(col0)

Pragma Table

json_exists(col0, col1)

json_extract(col0, col1)

json_extract_path(col0, col1)

json_extract_path_text(col0, col1)

json_extract_string(col0, col1)

json_group_array(x)

json_group_object(n, v)

json_group_structure(x)

json_keys(col0)json_keys(col0, col1)

json_serialize_plan(col0)json_serialize_plan(col0, col1)json_serialize_plan(col0, col1, col2)json_serialize_plan(col0, col1, col2, col3)json_serialize_plan(col0, col1, col2, col3, col4)

json_merge_patch(…)

json_serialize_sql(col0)json_serialize_sql(col0, col1)json_serialize_sql(col0, col1, col2)json_serialize_sql(col0, col1, col2, col3)json_serialize_sql(col0, col1, col2, col3, col4)

json_structure(col0)

json_transform(col0, col1)

json_transform_strict(col0, col1)

json_tree(col0)json_tree(col0, col1)

json_type(col0)json_type(col0, col1)

json_valid(col0)

json_value(col0, col1)

julian(ts)

Extract the Julian Day number from a date or timestamp

kahan_sum(arg)

Calculates the sum using a more accurate floating point summation (Kahan Sum).

kurtosis(x)

Returns the excess kurtosis (Fisher’s definition) of all input values, with a bias correction according to the sample size

kurtosis_pop(x)

Returns the excess kurtosis (Fisher’s definition) of all input values, without bias correction

last(arg)

Returns the last value of a column. This function is affected by ordering.

last_day(ts)

Returns the last day of the month

lcase(string)

Converts string to lower case

lcm(x, y)

Computes the least common multiple of x and y

least(arg1, …)

least_common_multiple(x, y)

Returns the lowest value of the set of input parameters

Computes the least common multiple of x and y

left(string, count)

left_grapheme(string, count)

Extracts the left-most count characters

Extracts the left-most count grapheme clusters

len(string)

Number of characters in string.

length(string)

length_grapheme(string)

Number of characters in string.

levenshtein(str1, str2)

Number of grapheme clusters in string.

The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Different case is considered different

lgamma(x)

like_escape(string, like_specifier, escape_character)

Computes the log of the gamma function

Returns true if the string matches the like_specifier (see Pattern Matching) using case-sensitive matching. escape_character is used to search for wildcard characters in the string.

list(arg)

list_aggr(list, name, …)

Returns a LIST containing all the values of a column.

list_aggregate(list, name, …)

Executes the aggregate function name on the elements of list

list_apply(list, lambda)

Executes the aggregate function name on the elements of list

list_approx_count_distinct(l)

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details

Concatenates two lists.

list_concat(…)

list_contains(list, element)

Concatenates two lists.

list_cosine_distance(list1, list2)

Returns true if the list contains the element.

list_cosine_similarity(list1, list2)

Compute the cosine distance between two lists

Compute the cosine similarity between two lists

list_count(l)

list_distance(list1, list2)

Compute the distance between two lists

list_distinct(list)

list_dot_product(list1, list2)

Removes all duplicates and NULLs from a list. Does not preserve the original order

list_element(list, index)

Compute the inner product between two lists

Extract the indexth (1-based) value from the list.

list_entropy(l)

list_extract(list, index)

list_filter(list, lambda)

Extract the indexth (1-based) value from the list.

Constructs a list from those elements of the input list for which the lambda function returns true

list_first(l)

list_grade_up(list)list_grade_up(list, col1)list_grade_up(list, col1, col2)

list_has(list, element)

Returns the index of their sorted position.

Returns true if the list contains the element.

list_has_all(l1, l2)

Returns true if all elements of l2 are in l1. NULLs are ignored.

list_has_any(l1, l2)

Returns true if the lists have any element in common. NULLs are ignored.

list_histogram(l)

list_indexof(list, element)

list_inner_product(list1, list2)

Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.

Compute the inner product between two lists

list_intersect(l1, l2)

list_kurtosis(l)

list_negative_dot_product(list1, list2)

list_kurtosis_pop(l)

list_negative_inner_product(list1, list2)

Compute the negative inner product between two lists

Compute the negative inner product between two lists

list_pack(…)

list_position(list, element)

Create a LIST containing the argument values

list_reduce(list, lambda[)list_reduce(list, lambda[, initial])

Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.

list_resize(list, size[)list_resize(list, size[, value])

Resizes the list to contain size elements. Initializes new elements with value or NULL if value is not set.

list_reverse(l)

list_reverse_sort(list)list_reverse_sort(list, col1)

list_select(value_list, index_list)

Sorts the elements of the list in reverse order

list_slice(list, begin, end)list_slice(list, begin, end, step)

Returns a list based on the elements selected by the index_list.

list_sort(list)list_sort(list, col1)list_sort(list, col1, col2)

list_slice with added step feature.

Sorts the elements of the list

list_stddev_pop(l)

list_transform(list, lambda)

list_stddev_samp(l)

Returns a list that is the result of applying the lambda function to each element of the input list. See the Lambda Functions section for more details

list_unique(list)

Counts the unique elements of a list

list_value(…)

list_where(value_list, mask_list)

Create a LIST containing the argument values

Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list.

list_zip(…)

listagg(str)listagg(str, arg)

Concatenates the column string values with an optional separator.

ln(x)

Computes the natural logarithm of x

log(b)log(b, x)

Computes the logarithm of x to base b. b may be omitted, in which case the default 10

log10(x)

Computes the 10-log of x

log2(x)

Computes the 2-log of x

lower(string)

lpad(string, count, character)

Converts string to lower case

ltrim(string)ltrim(string, characters)

Pads the string with the character from the left until it has count characters

Removes any occurrences of any of the characters from the left side of the string

mad(x)

make_date(col0)make_date(date-struct)make_date(year, month, day)

Returns the median absolute deviation for the values within x. NULL values are ignored. Temporal types return a positive INTERVAL.

make_time(hour, minute, seconds)

The date for the given struct.

make_timestamp(year)make_timestamp(year, month, day, hour, minute, seconds)

The time for the given parts

make_timestamp_ns(nanos)

The timestamp for the given parts

make_timestamptz(col0)make_timestamptz(col0, col1, col2, col3, col4, col5)make_timestamptz(col0, col1, col2, col3, col4, col5, col6)

The timestamp for the given nanoseconds since epoch

map(…)

Creates a map from a set of keys and values

map_concat(…)

Returns a map created from merging the input maps, on key collision the value is taken from the last map with that key

map_contains(map, key)

map_contains_entry(map, key, value)

Checks if a map contains a given key.

map_contains_value(map, value)

map_entries(…)

map_extract(map, key, …)

Returns the map entries as a list of keys/values

map_extract_value(map, key, …)

Returns the value for a given key or NULL if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map’s keys else an error is returned

map_from_entries(…)

Returns a map created from the entries of the array

map_keys(…)

Returns the keys of a map as a list

map_values(…)

Returns the values of a map as a list

max(arg)max(arg, col1)

max_by(arg, val)max_by(arg, val, col2)

Returns the maximum value present in arg.

Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.

md5(value)

Returns the MD5 hash of the value as a string

md5_number(value)

md5_number_lower(param)

Returns the MD5 hash of the value as an INT128

md5_number_upper(param)

mean(x)

Calculates the average value for all tuples in x.

median(x)

Returns the middle value of the set. NULL values are ignored. For even value counts, interpolate-able types (numeric, date/time) return the average of the two middle values. Non-interpolate-able types (everything else) return the lower of the two middle values.

Extract the microsecond component from a date or timestamp

millennium(ts)

Extract the millennium component from a date or timestamp

millisecond(ts)

Extract the millisecond component from a date or timestamp

min(arg)min(arg, col1)

min_by(arg, val)min_by(arg, val, col2)

Returns the minimum value present in arg.

Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.

minute(ts)

Extract the minute component from a date or timestamp

mismatches(str1, str2)

The number of positions with different characters for 2 strings of equal length. Different case is considered different

Returns the most frequent value for the values within x. NULL values are ignored.

month(ts)

Extract the month component from a date or timestamp

monthname(ts)

The (English) name of the month

multiply(col0, col1)

nanosecond(tsns)

Extract the nanosecond component from a date or timestamp

nextafter(x, y)

nextval('sequence_name')

Returns the next floating point value after x in the direction of y

Return the following value of the sequence.

nfc_normalize(string)

normalized_interval(interval)

Converts string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.

not_ilike_escape(string, like_specifier, escape_character)

Normalizes an INTERVAL to an equivalent interval

not_like_escape(string, like_specifier, escape_character)

Returns false if the string matches the like_specifier (see Pattern Matching) using case-insensitive matching. escape_character is used to search for wildcard characters in the string.

Returns false if the string matches the like_specifier (see Pattern Matching) using case-sensitive matching. escape_character is used to search for wildcard characters in the string.

now()

Returns the current timestamp

nullif(a, b)

octet_length(bitstring)octet_length(blob)

Returns the number of bytes in the bitstring.

ord(str)

parquet_bloom_probe(col0, col1, col2)

Returns the unicode codepoint of the first character of the string

parquet_file_metadata(col0)

parquet_kv_metadata(col0)

parquet_metadata(col0)

parquet_scan(col0, named arguments)

parquet_schema(col0)

parse_dirname(string)parse_dirname(string, separator)

parse_dirpath(string)parse_dirpath(string, separator)

Returns the top-level directory name. separator options: system, both_slash (default), forward_slash, backslash

parse_duckdb_log_message(type, message)

Returns the head of the path similarly to Python's os.path.dirname. separator options: system, both_slash (default), forward_slash, backslash

parse_filename(string)parse_filename(string, trim_extension)parse_filename(string, trim_extension, separator)

Parse the message into the expected logical type

parse_path(string)parse_path(string, separator)

Returns the last component of the path similarly to Python's os.path.basename. If trim_extension is true, the file extension will be removed (it defaults to false). separator options: system, both_slash (default), forward_slash, backslash

Returns a list of the components (directories and filename) in the path similarly to Python's pathlib.PurePath::parts. separator options: system, both_slash (default), forward_slash, backslash

pg_timezone_names()

pi()

Returns the value of pi

platform()

position(haystack, needle)

Returns location of first occurrence of needle in haystack, counting from 1. Returns 0 if no match found

pow(x, y)

Computes x to the power of y

power(x, y)

Computes x to the power of y

pragma_collations()

pragma_database_size()

pragma_metadata_info()pragma_metadata_info(col0)

pragma_storage_info(col0)

pragma_table_info(col0)

pragma_user_agent()

Formats a string using printf syntax

product(arg)

quantile(x)quantile(x, pos)

Calculates the product of all tuples in arg.

Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact quantiles.

quantile_cont(x, pos)

quantile_disc(x)quantile_disc(x, pos)

Returns the interpolated quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles.

Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact quantiles.

quarter(ts)

Extract the quarter component from a date or timestamp

query(col0)

query_table(col0)query_table(col0, col1)

radians(x)

Converts degrees to radians

random()

range(start)range(start, stop)range(start, stop, step)range(col0)range(col0, col1)range(col0, col1, col2)

Returns a random number between 0 and 1

Scalar Table

Create a list of values between start and stop - the stop parameter is exclusive

read_blob(col0)

read_csv(col0, named arguments)

read_csv_auto(col0, named arguments)

read_json(col0, named arguments)

read_json_auto(col0, named arguments)

read_json_objects(col0, named arguments)

read_json_objects_auto(col0, named arguments)

read_ndjson(col0, named arguments)

read_ndjson_auto(col0, named arguments)

read_ndjson_objects(col0, named arguments)

read_parquet(col0, named arguments)

read_text(col0)

reduce(list, lambda[)reduce(list, lambda[, initial])

regexp_escape(string)

regexp_extract(string, pattern[)regexp_extract(string, pattern[, group = 0][)regexp_extract(string, pattern[, group = 0][, options])

Escapes special patterns to turn string into a regular expression similarly to Python's re.escape function.

regexp_extract_all(string, regex[)regexp_extract_all(string, regex[, group = 0][)regexp_extract_all(string, regex[, group = 0][, options])

If string contains the regexp pattern, returns the capturing group specified by optional parameter group. The group must be a constant value. If no group is given, it defaults to 0. A set of optional options can be set.

regexp_full_match(string, regex[)regexp_full_match(string, regex[, options])

Splits the string along the regex and extract all occurrences of group. A set of optional options can be set.

regexp_matches(string, pattern[)regexp_matches(string, pattern[, options])

Returns true if the entire string matches the regex. A set of optional options can be set.

regexp_replace(string, pattern, replacement[)regexp_replace(string, pattern, replacement[, options])

Returns true if string contains the regexp pattern, false otherwise. A set of optional options can be set.

regexp_split_to_array(string, regex)regexp_split_to_array(string, regex, col2)

If string contains the regexp pattern, replaces the matching part with replacement. A set of optional options can be set.

regexp_split_to_table(text, pattern)

Splits the string along the regex

regr_avgx(y, x)

Returns the average of the independent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.

regr_avgy(y, x)

Returns the average of the dependent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.

regr_count(y, x)

Returns the number of non-null number pairs in a group.

regr_intercept(y, x)

Returns the intercept of the univariate linear regression line for non-null pairs in a group.

regr_r2(y, x)

Returns the coefficient of determination for non-null pairs in a group.

regr_slope(y, x)

Returns the slope of the linear regression line for non-null pairs in a group.

Returns the population covariance of input values

regr_syy(y, x)

remap_struct(input, target_type, mapping, defaults)

repeat(string, count)repeat(col0, col1)

Map a struct to another struct type, potentially re-ordering, renaming and casting members and filling in defaults for missing values

Scalar Table

Repeats the string count number of times

repeat_row(…, named arguments)

replace(string, source, target)

reservoir_quantile(x, quantile)reservoir_quantile(x, quantile, sample_size)

Replaces any occurrences of the source with target in string

Gives the approximate quantile using reservoir sampling, the sample size is optional and uses 8192 as a default size.

reverse(string)

Reverses the string

right(string, count)

right_grapheme(string, count)

Extract the right-most count characters

round(x)round(x, precision)

Extracts the right-most count grapheme clusters

Rounds x to s decimal places

Create an unnamed STRUCT (tuple) containing the argument values.

row_to_json(…)

rpad(string, count, character)

rtrim(string)rtrim(string, characters)

Pads the string with the character from the right until it has count characters

Removes any occurrences of any of the characters from the right side of the string

second(ts)

Extract the second component from a date or timestamp

sem(x)

set_bit(bitstring, index, new_value)

Returns the standard error of the mean

Sets the nth bit in bitstring to newvalue; the first (leftmost) bit is indexed 0. Returns a new bitstring

setseed(col0)

Sets the seed to be used for the random function

sha1(value)

Returns the SHA1 hash of the value

sha256(value)

Returns the SHA256 hash of the value

show_tables_expanded()

sign(x)

Returns the sign of x as -1, 0 or 1

signbit(x)

Returns whether the signbit is set or not

sin(x)

Computes the sin of x

sinh(x)

Computes the hyperbolic sin of x

skewness(x)

sniff_csv(col0, named arguments)

Returns the skewness of all input values.

split(string, separator)

split_part(string, delimiter, position)

Splits the string along the separator

sql_auto_complete(col0)

sqrt(x)

starts_with(string, search_string)

Returns the square root of x

Returns true if string begins with search_string

stats(expression)

Returns a string with statistics about the expression. Expression can be a column, constant, or SQL expression

stddev(x)

Returns the sample standard deviation

stddev_pop(x)

Returns the population standard deviation.

stddev_samp(x)

Returns the sample standard deviation

storage_info(col0)

str_split(string, separator)

str_split_regex(string, regex)str_split_regex(string, regex, col2)

Splits the string along the separator

Splits the string along the regex

strftime(data, format)

string_agg(str)string_agg(str, arg)

Converts a date to a string according to the format string.

string_split(string, separator)

Concatenates the column string values with an optional separator.

string_split_regex(string, regex)string_split_regex(string, regex, col2)

Splits the string along the separator

string_to_array(string, separator)

Splits the string along the regex

Splits the string along the separator

strip_accents(string)

Strips accents from string.

strlen(string)

strpos(haystack, needle)

Number of bytes in string.

strptime(text, format)strptime(text, format-list)

Returns location of first occurrence of needle in haystack, counting from 1. Returns 0 if no match found

Converts the string text to timestamp applying the format strings in the list until one succeeds. Throws an error on failure. To return NULL on failure, use try_strptime.

struct_concat(…)

struct_extract(struct, 'entry')

Merge the multiple STRUCTs into a single STRUCT.

struct_extract_at(struct, 'entry')

Extract the named entry from the STRUCT.

Extract the entry from the STRUCT by position (starts at 1!).

struct_insert(…)

Adds field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s)

struct_pack(…)

substr(string, start)substr(string, start, length)

Create a STRUCT containing the argument values. The entry name will be the bound variable name.

substring(string, start)substring(string, start, length)

Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.

substring_grapheme(string, start)substring_grapheme(string, start, length)

Extract substring of length characters starting from character start. Note that a start value of 1 refers to the first character of the string.

subtract(col0)subtract(col0, col1)

Extract substring of length grapheme clusters starting from character start. Note that a start value of 1 refers to the first character of the string.

Calculates the sum value for all tuples in arg.

sum_no_overflow(arg)

Internal only. Calculates the sum value for all tuples in arg without overflow checks.

sumkahan(arg)

Calculates the sum using a more accurate floating point summation (Kahan Sum).

Computes the tan of x

tanh(x)

test_all_types(named arguments)

Computes the hyperbolic tan of x

test_vector_types(col0, …, named arguments)

time_bucket(bucket_width, timestamp)time_bucket(bucket_width, timestamp, origin)

timetz_byte_comparable(time_tz)

Truncate TIMESTAMPTZ by the specified interval bucket_width. Buckets are aligned relative to origin TIMESTAMPTZ. The origin defaults to 2000-01-03 00:00:00+00 for buckets that do not include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets

timezone(ts)timezone(ts, col1)

Converts a TIME WITH TIME ZONE to an integer sort key

Extract the timezone component from a date or timestamp

timezone_hour(ts)

Extract the timezone_hour component from a date or timestamp

timezone_minute(ts)

to_base(number, radix)to_base(number, radix, min_length)

Extract the timezone_minute component from a date or timestamp

Converts a value to a string in the given base radix, optionally padding with leading zeros to the minimum length

to_base64(blob)

Converts a blob to a base64 encoded string.

to_binary(value)

Converts the value to binary representation

to_centuries(integer)

Construct a century interval

to_days(integer)

Construct a day interval

to_decades(integer)

to_hex(blob)to_hex(string)to_hex(value)

Construct a decade interval

Converts blob to VARCHAR using hexadecimal encoding.

to_hours(integer)

Construct a hour interval

to_json(…)

to_microseconds(integer)

Construct a microsecond interval

to_millennia(integer)

to_milliseconds(double)

Construct a millenium interval

Construct a millisecond interval

to_minutes(integer)

Construct a minute interval

to_months(integer)

Construct a month interval

to_quarters(integer)

Construct a quarter interval

to_seconds(double)

Construct a second interval

to_timestamp(sec)

Converts secs since epoch to a timestamp with time zone

to_weeks(integer)

Construct a week interval

to_years(integer)

Construct a year interval

today()

transaction_timestamp()

translate(string, from, to)

Returns the current timestamp

trim(string)trim(string, characters)

Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted

Removes any occurrences of any of the characters from either side of the string

trunc(x)

Truncates the number

truncate_duckdb_logs()

try_strptime(text, format)