Functions
Explore SQL functions for cleaning text, working with dates, transforming lists, and reading files. Open a function to understand its inputs and results, see examples, and experiment with SQL in your browser.
-operandleft - right
left ->> right
x!
Factorial of x. Computes the product of the current integer and all integers below it
left !~~ right
left !~~* right
@x
Absolute value
list1 @> list2
Returns true if all elements of list2 are in list1. NULLs are ignored.
left * right
x ** y
Computes x to the power of y
left / right
left // right
left & right
Bitwise AND
list1 && list2
Returns true if the lists have any element in common. NULLs are ignored.
left % right
x ^ y
Computes x to the power of y
string ^@ search_string
Returns true if string begins with search_string.
++operandleft + right
list1 <-> list2
Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.
list1 <@ list2
Returns true if all elements of list2 are in list1. NULLs are ignored.
input << right
Bitwise shift left
list1 <=> list2
Computes the cosine distance between two same-sized lists.
input >> right
Bitwise shift right
left | right
Bitwise OR
arg1 || arg2
Concatenates two strings, lists, or blobs. Any NULL input results in NULL. See also concat(arg1, arg2, ...) and list_concat(list1, list2, ...).
~input
Bitwise NOT
left ~~ right
left ~~* right
left ~~~ right
abs(x)
Absolute value
acos(x)
Computes the arccosine of x
acosh(x)
Computes the inverse hyperbolic cos of x
add(…) add(col0) add(col0, col1)
add_parquet_key(col0, col1)
age(timestamp) age(timestamp, timestamp)
Subtract arguments, resulting in the time difference between the two timestamps
aggregate(list, function_name, …)
Executes the aggregate function 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(x))
Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.
approx_count_distinct(any)
Computes the approximate count of distinct elements using HyperLogLog.
approx_quantile(x, pos)
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)
Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.
arg_max(arg, val) arg_max(arg, val, col2)
Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
arg_max_null(arg, val)
Finds the row with the maximum val. Calculates the arg expression at that row.
arg_min(arg, val) arg_min(arg, val, col2)
Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.
arg_min_null(arg, val)
Finds the row with the minimum val. Calculates the arg expression at that row.
argmax(arg, val) argmax(arg, val, col2)
Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
argmin(arg, val) argmin(arg, val, col2)
Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.
array_agg(arg)
Returns a LIST containing all the values of a column.
array_aggr(list, function_name, …)
Executes the aggregate function function_name on the elements of list.
array_aggregate(list, function_name, …)
Executes the aggregate function function_name on the elements of list.
array_append(arr, el)
array_apply(list, lambda(x))
Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.
array_cat(…)
Concatenates lists. NULL inputs are skipped. See also operator ||.
array_concat(…)
Concatenates lists. NULL inputs are skipped. See also operator ||.
array_contains(list, element)
Returns true if the list contains the element.
array_cosine_distance(array1, array2)
Computes 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_cosine_similarity(array1, array2)
Computes 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_cross_product(array, array)
Computes the cross product of two arrays of size 3. The array elements can not be NULL.
array_distance(array1, array2)
Computes 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)
Removes all duplicates and NULL values from a list. Does not preserve the original order.
array_dot_product(array1, array2)
Computes 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_extract(col0, col1) array_extract(string, index) array_extract(struct, entry) array_extract(struct, index)
Extracts a single character from a string using a (1-based) index.
array_filter(list, lambda(x))
Constructs a list from those elements of the input list for which the lambda function returns true. DuckDB must be able to cast the lambda function's return type to BOOL. The return type of list_filter is the same as the input list's.
array_grade_up(list) array_grade_up(list, col1) array_grade_up(list, col1, col2)
Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
array_has(list, element)
Returns true if the list contains the element.
array_has_all(list1, list2)
Returns true if all elements of list2 are in list1. NULLs are ignored.
array_has_any(list1, list2)
Returns true if the lists have any element in common. NULLs are ignored.
array_indexof(list, element)
Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.
array_inner_product(array1, array2)
Computes 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_intersect(l1, l2)
array_length(list) array_length(list, dimension)
array_length for lists with dimensions other than 1 not implemented
array_negative_dot_product(array1, array2)
Computes 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_negative_inner_product(array1, array2)
Computes 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(x,y)) array_reduce(list, lambda(x,y), initial_value)
Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument.
array_resize(list, size[) array_resize(list, size[, value])
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)
Sorts the elements of the list in reverse order.
array_select(value_list, index_list)
Returns a list based on the elements selected by the index_list.
array_slice(list, begin, end) array_slice(list, begin, end, step)
list_slice with added step feature.
array_sort(list) array_sort(list, col1) array_sort(list, col1, col2)
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(x))
Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.
array_unique(list)
Counts the unique elements of a list.
array_value(…)
Creates an ARRAY containing the argument values.
array_where(value_list, mask_list)
Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list.
array_zip(…)
Zips n LISTs to a new LIST whose length will be that of the longest list. Its elements are structs of n elements from each list list_1, …, list_n, missing elements are replaced with NULL. If truncate is set, all lists are truncated to the smallest list length.
arrow_scan(col0, col1, col2)
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)
Calculates the average value for all tuples in x.
bar(x, min, max) bar(x, min, max, width)
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(string) bin(value)
Converts the string to binary representation.
bit_and(arg)
Returns the bitwise AND of all bits in a given expression.
bit_count(x)
Returns the number of bits that are set
bit_length(bit) bit_length(string)
Returns the bit-length of the bit argument.
bit_or(arg)
Returns the bitwise OR of all bits in a given expression.
bit_position(substring, bitstring)
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)
Returns the bitwise XOR of all bits in a given expression.
bitstring(bitstring, length)
Pads the bitstring until the specified length
bitstring_agg(arg) bitstring_agg(arg, col1, col2)
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)
Returns TRUE if any input value is TRUE, otherwise FALSE.
can_cast_implicitly(source_type, target_type)
Whether or not we can implicitly cast from the source type to the other type
cardinality(map, …)
Returns the size of the map (or the number of entries in the map)
cast_to_type(param, type)
Casts the first argument to the type of the second argument
cbrt(x)
Returns the cube root of x
ceil(x)
Rounds the number up
ceiling(x)
Rounds the number up
century(ts)
Extract the century component from a date or timestamp
char_length(bit) char_length(list) char_length(string)
Returns the bit-length of the bit argument.
character_length(bit) character_length(list) character_length(string)
Returns the bit-length of the bit argument.
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(value, …)
Concatenates multiple strings or lists. NULL inputs are skipped. See also operator ||.
concat_ws(separator, string, …)
Concatenates many strings, separated by separator. NULL inputs are skipped.
constant_or_null(arg1, arg2, …)
If arg2 is NULL, return NULL. Otherwise, return arg1.
contains(col0, col1) contains(string, search_string)
Returns true if search_string is found within string.
copy_database(col0, col1)
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
count_star()
countif(arg)
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)
Returns the sample covariance for non-NULL pairs in a group.
create_sort_key(parameters..., …)
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()
Get the current query_id
current_role()
current_schema()
Returns the name of the currently active schema. Default is main
current_schemas(include_implicit)
Returns list of schemas. Pass a parameter of True to include implicit schemas
current_setting(setting_name)
Returns the current value of the configuration setting
current_transaction_id()
Get the current global transaction_id
current_user()
currval('sequence_name')
Return the current value of the sequence. Note that nextval must be called at least once prior to calling currval.
damerau_levenshtein(s1, s2)
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. Characters of different cases (e.g., a and A) are considered different.
database_list()
database_size()
date_add(date, interval)
date_diff(part, startdate, enddate)
The number of partition boundaries between the timestamps
date_part(ts, col1)
Get subfield (equivalent to extract)
date_sub(part, startdate, enddate)
The number of complete partitions between the timestamps
date_trunc(part, timestamp)
Truncate to specified precision
datediff(part, startdate, enddate)
The number of partition boundaries between the timestamps
datepart(ts, col1)
Get subfield (equivalent to extract)
datesub(part, startdate, enddate)
The number of complete partitions between the timestamps
datetrunc(part, timestamp)
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)
Converts radians to degrees
disable_checkpoint_on_shutdown()
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()
divide(col0, col1)
duckdb_approx_database_count()
duckdb_columns()
duckdb_connection_count()
duckdb_constraints()
duckdb_databases()
duckdb_dependencies()
duckdb_extensions()
duckdb_external_file_cache()
duckdb_functions()
duckdb_indexes()
duckdb_keywords()
duckdb_log_contexts()
duckdb_logs(named arguments)
duckdb_logs_parsed(log_type)
duckdb_memory()
duckdb_optimizers()
duckdb_prepared_statements()
duckdb_schemas()
duckdb_secret_types()
duckdb_secrets(named arguments)
duckdb_sequences()
duckdb_settings()
duckdb_table_sample(col0)
duckdb_tables()
duckdb_temporary_files()
duckdb_types()
duckdb_variables()
duckdb_views()
editdist3(s1, s2)
The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Characters of different cases (e.g., a and A) are considered different.
element_at(map, key)
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_checkpoint_on_shutdown()
enable_logging(…, named arguments)
enable_object_cache()
enable_optimizer()
enable_print_progress_bar()
enable_profile()
enable_profiling()
enable_progress_bar()
enable_verification()
encode(string)
Converts the string to BLOB. Converts UTF-8 characters into literal encoding.
ends_with(string, search_string)
Returns true if string ends with search_string.
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)
Returns all values of the input enum type as an array
enum_range_boundary(start, end)
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)
Extract the epoch component in microseconds from a temporal type
equi_width_bins(min, max, bin_count, nice_rounding)
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(x))
Constructs a list from those elements of the input list for which the lambda function returns true. DuckDB must be able to cast the lambda function's return type to BOOL. The return type of list_filter is the same as the input list's.
finalize(col0)
first(arg)
Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.
flatten(nested_list)
Flattens a nested list by one level.
floor(x)
Rounds the number down
fmod(x, y)
force_checkpoint() force_checkpoint(col0)
format(format, …)
Formats a string using the fmt syntax.
format_bytes(integer)
Converts integer to a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).
formatReadableDecimalSize(integer)
Converts integer to a human-readable representation using units based on powers of 10 (KB, MB, GB, etc.).
formatReadableSize(integer)
Converts integer to a human-readable representation using units based on powers of 2 (KiB, MiB, GiB, etc.).
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).
functions()
gamma(x)
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()
Returns a random UUID v4 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687
generate_series(start) generate_series(start, stop) generate_series(start, stop, step) generate_series(col0) generate_series(col0, col1) generate_series(col0, col1, col2)
Creates a list of values between start and stop - the stop parameter is inclusive.
generate_subscripts(arr, dim)
geomean(x)
geometric_mean(x)
get_bit(bitstring, index)
Extracts the nth bit from bitstring; the first (leftmost) bit is indexed 0
get_block_size(db_name)
get_current_time()
get_current_timestamp()
Returns the current timestamp
getenv(col0)
getvariable(col0)
glob(col0)
grade_up(list) grade_up(list, col1) grade_up(list, col1, col2)
Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
greatest(arg1, …)
Returns the largest value. For strings lexicographical ordering is used. Note that lowercase characters are considered “larger” than uppercase characters and collations are not supported.
greatest_common_divisor(x, y)
Computes the greatest common divisor of x and y
group_concat(str) group_concat(str, arg)
Concatenates the column string values with an optional separator.
hamming(s1, s2)
The Hamming distance between to strings, i.e., the number of positions with different characters for two strings of equal length. Strings must be of equal length. Characters of different cases (e.g., a and A) are considered different.
hash(value, …)
Returns a UBIGINT with the hash of the value. Note that this is not a cryptographic hash.
hex(blob) hex(string) hex(value)
Converts blob to VARCHAR using hexadecimal encoding.
histogram(arg) histogram(arg, col1) histogram(source, col_name, bin_count, technique)
Returns a LIST of STRUCTs with the fields bucket and count.
histogram_exact(arg, bins)
Returns a LIST of STRUCTs with the fields bucket and count matching the buckets exactly.
histogram_values(source, col_name, bin_count, technique)
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)
Returns whether or not the database/schema are in the search path
instr(string, search_string)
Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
is_histogram_other_bin(val)
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(s1, s2)
The Jaccard similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1.
jaro_similarity(s1, s2) jaro_similarity(s1, s2, score_cutoff)
The Jaro similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.
jaro_winkler_similarity(s1, s2) jaro_winkler_similarity(s1, s2, score_cutoff)
The Jaro-Winkler similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.
json(x)
json_array(…)
json_array_length(col0) json_array_length(col0, col1)
json_contains(col0, col1)
json_deserialize_sql(col0)
json_each(col0) json_each(col0, col1)
json_execute_serialized_sql(col0)
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_merge_patch(col0, col1, …)
json_object(…)
json_pretty(col0)
json_quote(…)
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_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, …)
Returns the smallest value. For strings lexicographical ordering is used. Note that uppercase characters are considered “smaller” than lowercase characters, and collations are not supported.
least_common_multiple(x, y)
Computes the least common multiple of x and y
left(string, count)
Extracts the left-most count characters.
left_grapheme(string, count)
Extracts the left-most count grapheme clusters.
len(bit) len(list) len(string)
Returns the bit-length of the bit argument.
length(bit) length(list) length(string)
Returns the bit-length of the bit argument.
length_grapheme(string)
Number of grapheme clusters in string.
levenshtein(s1, s2)
The minimum number of single-character edits (insertions, deletions or substitutions) required to change one string to the other. Characters of different cases (e.g., a and A) are considered different.
lgamma(x)
Computes the log of the gamma function
like_escape(string, like_specifier, escape_character)
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)
Returns a LIST containing all the values of a column.
list_aggr(list, function_name, …)
Executes the aggregate function function_name on the elements of list.
list_aggregate(list, function_name, …)
Executes the aggregate function function_name on the elements of list.
list_any_value(l)
list_append(l, e)
list_apply(list, lambda(x))
Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.
list_approx_count_distinct(l)
list_avg(l)
list_bit_and(l)
list_bit_or(l)
list_bit_xor(l)
list_bool_and(l)
list_bool_or(l)
list_cat(…)
Concatenates lists. NULL inputs are skipped. See also operator ||.
list_concat(…)
Concatenates lists. NULL inputs are skipped. See also operator ||.
list_contains(list, element)
Returns true if the list contains the element.
list_cosine_distance(list1, list2)
Computes the cosine distance between two same-sized lists.
list_cosine_similarity(list1, list2)
Computes the cosine similarity between two same-sized lists.
list_count(l)
list_distance(list1, list2)
Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.
list_distinct(list)
Removes all duplicates and NULL values from a list. Does not preserve the original order.
list_dot_product(list1, list2)
Computes the inner product between two same-sized lists.
list_element(list, index)
Extract the indexth (1-based) value from the list.
list_entropy(l)
list_extract(list, index)
Extract the indexth (1-based) value from the list.
list_filter(list, lambda(x))
Constructs a list from those elements of the input list for which the lambda function returns true. DuckDB must be able to cast the lambda function's return type to BOOL. The return type of list_filter is the same as the input list's.
list_first(l)
list_grade_up(list) list_grade_up(list, col1) list_grade_up(list, col1, col2)
Works like list_sort, but the results are the indexes that correspond to the position in the original list instead of the actual values.
list_has(list, element)
Returns true if the list contains the element.
list_has_all(list1, list2)
Returns true if all elements of list2 are in list1. NULLs are ignored.
list_has_any(list1, list2)
Returns true if the lists have any element in common. NULLs are ignored.
list_histogram(l)
list_indexof(list, element)
Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.
list_inner_product(list1, list2)
Computes the inner product between two same-sized lists.
list_intersect(l1, l2)
list_kurtosis(l)
list_kurtosis_pop(l)
list_last(l)
list_mad(l)
list_max(l)
list_median(l)
list_min(l)
list_mode(l)
list_negative_dot_product(list1, list2)
Computes the negative inner product between two same-sized lists.
list_negative_inner_product(list1, list2)
Computes the negative inner product between two same-sized lists.
list_pack() list_pack(any, …)
Creates a LIST containing the argument values.
list_position(list, element)
Returns the index of the element if the list contains the element. If the element is not found, it returns NULL.
list_prepend(e, l)
list_product(l)
list_reduce(list, lambda(x,y)) list_reduce(list, lambda(x,y), initial_value)
Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument.
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)
Sorts the elements of the list in reverse order.
list_select(value_list, index_list)
Returns a list based on the elements selected by the index_list.
list_sem(l)
list_skewness(l)
list_slice(list, begin, end) list_slice(list, begin, end, step)
list_slice with added step feature.
list_sort(list) list_sort(list, col1) list_sort(list, col1, col2)
Sorts the elements of the list.
list_stddev_pop(l)
list_stddev_samp(l)
list_string_agg(l)
list_sum(l)
list_transform(list, lambda(x))
Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function.
list_unique(list)
Counts the unique elements of a list.
list_value() list_value(any, …)
Creates a LIST containing the argument values.
list_var_pop(l)
list_var_samp(l)
list_where(value_list, mask_list)
Returns a list with the BOOLEANs in mask_list applied as a mask to the value_list.
list_zip(…)
Zips n LISTs to a new LIST whose length will be that of the longest list. Its elements are structs of n elements from each list list_1, …, list_n, missing elements are replaced with NULL. If truncate is set, all lists are truncated to the smallest list length.
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)
Converts string to lower case.
lpad(string, count, character)
Pads the string with the character on the left until it has count characters. Truncates the string on the right if it has more than count characters.
ltrim(string) ltrim(string, characters)
Removes any occurrences of any of the characters from the left side of the string. characters defaults to space.
mad(x)
Returns the median absolute deviation for the values within x. NULL values are ignored. Temporal types return a positive INTERVAL.
make_date(col0) make_date(date-struct) make_date(year, month, day)
The date for the given struct.
make_time(hour, minute, seconds)
The time for the given parts
make_timestamp(year) make_timestamp(year, month, day, hour, minute, seconds)
The timestamp for the given parts
make_timestamp_ms(nanos)
The timestamp for the given microseconds since the epoch
make_timestamp_ns(nanos)
The timestamp for the given nanoseconds since epoch
make_timestamptz(col0) make_timestamptz(col0, col1, col2, col3, col4, col5) make_timestamptz(col0, col1, col2, col3, col4, col5, col6)
map()map(keys, values)
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)
Checks if a map contains a given key.
map_contains_entry(map, key, value)
map_contains_value(map, value)
map_entries(map)
Returns the map entries as a list of keys/values
map_extract(map, key)
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
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(map)
Returns a map created from the entries of the array
map_keys(map)
Returns the keys of a map as a list
map_values(map)
Returns the values of a map as a list
max(arg) max(arg, col1)
Returns the maximum value present in arg.
max_by(arg, val) max_by(arg, val, col2)
Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
md5(blob) md5(string)
Returns the MD5 hash of the blob as a VARCHAR.
md5_number(blob) md5_number(string)
Returns the MD5 hash of the blob as a HUGEINT.
md5_number_lower(param)
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.
metadata_info()
microsecond(ts)
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)
Returns the minimum value present in arg.
min_by(arg, val) min_by(arg, val, col2)
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(s1, s2)
The Hamming distance between to strings, i.e., the number of positions with different characters for two strings of equal length. Strings must be of equal length. Characters of different cases (e.g., a and A) are considered different.
mod(col0, col1)
mode(x)
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)
Returns the next floating point value after x in the direction of y
nextval('sequence_name')
Return the following value of the sequence.
nfc_normalize(string)
Converts string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.
normalized_interval(interval)
Normalizes an INTERVAL to an equivalent interval
not_ilike_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.
not_like_escape(string, like_specifier, escape_character)
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(string)
Returns an INTEGER representing the unicode codepoint of the first character in the string.
parquet_bloom_probe(col0, col1, col2)
parquet_file_metadata(col0)
parquet_kv_metadata(col0)
parquet_metadata(col0)
parquet_scan(col0, named arguments)
parquet_schema(col0)
parse_dirname(path) parse_dirname(path, separator)
Returns the top-level directory name from the given path. separator options: system, both_slash (default), forward_slash, backslash.
parse_dirpath(path) parse_dirpath(path, separator)
Returns the head of the path (the pathname until the last slash) similarly to Python's os.path.dirname. separator options: system, both_slash (default), forward_slash, backslash.
parse_duckdb_log_message(type, message)
Parse the message into the expected logical type
parse_filename(string) parse_filename(string, trim_extension) parse_filename(string, trim_extension, separator)
Returns the last component of the path similarly to Python's os.path.basename function. If trim_extension is true, the file extension will be removed (defaults to false). separator options: system, both_slash (default), forward_slash, backslash.
parse_path(path) parse_path(path, separator)
Returns a list of the components (directories and filename) in the path similarly to Python's pathlib.parts function. separator options: system, both_slash (default), forward_slash, backslash.
pg_timezone_names()
pi()
Returns the value of pi
platform()
position(string, search_string)
Returns location of first occurrence of search_string in string, 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_platform()
pragma_show(col0)
pragma_storage_info(col0)
pragma_table_info(col0)
pragma_user_agent()
pragma_version()
prefix(string, search_string)
Returns true if string starts with search_string.
printf(format, …)
Formats a string using printf syntax.
product(arg)
Calculates the product of all tuples in arg.
quantile(x) quantile(x, pos)
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)
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.
quantile_disc(x) quantile_disc(x, pos)
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()
Returns a random number between 0 and 1
range(start) range(start, stop) range(start, stop, step) range(col0) range(col0, col1) range(col0, col1, col2)
Creates 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(x,y)) reduce(list, lambda(x,y), initial_value)
Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument.
regexp_escape(string)
Escapes special patterns to turn string into a regular expression similarly to Python's re.escape function.
regexp_extract(string, regex) regexp_extract(string, regex, group) regexp_extract(string, regex, name_list) regexp_extract(string, regex, group, options) regexp_extract(string, regex, name_list, options)
If string contains the regex pattern, returns the capturing group specified by optional parameter group; otherwise, returns the empty string. The group must be a constant value. If no group is given, it defaults to 0. A set of optional regex options can be set.
regexp_extract_all(string, regex) regexp_extract_all(string, regex, group) regexp_extract_all(string, regex, group, options)
Finds non-overlapping occurrences of the regex in the string and returns the corresponding values of the capturing group. A set of optional regex options can be set.
regexp_full_match(string, regex) regexp_full_match(string, regex, col2)
Returns true if the entire string matches the regex. A set of optional regex options can be set.
regexp_matches(string, regex) regexp_matches(string, regex, options)
Returns true if string contains the regex, false otherwise. A set of optional regex options can be set.
regexp_replace(string, regex, replacement) regexp_replace(string, regex, replacement, options)
If string contains the regex, replaces the matching part with replacement. A set of optional regex options can be set.
regexp_split_to_array(string, regex) regexp_split_to_array(string, regex, options)
Splits the string along the regex. A set of optional regex options can be set.
regexp_split_to_table(text, pattern)
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.
regr_sxx(y, x)
regr_sxy(y, x)
Returns the population covariance of input values
regr_syy(y, x)
remap_struct(input, target_type, mapping, defaults)
Map a struct to another struct type, potentially re-ordering, renaming and casting members and filling in defaults for missing values
repeat(blob, count) repeat(col0, col1) repeat(string, count)
Repeats the blob count number of times.
repeat_row(…, named arguments)
replace(string, source, target)
Replaces any occurrences of the source with target in string.
replace_type(param, type1, type2)
Casts all fields of type1 to type2
reservoir_quantile(x, quantile) reservoir_quantile(x, quantile, sample_size)
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)
Extract the right-most count characters.
right_grapheme(string, count)
Extracts the right-most count grapheme clusters.
round(x) round(x, precision)
Rounds x to s decimal places
round_even(x, n)
roundbankers(x, n)
row(…)
Create an unnamed STRUCT (tuple) containing the argument values.
row_to_json(…)
rpad(string, count, character)
Pads the string with the character on the right until it has count characters. Truncates the string on the right if it has more than count characters.
rtrim(string) rtrim(string, characters)
Removes any occurrences of any of the characters from the right side of the string. characters defaults to space.
second(ts)
Extract the second component from a date or timestamp
sem(x)
Returns the standard error of the mean
seq_scan()
session_user()
set_bit(bitstring, index, new_value)
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(blob) sha1(value)
Returns a VARCHAR with the SHA-1 hash of the blob.
sha256(blob) sha256(value)
Returns a VARCHAR with the SHA-256 hash of the blob.
show(col0)
show_databases()
show_tables()
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)
Returns the skewness of all input values.
sniff_csv(col0, named arguments)
split(string, separator)
Splits the string along the separator.
split_part(string, delimiter, position)
sql_auto_complete(col0)
sqrt(x)
Returns the square root of x
starts_with(string, search_string)
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)
Splits the string along the separator.
str_split_regex(string, regex) str_split_regex(string, regex, options)
Splits the string along the regex. A set of optional regex options can be set.
strftime(data, format)
Converts a date to a string according to the format string.
string_agg(str) string_agg(str, arg)
Concatenates the column string values with an optional separator.
string_split(string, separator)
Splits the string along the separator.
string_split_regex(string, regex) string_split_regex(string, regex, options)
Splits the string along the regex. A set of optional regex options can be set.
string_to_array(string, separator)
Splits the string along the separator.
strip_accents(string)
Strips accents from string.
strlen(string)
Number of bytes in string.
strpos(string, search_string)
Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
strptime(text, format) strptime(text, format-list)
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(…)
Merge the multiple STRUCTs into a single STRUCT.
struct_contains(struct, 'entry')
Check if an unnamed STRUCT contains the value.
struct_extract(struct, 'entry')
Extract the named entry from the STRUCT.
struct_extract_at(struct, 'entry')
Extract the entry from the STRUCT by position (starts at 1!).
struct_has(struct, 'entry')
Check if an unnamed STRUCT contains the value.
struct_indexof(struct, 'entry')
Get the position of the entry in an unnamed STRUCT, starting 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(…)
Create a STRUCT containing the argument values. The entry name will be the bound variable name.
struct_position(struct, 'entry')
Get the position of the entry in an unnamed STRUCT, starting at 1.
struct_update(…)
Changes field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s)
substr(string, start) substr(string, start, length)
Extracts substring starting from character start up to the end of the string. If optional argument length is set, extracts a substring of length characters instead. Note that a start value of 1 refers to the first character of the string.
substring(string, start) substring(string, start, length)
Extracts substring starting from character start up to the end of the string. If optional argument length is set, extracts a substring of length characters instead. Note that a start value of 1 refers to the first character of the string.
substring_grapheme(string, start) substring_grapheme(string, start, length)
Extracts substring starting from grapheme clusters start up to the end of the string. If optional argument length is set, extracts a substring of length grapheme clusters instead. Note that a start value of 1 refers to the first character of the string.
subtract(col0) subtract(col0, col1)
suffix(string, search_string)
Returns true if string ends with search_string.
sum(arg)
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).
summary(col0)
table_info(col0)
tan(x)
Computes the tan of x
tanh(x)
Computes the hyperbolic tan of x
test_all_types(named arguments)
test_vector_types(col0, …, named arguments)
time_bucket(bucket_width, timestamp) time_bucket(bucket_width, timestamp, origin)
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
timetz_byte_comparable(time_tz)
Converts a TIME WITH TIME ZONE to an integer sort key
timezone(ts) timezone(ts, col1)
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)
Extract the timezone_minute component from a date or timestamp
to_base(number, radix) to_base(number, radix, min_length)
Converts number to a string in the given base radix, optionally padding with leading zeros to min_length.
to_base64(blob)
Converts a blob to a base64 encoded string.
to_binary(string) to_binary(value)
Converts the string to binary representation.
to_centuries(integer)
Construct a century interval
to_days(integer)
Construct a day interval
to_decades(integer)
Construct a decade interval
to_hex(blob) to_hex(string) to_hex(value)
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)
Construct a millenium interval
to_milliseconds(double)
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()
Returns the current timestamp
translate(string, from, to)
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.
trim(string) trim(string, characters)
Removes any occurrences of any of the characters from either side of the string. characters defaults to space.
trunc(x) trunc(x, col1)
Truncates the number
truncate_duckdb_logs()
try_strptime(text, format)
Converts the string text to timestamp according to the format string. Returns NULL on failure.
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
typeof(expression)
Returns the name of the data type of the result of the expression
ucase(string)
Converts string to upper case.
unbin(value)
Converts a value from binary representation to a blob.
unhex(value)
Converts a value from hexadecimal representation to a blob.
unicode(string)
Returns an INTEGER representing the unicode codepoint of the first character in the string.
union_extract(union, tag)
Extract the value with the named tags from the union. NULL if the tag is not currently selected
union_tag(union)
Retrieve the currently selected tag of the union as an ENUM
union_value(…)
Create a single member UNION containing the argument value. The tag of the value will be the bound variable name
unnest(col0)
unpivot_list(…)
Identical to list_value, but generated as part of unpivot for better error messages.
upper(string)
Converts string to upper case.
url_decode(string)
Decodes a URL from a representation using Percent-Encoding.
url_encode(string)
Encodes a URL to a representation using Percent-Encoding.
user()
user_agent()
uuid()
Returns a random UUID v4 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687
uuid_extract_timestamp(uuid)
Extract the timestamp for the given UUID v7.
uuid_extract_version(uuid)
Extract a version for the given UUID.
uuidv4()
Returns a random UUIDv4 similar to this: eeccb8c5-9943-b2bb-bb5e-222f4e14b687
uuidv7()
Returns a random UUID v7 similar to this: 019482e4-1441-7aad-8127-eec99573b0a0
var_pop(x)
Returns the population variance.
var_samp(x)
Returns the sample variance of all input values.
variance(x)
Returns the sample variance of all input values.
variant_extract(col0, col1)
variant_typeof(input_variant)
Returns the internal type of the input_variant.
vector_type(col)
Returns the VectorType of a given column
verify_external()
verify_fetch_row()
verify_parallelism()
verify_serializer()
version()
Returns the currently active version of DuckDB in this format: v0.3.2
wavg(value, weight)
week(ts)
Extract the week component from a date or timestamp
weekday(ts)
Extract the weekday component from a date or timestamp
weekofyear(ts)
Extract the weekofyear component from a date or timestamp
weighted_avg(value, weight)
which_secret(col0, col1)
write_log(string, …)
Writes to the logger
xor(left, right)
Bitwise XOR
year(ts)
Extract the year component from a date or timestamp
yearweek(ts)
Extract the yearweek component from a date or timestamp
__internal_compress_integral_ubigint(col0, col1)
__internal_compress_integral_uinteger(col0, col1)
__internal_compress_integral_usmallint(col0, col1)
__internal_compress_integral_utinyint(col0, col1)
__internal_compress_string_hugeint(col0)
__internal_compress_string_ubigint(col0)
__internal_compress_string_uhugeint(col0)
__internal_compress_string_uinteger(col0)
__internal_compress_string_usmallint(col0)
__internal_compress_string_utinyint(col0)
__internal_decompress_integral_bigint(col0, col1)
__internal_decompress_integral_hugeint(col0, col1)
__internal_decompress_integral_integer(col0, col1)
__internal_decompress_integral_smallint(col0, col1)
__internal_decompress_integral_ubigint(col0, col1)
__internal_decompress_integral_uhugeint(col0, col1)
__internal_decompress_integral_uinteger(col0, col1)
__internal_decompress_integral_usmallint(col0, col1)
__internal_decompress_string(col0)
No functions found.
Try a shorter name, a data type like VARCHAR, or clear your filters.