Function catalog
Supported function names, aliases and signatures organized by family.
This page is the discovery index for VegaDB functions. Family pages explain behavior and examples; information_schema.routines returns the exact overload types enabled on the connected warehouse.
For worked SQL examples, use scalar functions, aggregates, window functions or nested data and JSON.
SELECT routine_name, data_type
FROM information_schema.routines
ORDER BY routine_name, specific_name;An alias has the same core operation as its canonical name but can retain syntax-specific argument order. An overload remains subject to its accepted input types, dimensions and bounded-state rules.
Aggregate
| Family | Names and signatures |
|---|---|
| Counting | count(*), count(value), countif(condition), count_if(condition) |
| Sum and average | sum(value), fsum(value), avg(value), favg(value), weighted_avg(value, weight), product(value), geometric_mean(value) |
| Extremes | min(value), max(value), min(value, n), max(value, n) |
| Associated values | arg_min(arg, value), arg_max(arg, value), arg_min(arg, value, n), arg_max(arg, value, n), arg_min_null, arg_max_null, min_by, max_by |
| Value selection | any_value, arbitrary, first, last |
| Collections | list, array_agg, string_agg, group_concat |
| Boolean and bit | bool_and, bool_or, every, bit_and, bit_or, bit_xor, bitstring_agg |
| Distribution | histogram, histogram_exact, histogram_values |
| Approximate | approx_count_distinct, approx_quantile, approx_top_k, reservoir_quantile |
| Exact statistics | median, mode, mad, quantile_cont, quantile_disc, entropy, skewness, kurtosis, kurtosis_pop, sem |
| Variation | var_pop, var_samp, variance, stddev_pop, stddev_samp, stddev |
| Covariance | corr, covar_pop, covar_samp |
| Regression | regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy |
Numeric
| Family | Names |
|---|---|
| Magnitude and sign | abs, sign, signbit, even |
| Rounding | ceil, ceiling, floor, round, round_even, roundbankers, trunc |
| Roots and powers | sqrt, cbrt, pow, power, exp |
| Logarithms | ln, log, log2, log10 |
| Trigonometry | sin, cos, tan, cot, asin, acos, atan, atan2 |
| Hyperbolic | sinh, cosh, tanh, asinh, acosh, atanh |
| Angles | degrees, radians, pi |
| Integer helpers | bit_count, factorial, gcd, greatest_common_divisor, lcm, least_common_multiple |
| Special/boundary | gamma, lgamma, isfinite, isinf, isnan, nextafter |
| Selection | greatest, least |
| Arithmetic aliases | add, subtract, multiply, divide, fdiv, fmod |
| Random | random |
Numeric operators include addition, subtraction, multiplication, division, integer division, remainder, power, bitwise operations and shifts. See Expressions and operators.
Text
| Family | Names |
|---|---|
| Length/code points | length, len, char_length, character_length, strlen, octet_length, bit_length, ascii, unicode, ord, chr |
| Case/normalization | lower, lcase, upper, ucase, casefold, nfc_normalize, strip_accents |
| Trim/pad | trim, ltrim, rtrim, lpad, rpad |
| Extract | substring, substr, substring_grapheme, left, right, left_grapheme, right_grapheme, split_part |
| Combine/transform | concat, concat_ws, replace, translate, reverse, repeat |
| Find/test | contains, starts_with, ends_with, prefix, suffix, strpos, instr, position |
| Split | split, string_split, string_to_array, str_split, string_split_regex, str_split_regex |
| Similarity | levenshtein, damerau_levenshtein, editdist3, hamming, mismatches, jaccard, jaro_similarity, jaro_winkler_similarity |
| Formatting | format, printf, bar, format_bytes, formatReadableDecimalSize |
| Paths | parse_filename, parse_dirname, parse_dirpath, parse_path |
Pattern helpers include like_escape, ilike_escape, not_like_escape and not_ilike_escape. Pattern operators and regular-expression functions are documented below.
Regular expressions
regexp_escape, regexp_extract, regexp_extract_all, regexp_full_match, regexp_matches, regexp_replace, regexp_split_to_array and regexp_split_to_table support compatible patterns and option flags. Common flags include case-sensitive/case-insensitive, literal, newline and global replacement behavior where applicable.
Date, time and interval
| Family | Names |
|---|---|
| Current values | current_date, today, current_time, current_timestamp, now, transaction_timestamp, localtime, localtimestamp |
| Parts | date_part, datepart, extract, plus convenience part functions such as year, month, week, day, hour, minute, second, epoch, isoyear, isodow |
| Arithmetic | date_add, date_diff, date_sub, age |
| Boundaries | date_trunc, time_bucket, last_day, days_in_month |
| Names/conversion | dayname, monthname, julian, epoch_ms, epoch_us, epoch_ns, to_timestamp |
| Constructors | make_date, make_time, make_timestamp, make_timestamp_ms, make_timestamp_ns, make_timestamptz |
| Parse/format | strftime, strptime, try_strptime |
| Zones | timezone, AT TIME ZONE |
| Interval constructors | to_years, to_quarters, to_months, to_weeks, to_days, to_hours, to_minutes, to_seconds, to_milliseconds, to_microseconds |
| Series | range, generate_series with compatible temporal bounds and interval step |
See Date and time formats for supported percent specifiers.
Binary, encoding and hashing
base64, to_base64, from_base64, hex, to_hex, from_hex, unhex, bin, unbin, encode, decode, to_binary, from_binary, url_encode, url_decode, md5, md5_number, md5_number_lower, md5_number_upper, sha1, sha256 and hash.
LIST and list aliases
| Family | Canonical names and common aliases |
|---|---|
| Construct | list_value, list_pack, array_value, range, generate_series, repeat |
| Access/slice | list_extract, list_element, list_slice, array_extract, array_slice |
| Length/membership | length, list_contains, list_has, list_has_all, list_has_any, list_position, list_indexof, plus corresponding array_* aliases |
| Add/combine | list_append, list_prepend, list_concat, list_cat, list_resize, plus array_* aliases |
| Order/set | list_distinct, list_unique, list_intersect, list_sort, list_reverse, list_reverse_sort, list_grade_up, plus array_* aliases |
| Select/shape | list_select, list_where, list_zip, flatten, unnest, plus array_* aliases |
| Lambda | list_apply, list_filter, list_transform, list_reduce, apply, filter, reduce, plus array_* aliases |
| Aggregate a list | list_aggregate, list_aggr, list_sum, list_avg, list_min, list_max, list_count, list_product, list_first, list_last, list_any_value |
| List statistics | list_median, list_mode, list_mad, list_sem, list_entropy, list_skewness, list_kurtosis, list_var_pop, list_var_samp, list_stddev_pop, list_stddev_samp |
| Vector-like list math | list_distance, list_cosine_distance, list_cosine_similarity, list_inner_product, list_dot_product, list_negative_inner_product, list_negative_dot_product |
Fixed ARRAY and vector math
array_value, array_distance, array_cosine_distance, array_cosine_similarity, array_inner_product, array_dot_product, array_negative_inner_product, array_negative_dot_product and array_cross_product require compatible numeric element types and dimensions.
STRUCT, MAP, UNION and enum
| Type | Names |
|---|---|
| STRUCT | row, struct_pack, struct_extract, struct_extract_at, struct_insert, struct_update, struct_concat, struct_contains, struct_has, struct_position, struct_indexof |
| MAP | map, map_from_entries, map_entries, map_keys, map_values, map_extract, map_extract_value, element_at, map_contains, map_contains_entry, map_contains_value, map_concat, cardinality |
| UNION | union_value, union_tag, union_extract, member access |
| ENUM | enum_code, enum_first, enum_last, enum_range, enum_range_boundary |
JSON
json_valid, json_type, json_structure, json_extract, json_extract_string, json_value, json_exists, json_array_length, json_keys, json_contains, json_array, json_object, to_json, json_merge_patch, json_transform, json_transform_strict and from_json. The arrow operators return JSON or text according to the selected form.
Conditional, conversion and utility
coalesce, if, ifnull, nullif, constant_or_null, CAST, TRY_CAST, cast_to_type, can_cast_implicitly, typeof, pg_typeof, alias, error, current_catalog, current_database, current_schema, current_schemas, current_query, current_setting, version, stats, create_sort_key, equi_width_bins, is_histogram_other_bin, uuid, uuidv4, uuidv7, gen_random_uuid, uuid_extract_version and uuid_extract_timestamp.
Window
row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value, fill, and supported aggregates used with OVER.
See Window functions for ordering and frame behavior.