Functions and operators

VegaDB resolves functions by name and argument types. This page maps the complete customer-facing function surface. The SQL functions view in the VegaDB console shows the exact overloads, parameter types and return types enabled for a warehouse release.

Arithmetic and comparison

+, -, *, /, //, %, **, unary +/-, =, <>, !=, <, <=, >, >=, BETWEEN, IN, IS NULL, IS DISTINCT FROM, AND, OR, NOT and bitwise operators are supported for compatible types.

Use IS NOT DISTINCT FROM when two NULLs should compare equal. Collection and nested types have stricter comparability rules than primitive scalars.

Conditional and conversion

Text and regular expressions

Common calls include lower, upper, trim, ltrim, rtrim, length, concat, concat_ws, format, printf, substring, left, right, replace, reverse, split_part, starts_with, ends_with, contains, levenshtein, regexp_extract, regexp_extract_all, regexp_matches, regexp_replace and regexp_split_to_array.

SELECT lower(trim(email)) AS normalized_email,
       regexp_extract(path, '/orders/([0-9]+)', 1) AS order_id
FROM requests;

Date and time

current_date, current_time, current_timestamp, date_part, date_trunc, date_diff, date_sub, date_add, make_date, make_time, make_timestamp, epoch, strftime, strptime, time_bucket, last_day, dayname, monthname and AT TIME ZONE cover the normal temporal surface.

SELECT time_bucket(INTERVAL '15 minutes', event_time) AS bucket,
       count(*)
FROM events
GROUP BY bucket;

JSON

Use json_extract, json_extract_string, json_value, json_exists, json_array_length, json_keys, json_type, json_valid, json_structure, json_transform, json_merge_patch, json_array, json_object and to_json. Arrow operators -> and ->> extract JSON and text.

LIST and ARRAY

List functions include list_value, list_extract, list_slice, list_contains, list_position, list_concat, list_distinct, list_sort, list_reverse_sort, list_filter, list_transform, list_reduce, list_zip, list_where, list_select, list_resize, list_flatten, list_grade_up, list_aggregate, range and generate_series.

Fixed arrays add array_value, array_distance, array_cosine_distance, array_cosine_similarity, array_inner_product, array_dot_product, array_negative_inner_product and array_cross_product for compatible numeric arrays.

SELECT list_transform(scores, x -> round(x * 100, 1)) AS percentages,
       array_cosine_similarity(embedding, $1::FLOAT[768]) AS similarity
FROM documents;

STRUCT, MAP and UNION

Use struct literals, struct_pack, row, struct_extract, struct_insert, map, map_from_entries, map_entries, map_keys, map_values, map_extract, union_value, union_tag and union_extract.

General aggregates

Aggregate calls accept DISTINCT, FILTER (WHERE ...) and, where meaningful, an internal ORDER BY.

SELECT account_id,
       count(*) FILTER (WHERE status = 'failed') AS failures,
       string_agg(event_type, ', ' ORDER BY occurred_at) AS event_path
FROM account_events
GROUP BY account_id;

Statistical and approximate aggregates

Supported statistical families include corr, covariance, median, mode, median absolute deviation, continuous/discrete quantiles, entropy, skewness, kurtosis, standard error, population/sample variance and standard deviation, and the regr_* regression aggregates.

Approximate functions include approx_count_distinct, approx_quantile, approx_top_k and reservoir_quantile. Their distributed state formats are versioned; a retry or final merge does not combine arbitrary incompatible sketches.

SELECT
  approx_count_distinct(user_id),
  approx_quantile(duration_ms, [0.5, 0.95, 0.99]),
  approx_top_k(country, 10)
FROM requests;

Window functions

row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value and aggregates used with OVER are supported. Result semantics depend on the declared partition, ordering and frame.

Volatility and side effects

VegaDB records function stability and side effects during binding. Volatile expressions are evaluated within their proven row or query scope. A retry does not get to collapse separate outer-row evaluations because the displayed correlation values happen to match. Side-effecting or unsafe table functions are outside the pure distributed query surface and fail closed unless a specific managed service owns them.