Haybarn WASM 2.0.0-rc12 SQL function catalog
Browse 1,119 functions captured from Haybarn WASM 2.0.0-rc12. Filter by name, category, or type, and inspect arguments, return types, and release availability.
-xleft - right
json ->> indexjson ->> path
x!
Factorial of x. Computes the product of the current integer and all integers below it
left != right
Returns whether left is not equal to right.
string !~~ pattern
string !~~* pattern
@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
Use the // operator for integer division. With integer operands, the fractional part is discarded rather than returned as a decimal.
left & right
Bitwise AND
geom1 && geom2list1 && list2
Returns true if the geometries bounding boxes intersect
left % right
x ^ y
Computes x to the power of y
string ^@ search_string
Returns true if string begins with search_string.
++xleft + right
left < right
Returns whether left is less than 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 << shift
Bitwise shift left
left <= right
Returns whether left is less than or equal to right.
list1 <=> list2
Computes the cosine distance between two same-sized lists.
left = right
Returns whether left is equal to right.
left > right
Returns whether left is greater than right.
left >= right
Returns whether left is greater than or equal to right.
input >> shift
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
string ~~ pattern
string ~~* pattern
string ~~~ pattern
abs(x)
Absolute value
acos(x)
Computes the arccosine of x
acosh(x)
Computes the inverse hyperbolic cos of x
add(…) add(x) add(left, right)
add_parquet_key(key_name, key)
age(timestamp) age(timestamp1, timestamp2)
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.
ago(i)
alias(expr)
Returns the name of a given expression
any_value(arg)
Returns the first non-NULL value from arg. This function is affected by ordering.
apply(list, lambda)
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, N)
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_max_nulls_last(arg, val) arg_max_nulls_last(arg, val, N)
Finds the rows with N maximum vals, including nulls. Calculates the arg expression at that row.
arg_min(arg, val) arg_min(arg, val, N)
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.
arg_min_nulls_last(arg, val) arg_min_nulls_last(arg, val, N)
Finds the rows with N minimum vals, including nulls. Calculates the arg expression at that row.
argmax(arg, val) argmax(arg, val, N)
Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
argmin(arg, val) argmin(arg, val, N)
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)
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(array1, array2)
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(list, index) array_extract(string, index) array_extract(struct, entry) array_extract(tuple, index)
Extracts a single character from a string using a (1-based) index.
array_filter(list, lambda)
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, sort_order) array_grade_up(list, sort_order, null_order)
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(list1, list2)
Returns a list containing the distinct elements that are present in both list1 and list2.
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) array_reduce(list, lambda, 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, null_order)
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, sort_order) array_sort(list, sort_order, null_order)
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. 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_kv_metadata(col0)
arrow_scan()
arrow_scan_dumb()
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
assert_true(condition) assert_true(condition, message)
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.
binom(n, k)
Returns binom(n,k)
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, min, max)
Returns a bitstring with bits set for each distinct value.
bitstring_byte_comparable(bitstring)
Converts a BIT to a binary comparable sort key
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.
checkpoint()checkpoint(database)
chr(code_point)
Returns a character which is corresponding the ASCII code value or Unicode code point.
collate_aa(str)
collate_af(str)
collate_am(str)
collate_ar(str)
collate_ar_sa(str)
collate_as(str)
collate_az(str)
collate_bal(str)
collate_be(str)
collate_bg(str)
collate_blo(str)
collate_bn(str)
collate_bo(str)
collate_br(str)
collate_bs(str)
collate_ca(str)
collate_ceb(str)
collate_chr(str)
collate_cs(str)
collate_cu(str)
collate_cy(str)
collate_da(str)
collate_de(str)
collate_de_at(str)
collate_dsb(str)
collate_dz(str)
collate_ee(str)
collate_el(str)
collate_en(str)
collate_en_us(str)
collate_eo(str)
collate_es(str)
collate_et(str)
collate_fa(str)
collate_fa_af(str)
collate_ff(str)
collate_fi(str)
collate_fil(str)
collate_fo(str)
collate_fr(str)
collate_fr_ca(str)
collate_fy(str)
collate_ga(str)
collate_gl(str)
collate_gu(str)
collate_ha(str)
collate_haw(str)
collate_he(str)
collate_he_il(str)
collate_hi(str)
collate_hr(str)
collate_hsb(str)
collate_hu(str)
collate_hy(str)
collate_id(str)
collate_id_id(str)
collate_ig(str)
collate_is(str)
collate_it(str)
collate_ja(str)
collate_ka(str)
collate_kk(str)
collate_kl(str)
collate_km(str)
collate_kn(str)
collate_ko(str)
collate_kok(str)
collate_ku(str)
collate_ky(str)
collate_lb(str)
collate_lij(str)
collate_lkt(str)
collate_ln(str)
collate_lo(str)
collate_lt(str)
collate_lv(str)
collate_mk(str)
collate_ml(str)
collate_mn(str)
collate_mr(str)
collate_ms(str)
collate_mt(str)
collate_my(str)
collate_nb(str)
collate_nb_no(str)
collate_ne(str)
collate_nl(str)
collate_nn(str)
collate_no(str)
collate_noaccent(str)
collate_nso(str)
collate_om(str)
collate_or(str)
collate_pa(str)
collate_pa_in(str)
collate_pl(str)
collate_ps(str)
collate_pt(str)
collate_ro(str)
collate_ru(str)
collate_sa(str)
collate_se(str)
collate_sgs(str)
collate_si(str)
collate_sk(str)
collate_sl(str)
collate_smn(str)
collate_sq(str)
collate_sr(str)
collate_sr_ba(str)
collate_sr_me(str)
collate_sr_rs(str)
collate_ssy(str)
collate_st(str)
collate_sv(str)
collate_sw(str)
collate_ta(str)
collate_te(str)
collate_th(str)
collate_tk(str)
collate_tn(str)
collate_to(str)
collate_tr(str)
collate_ug(str)
collate_uk(str)
collate_ur(str)
collate_uz(str)
collate_vi(str)
collate_vo(str)
collate_wae(str)
collate_wo(str)
collate_xh(str)
collate_yi(str)
collate_yo(str)
collate_zh(str)
collate_zh_cn(str)
collate_zh_hk(str)
collate_zh_mo(str)
collate_zh_sg(str)
collate_zh_tw(str)
collate_zu(str)
collations()
combine(state1, state2)
combine_aggr(arg) combine_aggr(arg, multiplicities)
Combines exported aggregate states
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(list, element) contains(map, key) contains(string, search_string) contains(struct, entry)
Returns true if search_string is found within string.
copy_database(from_database, to_database)
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_external_resource(type, named arguments)
create_sort_key(key1, …)
Constructs a binary-comparable sort key based on a set of input parameters and sort qualifiers
cume_dist()
The number of partition rows preceding or peer with current row / total partition rows.
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(part_list, ts) date_part(part, ts)
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(part_list, ts) datepart(part, ts)
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
days_in_month(date)
decade(ts)
Extract the decade component from a date or timestamp
decimal_average(arg)
Computes the average of a DECIMAL column using exact integer arithmetic with banker's rounding. Returns a DECIMAL with the same width and scale as the input.
decimal_division(x, y) decimal_division(x, y, scale)
Divides two DECIMAL values using exact integer arithmetic and round-half-to-even, returning a DECIMAL result with scale determined by SQL Server semantics (result_scale = max(6, s1 + p2 + 1)).
decode(blob) decode(blob, error_option)
Converts blob to VARCHAR. Invalid UTF-8 is handled based on the error behavior argument. Can be 'strict' (default, fail), 'replace' to replace invalid characters with '?', or 'ignore' to skip invalid characters.
degrees(x)
Converts radians to degrees
dense_rank()
The rank of the current row without gaps
deregister_external_resource(name)
destroy_external_resource(deleter_function, payload)
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_parallelism()
divide(left, right)
duckdb_approx_database_count()
duckdb_available_metrics()
duckdb_columns()
Query column metadata to inspect a table’s column names, declared types, and positions without scanning its rows.
duckdb_connection_count()
duckdb_constraints()
duckdb_coordinate_systems()
duckdb_databases()
duckdb_dependencies()
duckdb_dialects()
duckdb_eviction_queues()
duckdb_extension_repositories()
duckdb_extensions()
duckdb_external_file_cache()
duckdb_external_resource_types()
duckdb_external_resources(named arguments)
duckdb_functions()
duckdb_grammar_extensions()
duckdb_indexes()
duckdb_keywords()
duckdb_log_contexts()
duckdb_logs(named arguments)
duckdb_logs_parsed(log_type)
duckdb_memory()
duckdb_optimizers()
duckdb_prepared_statements()
duckdb_profiling_settings()
duckdb_schemas()
duckdb_secret_type_parameters()
duckdb_secret_types()
duckdb_secrets(named arguments)
duckdb_sequences()
duckdb_settings(named arguments)
duckdb_table_sample(table_name)
duckdb_tables()
List table metadata, including database, schema, and table names. Use it to inspect the connected database without reading table data.
duckdb_temporary_files()
duckdb_triggers()
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_profiling(metrics, named arguments)
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) epoch(ts)
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)
fill(expr)
Fill in missing values using linear interpolation with ORDER BY as the X-axis.
filter(list, lambda)
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(state)
first(arg)
Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.
first_value(expr)
The first value of expr in the frame. Can IGNORE or RESPECT NULLS.
flatten(nested_list)
Flattens a nested list by one level.
floor(x)
Rounds the number down
fmod(x, y)
force_checkpoint() force_checkpoint(database)
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(json, structure)
from_json_strict(json, structure)
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(stop) generate_series(start, stop) generate_series(start, stop, step)
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
get_type(expression)
Returns the type of the result of the expression
getvariable(variable_name)
glob(pattern)
grade_up(list) grade_up(list, sort_order) grade_up(list, sort_order, null_order)
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, bins) 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)
hive_partition_component(name, value)
Returns the hive partition directory for a partition column and its value, e.g. name=value. The name and the value are escaped like url_encode does, a NULL value becomes __HIVE_DEFAULT_PARTITION__.
hour(ts)
Extract the hour component from a date or timestamp
icu_calendar_names()
icu_collate_aa(str)
icu_collate_af(str)
icu_collate_am(str)
icu_collate_ar(str)
icu_collate_ar_sa(str)
icu_collate_as(str)
icu_collate_az(str)
icu_collate_bal(str)
icu_collate_be(str)
icu_collate_bg(str)
icu_collate_blo(str)
icu_collate_bn(str)
icu_collate_bo(str)
icu_collate_br(str)
icu_collate_bs(str)
icu_collate_ca(str)
icu_collate_ceb(str)
icu_collate_chr(str)
icu_collate_cs(str)
icu_collate_cu(str)
icu_collate_cy(str)
icu_collate_da(str)
icu_collate_de(str)
icu_collate_de_at(str)
icu_collate_dsb(str)
icu_collate_dz(str)
icu_collate_ee(str)
icu_collate_el(str)
icu_collate_en(str)
icu_collate_en_us(str)
icu_collate_eo(str)
icu_collate_es(str)
icu_collate_et(str)
icu_collate_fa(str)
icu_collate_fa_af(str)
icu_collate_ff(str)
icu_collate_fi(str)
icu_collate_fil(str)
icu_collate_fo(str)
icu_collate_fr(str)
icu_collate_fr_ca(str)
icu_collate_fy(str)
icu_collate_ga(str)
icu_collate_gl(str)
icu_collate_gu(str)
icu_collate_ha(str)
icu_collate_haw(str)
icu_collate_he(str)
icu_collate_he_il(str)
icu_collate_hi(str)
icu_collate_hr(str)
icu_collate_hsb(str)
icu_collate_hu(str)
icu_collate_hy(str)
icu_collate_id(str)
icu_collate_id_id(str)
icu_collate_ig(str)
icu_collate_is(str)
icu_collate_it(str)
icu_collate_ja(str)
icu_collate_ka(str)
icu_collate_kk(str)
icu_collate_kl(str)
icu_collate_km(str)
icu_collate_kn(str)
icu_collate_ko(str)
icu_collate_kok(str)
icu_collate_ku(str)
icu_collate_ky(str)
icu_collate_lb(str)
icu_collate_lij(str)
icu_collate_lkt(str)
icu_collate_ln(str)
icu_collate_lo(str)
icu_collate_lt(str)
icu_collate_lv(str)
icu_collate_mk(str)
icu_collate_ml(str)
icu_collate_mn(str)
icu_collate_mr(str)
icu_collate_ms(str)
icu_collate_mt(str)
icu_collate_my(str)
icu_collate_nb(str)
icu_collate_nb_no(str)
icu_collate_ne(str)
icu_collate_nl(str)
icu_collate_nn(str)
icu_collate_no(str)
icu_collate_noaccent(str)
icu_collate_nso(str)
icu_collate_om(str)
icu_collate_or(str)
icu_collate_pa(str)
icu_collate_pa_in(str)
icu_collate_pl(str)
icu_collate_ps(str)
icu_collate_pt(str)
icu_collate_ro(str)
icu_collate_ru(str)
icu_collate_sa(str)
icu_collate_se(str)
icu_collate_sgs(str)
icu_collate_si(str)
icu_collate_sk(str)
icu_collate_sl(str)
icu_collate_smn(str)
icu_collate_sq(str)
icu_collate_sr(str)
icu_collate_sr_ba(str)
icu_collate_sr_me(str)
icu_collate_sr_rs(str)
icu_collate_ssy(str)
icu_collate_st(str)
icu_collate_sv(str)
icu_collate_sw(str)
icu_collate_ta(str)
icu_collate_te(str)
icu_collate_th(str)
icu_collate_tk(str)
icu_collate_tn(str)
icu_collate_to(str)
icu_collate_tr(str)
icu_collate_ug(str)
icu_collate_uk(str)
icu_collate_ur(str)
icu_collate_uz(str)
icu_collate_vi(str)
icu_collate_vo(str)
icu_collate_wae(str)
icu_collate_wo(str)
icu_collate_xh(str)
icu_collate_yi(str)
icu_collate_yo(str)
icu_collate_zh(str)
icu_collate_zh_cn(str)
icu_collate_zh_hk(str)
icu_collate_zh_mo(str)
icu_collate_zh_sg(str)
icu_collate_zh_tw(str)
icu_collate_zu(str)
icu_sort_key(str, collator)
if(a, b, c)
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(path)
in_search_path(database_name, schema_name)
Returns whether or not the database/schema are in the search path
index_key(path, name, …)
Generate index keys for a named index on a table (specified by a path struct with required 'table' and optional 'catalog'/'schema' fields) from key column arguments. Currently only supports ART indexes.
instr(string, search_string)
Returns location of first occurrence of search_string in string, counting from 1. Returns 0 if no match found.
invoke(lambda, arg1, …)
Invokes a lambda function with the given arguments
IS DISTINCT FROM(left, right)
Returns whether left is distinct from right, treating null as a comparable value.
IS NOT DISTINCT FROM(left, right)
Returns whether left is not distinct from right, treating null as a comparable value.
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(json) json_array_length(json, path)
json_contains(haystack, needle)
json_copy_strftime_if_date(x, format)
json_copy_strftime_if_timestamp(x, format)
json_deep_merge(json1, json2, …)
json_deserialize_sql(json)
json_each(json) json_each(json, path)
json_execute_serialized_sql(serialized_sql)
json_exists(json, path)
Test whether a JSON document contains a path, including paths whose stored value is JSON null.
json_extract(json, index) json_extract(json, path)
Select a value from a JSON document by path while keeping the result as JSON, including quotes around JSON strings.
json_extract_path(json, index) json_extract_path(json, path)
json_extract_path_text(json, index) json_extract_path_text(json, path)
json_extract_string(json, index) json_extract_string(json, path)
Read a JSON field as VARCHAR without JSON string quotes, ready for text filtering, display, or a subsequent type conversion.
json_group_array(x)
json_group_object(n, v)
json_group_structure(x)
json_insert(json, path, value)
json_keys(json) json_keys(json, path)
json_merge_patch(json1, json2, …)
json_merge_patch_diff(old, new)
json_normalize(json)
json_object(…)
json_pretty(json)
json_quote(…)
json_remove(json, path)
json_replace(json, path, value)
json_serialize_plan(sql, skip_null, skip_empty, skip_default, format, optimize)
json_serialize_sql(sql, skip_null, skip_empty, skip_default, format)
json_set(json, path, value)
json_strip_nulls(json)
json_structure(json)
json_transform(json, structure)
json_transform_strict(json, structure)
json_tree(json) json_tree(json, path)
json_type(json) json_type(json, path)
json_valid(json)
json_value(json, index) json_value(json, path)
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
lag(col, offset, default)
The value of expr n rows after the current row, or the default. Can IGNORE or RESPECT NULLS.
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
last_value(expr)
The last value of expr in the frame. Can IGNORE or RESPECT NULLS.
lcase(string)
Converts string to lower case.
lcm(x, y)
Computes the least common multiple of x and y
lead(col, offset, default)
The value of expr n rows after the current row, or the default. Can IGNORE or RESPECT NULLS.
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)
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) list_element(string, index)
Extract the indexth (1-based) value from the list.
list_entropy(l)
list_extract(list, index) list_extract(string, index)
Extract the indexth (1-based) value from the list.
list_filter(list, lambda)
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, sort_order) list_grade_up(list, sort_order, null_order)
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(list1, list2)
Returns a list containing the distinct elements that are present in both list1 and list2.
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(value, …)
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) list_reduce(list, lambda, 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, null_order)
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, sort_order) list_sort(list, sort_order, null_order)
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)
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(value, …)
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(x) 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.
lttb(x, y, n)
Downsamples the (x, y) points to (at most) n representative points using the Largest Triangle Three Buckets algorithm. Returns a LIST of STRUCT(x, y); the points must be ordered by x (use lttb(x, y, n ORDER BY x)).
mad(x)
Returns the median absolute deviation for the values within x. NULL values are ignored. Temporal types return a positive INTERVAL.
make_date(date-struct) make_date(days) make_date(year, month, day)
The date for the given struct.
make_time(hour, minute, seconds)
The time for the given parts
make_timestamp(micros) 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(micros) make_timestamptz(year, month, day, hour, minute, seconds) make_timestamptz(year, month, day, hour, minute, seconds, timezone)
make_type(name, …)
Construct a type from its name and optional parameters
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, n)
Returns the maximum value present in arg.
max_by(arg, val) max_by(arg, val, N)
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, n)
Returns the minimum value present in arg.
min_by(arg, val) min_by(arg, val, N)
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(left, right)
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(left, right)
nanoarrow_version()
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
nth_value(expr, n)
The nth value of expr in the frame. Can IGNORE or RESPECT NULLS.
ntile(num_buckets)
The row bucket in a window partition for a given bucket count
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.
overlay(string, replacement, start) overlay(string, replacement, start, count)
Replaces a substring of string starting at character start with replacement. If count is not specified, it defaults to the length of replacement. Note that a start value of 1 refers to the first character of the string.
parquet_bloom_probe(path, column_name, value) parquet_bloom_probe(paths, column_name, value)
parquet_file_metadata(path) parquet_file_metadata(paths)
parquet_full_metadata(path) parquet_full_metadata(paths)
parquet_kv_metadata(path) parquet_kv_metadata(paths)
parquet_metadata(path) parquet_metadata(paths)
parquet_scan(path, named arguments) parquet_scan(paths, named arguments)
parquet_schema(path) parquet_schema(paths)
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, separator) 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_formatted_bytes(string)
Parses a human-readable representation of a size in bytes into an integer.
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.
path_join(path, …)
Joins path components using the platform separator, normalizing duplicate separators and dot segments. Absolute RHS components must be compatible (same scheme/parent) or raise an error.
percent_rank()
The relative rank of the current row as a fraction
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(database)
pragma_platform()
pragma_show(table_name)
pragma_storage_info(table_name, named arguments)
pragma_table_info(table_name)
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, quantile)
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, quantile)
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, quantile)
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(query)
query_table(table_name) query_table(table_names) query_table(table_name, by_name) query_table(table_names, by_name)
radians(x)
Converts degrees to radians
random()
Returns a random number between 0 and 1
range(stop) range(start, stop) range(start, stop, step)
Creates a list of values between start and stop - the stop parameter is exclusive.
rank()
The rank of the current row with gaps
rank_dense()
The rank of the current row without gaps
read_arrow(path, named arguments) read_arrow(paths, named arguments)
read_blob(path, named arguments) read_blob(paths, named arguments)
read_csv(path, named arguments) read_csv(paths, named arguments)
Read a CSV file as a table and query its columns directly. Set header and column types explicitly when the file format is known.
read_csv_auto(path, named arguments) read_csv_auto(paths, named arguments)
read_duckdb(path, named arguments) read_duckdb(paths, named arguments)
read_json(path, named arguments) read_json(paths, named arguments)
read_json_auto(path, named arguments) read_json_auto(paths, named arguments)
read_json_objects(path, named arguments) read_json_objects(paths, named arguments)
read_json_objects_auto(path, named arguments) read_json_objects_auto(paths, named arguments)
read_ndjson(path, named arguments) read_ndjson(paths, named arguments)
read_ndjson_auto(path, named arguments) read_ndjson_auto(paths, named arguments)
read_ndjson_objects(path, named arguments) read_ndjson_objects(paths, named arguments)
read_parquet(path, named arguments) read_parquet(paths, named arguments)
Read typed columns from a Parquet file directly in a FROM clause, selecting and filtering the fields needed by your query.
read_single_csv_file(file, named arguments)
read_single_json_file(file, named arguments)
read_single_parquet_file(file, named arguments)
read_text(path, named arguments) read_text(paths, named arguments)
reduce(list, lambda) reduce(list, lambda, 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, options) 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, name_list) regexp_extract_all(string, regex, group, options) regexp_extract_all(string, regex, name_list, 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, options)
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)
register_external_resource(type, name, handle, named arguments)
register_external_resource_type(name, named arguments)
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(list, count) repeat(string, count) repeat(value, 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, 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, precision)
Rounds x to s decimal places, rounding halfway cases to the nearest even digit
roundbankers(x, precision)
Rounds x to s decimal places, rounding halfway cases to the nearest even digit
row(…)
Create an unnamed STRUCT (tuple) containing the argument values.
row_number()
The row number in a window partition
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.
scan_arrow_ipc(col0)
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(seed)
Sets the seed to be used for the random function
setval(sequence_name, value) setval(sequence_name, value, is_called)
Set the value of the sequence to the specified value. If is_called is true, the sequence is called. If is_called is false, the sequence is not called.
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(table_name)
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.
sleep_ms(milliseconds)
Sleeps for the specified number of milliseconds and returns NULL
sniff_csv(path, named arguments)
split(string, separator)
Splits the string along the separator.
split_part(string, delimiter, position)
sqrt(x)
Returns the square root of x
st_asbinary(geom)
Returns the Well-Known Binary (WKB) representation of the geometry
st_asgeojson(geom)
st_astext(geom)
Returns the Well-Known Text (WKT) representation of the geometry
st_aswkb(geom)
Returns the Well-Known Binary (WKB) representation of the geometry
st_aswkt(geom)
Returns the Well-Known Text (WKT) representation of the geometry
st_crs(geom)
Returns the Coordinate Reference System (CRS) identifier of the geometry
st_geomfromgeojson(geojson)
st_geomfromwkb(wkb)
Creates a geometry from Well-Known Binary (WKB) representation
st_intersects_extent(geom1, geom2)
Returns true if the geometries bounding boxes intersect
st_setcrs(geom, crs)
Sets the Coordinate Reference System (CRS) identifier of the geometry
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(table_name)
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) strftime(format, data)
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) struct_extract(tuple, index)
Extract the named entry from the STRUCT.
struct_extract_at(struct, index)
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(struct, …)
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_keys(struct)
Returns the field names of a STRUCT as a list
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(struct, …)
Changes field(s)/value(s) to an existing STRUCT with the argument values. The entry name(s) will be the bound variable name(s)
struct_values(struct)
Returns the field values of a STRUCT as an UnnamedStruct
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(x) subtract(left, right)
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(input)
switch(map) switch(key, map) switch(map, value) switch(key, map, value)
Creates a switch statement similar to CASE WHEN/THEN
table_info(table_name)
tan(x)
Computes the tan of x
tanh(x)
Computes the hyperbolic tan of x
test_all_types(named arguments)
test_vector_types(column, …, named arguments)
time_bucket(bucket_width, timestamp) time_bucket(bucket_width, timestamp, offset) time_bucket(bucket_width, timestamp, origin) time_bucket(bucket_width, timestamp, timezone)
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(offset, time_tz) timezone(timezone, timestamp)
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_aggregate_state(data, name, signature) to_aggregate_state(data, name, signature, constant_parameters) to_aggregate_state(data, name, signature, constant_parameters, order_by)
Converts a value into the aggregate state of the aggregate function with the given name and signature. The type of the value must exactly match the state layout of the aggregate function. An optional fourth argument supplies constant parameter values (e.g. string_agg's separator) as a list with one entry per argument, using NULL for arguments that are not bound to a constant.
to_arrow_ipc(col0, named arguments)
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, precision)
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(list)
Expand a list into individual rows so each item can be filtered, joined, or aggregated with ordinary SQL.
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_array_length(input_variant) variant_array_length(input_variant, path)
variant_bytes_to_variant(blob)
variant_comparator(input_variant)
Returns a binary sort key that orders input_variant according to the logical ordering of VARIANT values (used internally for comparison and ordering).
variant_contains(variant_haystack, variant_needle)
variant_exists(input_variant, path)
variant_extract(input_variant, field) variant_extract(input_variant, index)
variant_extract_string(input_variant, path)
variant_group_array(x)
variant_group_object(key, value)
Returns a VARIANT object containing all key, value pairs in the aggregation.
variant_keys(input_variant) variant_keys(input_variant, path)
variant_normalize(input_variant)
Normalizes the input_variant to a canonical representation.
variant_to_parquet_variant(variant)
variant_type(input_variant) variant_type(input_variant, path)
variant_typeof(input_variant)
Returns the internal type of the input_variant.
vector_type(col)
Returns the VectorType of a given column
verify_parallelism()
version()
Returns the currently active version of DuckDB in this format: v0.3.2
vertex_extract(geom, coordinate)
Extracts the specified coordinate (X, Y, Z, M) from a point geometry
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(path, type)
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
__between(input, lower, upper)
Returns whether or not input is between lower and upper.
__cast(value)
Casts value to the target type of the expression.
__internal_barrier(arg)
Returns arg unchanged, and marks it as an optimizer barrier: the expression is never evaluated on rows removed by the operators around it
__internal_compress_geometry_point(value)
__internal_compress_integral_ubigint(value, min)
__internal_compress_integral_uinteger(value, min)
__internal_compress_integral_usmallint(value, min)
__internal_compress_integral_utinyint(value, min)
__internal_compress_string_hugeint(value)
__internal_compress_string_ubigint(value)
__internal_compress_string_uhugeint(value)
__internal_compress_string_uinteger(value)
__internal_compress_string_usmallint(value)
__internal_compress_string_utinyint(value)
__internal_compressed_materialization_cast(value)
__internal_decompress_geometry_point(value)
__internal_decompress_integral_bigint(value, min)
__internal_decompress_integral_hugeint(value, min)
__internal_decompress_integral_integer(value, min)
__internal_decompress_integral_smallint(value, min)
__internal_decompress_integral_ubigint(value, min)
__internal_decompress_integral_uhugeint(value, min)
__internal_decompress_integral_uinteger(value, min)
__internal_decompress_integral_usmallint(value, min)
__internal_decompress_string(value)
__internal_json_copy_to_geojson(value, date_format, timestamp_format, geometry_column, id_column, write_bbox)
__internal_json_copy_to_json(value, date_format, timestamp_format)
__internal_tablefilter_bloom_filter(col)
__internal_tablefilter_dynamic(col)
__internal_tablefilter_optional(col)
__internal_tablefilter_prefix_range(col)
__internal_tablefilter_selectivity_optional(col)
No functions found.
Try a shorter name, a data type like VARCHAR, or clear your filters.