HeliosDB SQL Function Reference¶

Version: 7.0 (PostgreSQL 17 Compatible) Last Updated: January 2026

Complete reference for all SQL functions available in HeliosDB, including aggregate, string, date/time, JSON/JSONB, vector, window, and analytical functions.

Table of Contents¶

Aggregate Functions
String Functions
Date/Time Functions
Mathematical Functions
JSON/JSONB Functions
Array Functions
Vector Functions
Window Functions
Analytical Functions
Conditional Functions
Type Conversion Functions
System Functions

Aggregate Functions¶

Aggregate functions compute a single result from a set of input values.

COUNT¶

Count rows or non-null values.

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-null values
SELECT COUNT(email) FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT status) FROM users;

-- Count with filter
SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count FROM users;

SUM¶

Calculate the sum of numeric values.

-- Basic sum
SELECT SUM(amount) FROM orders;

-- Sum with grouping
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

-- Sum with filter
SELECT SUM(amount) FILTER (WHERE status = 'completed') AS completed_total
FROM orders;

-- Sum distinct values
SELECT SUM(DISTINCT amount) FROM orders;

AVG¶

Calculate the average of numeric values.

-- Basic average
SELECT AVG(age) FROM users;

-- Average with precision
SELECT ROUND(AVG(salary)::numeric, 2) AS avg_salary FROM employees;

-- Weighted average
SELECT SUM(price * quantity) / SUM(quantity) AS weighted_avg_price
FROM order_items;

-- Moving average (with window function)
SELECT date, value,
       AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_metrics;

MIN / MAX¶

Find minimum and maximum values.

-- Basic min/max
SELECT MIN(created_at), MAX(created_at) FROM users;

-- Min/max with grouping
SELECT category, MIN(price) AS min_price, MAX(price) AS max_price
FROM products
GROUP BY category;

-- Min/max on text (alphabetical)
SELECT MIN(name), MAX(name) FROM users;

-- Min/max on dates
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM orders
WHERE customer_id = 123;

STRING_AGG¶

Concatenate strings with a delimiter.

-- Basic string aggregation
SELECT STRING_AGG(name, ', ') FROM users;

-- With ordering
SELECT STRING_AGG(name, ', ' ORDER BY name) AS sorted_names FROM users;

-- With distinct
SELECT STRING_AGG(DISTINCT category, ', ') AS categories FROM products;

-- Grouped string aggregation
SELECT department_id, STRING_AGG(employee_name, ', ') AS employees
FROM employees
GROUP BY department_id;

ARRAY_AGG¶

Aggregate values into an array.

-- Basic array aggregation
SELECT ARRAY_AGG(id) FROM users WHERE status = 'active';

-- With ordering
SELECT ARRAY_AGG(name ORDER BY created_at DESC) FROM users;

-- Grouped array aggregation
SELECT category, ARRAY_AGG(product_name) AS products
FROM products
GROUP BY category;

-- Multidimensional array
SELECT ARRAY_AGG(ARRAY[id, age]) FROM users;

JSON_AGG / JSONB_AGG¶

Aggregate values into a JSON array.

-- Basic JSON aggregation
SELECT JSON_AGG(name) FROM users;

-- Aggregate rows as JSON objects
SELECT JSON_AGG(row_to_json(u)) FROM users u WHERE status = 'active';

-- With ordering
SELECT JSONB_AGG(name ORDER BY created_at) FROM users;

-- Grouped JSON aggregation
SELECT category, JSONB_AGG(jsonb_build_object(
    'id', id,
    'name', name,
    'price', price
)) AS products
FROM products
GROUP BY category;

JSON_OBJECT_AGG / JSONB_OBJECT_AGG¶

Aggregate key-value pairs into a JSON object.

-- Create object from key-value pairs
SELECT JSON_OBJECT_AGG(key, value) FROM settings;

-- Create object with custom keys
SELECT JSONB_OBJECT_AGG(id::text, name) AS id_to_name_map FROM users;

-- Grouped object aggregation
SELECT department_id, JSONB_OBJECT_AGG(employee_id::text, employee_name) AS employees
FROM employees
GROUP BY department_id;

BOOL_AND / BOOL_OR¶

Aggregate boolean values.

-- All true check
SELECT BOOL_AND(is_verified) AS all_verified FROM users;

-- Any true check
SELECT BOOL_OR(has_premium) AS any_premium FROM users;

-- With grouping
SELECT department_id, BOOL_AND(completed) AS all_completed
FROM tasks
GROUP BY department_id;

PERCENTILE_CONT / PERCENTILE_DISC¶

Calculate percentiles.

-- Continuous percentile (interpolated)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

-- Multiple percentiles
SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY response_time) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY response_time) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY response_time) AS p75,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time) AS p99
FROM requests;

-- Discrete percentile (actual value)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) AS median_score
FROM exam_results;

Statistical Functions¶

-- Standard deviation
SELECT STDDEV(salary), STDDEV_POP(salary), STDDEV_SAMP(salary)
FROM employees;

-- Variance
SELECT VARIANCE(salary), VAR_POP(salary), VAR_SAMP(salary)
FROM employees;

-- Correlation coefficient
SELECT CORR(advertising_spend, sales) AS correlation
FROM monthly_data;

-- Covariance
SELECT COVAR_POP(x, y), COVAR_SAMP(x, y)
FROM data_points;

-- Regression
SELECT REGR_SLOPE(sales, advertising) AS slope,
       REGR_INTERCEPT(sales, advertising) AS intercept,
       REGR_R2(sales, advertising) AS r_squared
FROM monthly_data;

String Functions¶

Functions for manipulating text and string data.

CONCAT / CONCAT_WS¶

Concatenate strings.

-- Basic concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Concatenate with separator
SELECT CONCAT_WS(', ', city, state, country) AS location FROM addresses;

-- Handles NULL gracefully
SELECT CONCAT(name, ' - ', COALESCE(description, 'No description')) FROM products;

SUBSTRING / SUBSTR¶

Extract a portion of a string.

-- Extract by position and length
SELECT SUBSTRING(email FROM 1 FOR 10) FROM users;

-- Alternative syntax
SELECT SUBSTR(phone, 1, 3) AS area_code FROM contacts;

-- Extract using pattern
SELECT SUBSTRING(email FROM '@(.*)$') AS domain FROM users;

-- Extract from position to end
SELECT SUBSTRING(text FROM 5) FROM documents;

UPPER / LOWER / INITCAP¶

Change case.

-- Convert to uppercase
SELECT UPPER(name) FROM users;

-- Convert to lowercase
SELECT LOWER(email) FROM users;

-- Capitalize first letter of each word
SELECT INITCAP(title) FROM articles;

-- Case-insensitive comparison
SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.COM');

TRIM / LTRIM / RTRIM¶

Remove characters from strings.

-- Remove leading/trailing whitespace
SELECT TRIM(name) FROM users;

-- Remove specific characters
SELECT TRIM(BOTH '0' FROM phone) FROM contacts;

-- Remove leading zeros
SELECT LTRIM(code, '0') FROM products;

-- Remove trailing spaces
SELECT RTRIM(description) FROM products;

-- Remove leading whitespace only
SELECT TRIM(LEADING FROM name) FROM users;

LENGTH / CHAR_LENGTH / BIT_LENGTH¶

Get string length.

-- Character length
SELECT LENGTH(name), CHAR_LENGTH(name) FROM users;

-- Byte length
SELECT OCTET_LENGTH(name) FROM users;

-- Bit length
SELECT BIT_LENGTH(name) FROM users;

-- Unicode character length
SELECT LENGTH('Hello world') AS len;  -- Returns 11

POSITION / STRPOS¶

Find substring position.

-- Find position of substring
SELECT POSITION('@' IN email) FROM users;

-- Alternative syntax
SELECT STRPOS(email, '@') FROM users;

-- Returns 0 if not found
SELECT * FROM users WHERE POSITION('@gmail.com' IN email) > 0;

REPLACE / TRANSLATE¶

Replace characters or substrings.

-- Replace substring
SELECT REPLACE(phone, '-', '') AS clean_phone FROM contacts;

-- Replace multiple occurrences
SELECT REPLACE(REPLACE(text, '\n', ' '), '\t', ' ') FROM documents;

-- Translate characters (one-to-one mapping)
SELECT TRANSLATE('hello', 'el', 'ip') FROM dual;  -- Returns 'hippo'

-- Remove characters
SELECT TRANSLATE(phone, '()-. ', '') FROM contacts;

SPLIT_PART¶

Split string and get a specific part.

-- Split by delimiter and get part
SELECT SPLIT_PART(email, '@', 1) AS username,
       SPLIT_PART(email, '@', 2) AS domain
FROM users;

-- Split path and get filename
SELECT SPLIT_PART(file_path, '/', -1) AS filename FROM files;

-- Handle multiple delimiters
SELECT SPLIT_PART('a,b,c', ',', 2);  -- Returns 'b'

REGEXP Functions¶

Regular expression operations.

-- Check if pattern matches
SELECT * FROM users WHERE email ~ '^[a-z]+@gmail\.com$';

-- Case-insensitive match
SELECT * FROM users WHERE name ~* 'john';

-- Replace with regex
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only FROM contacts;

-- Extract matches
SELECT REGEXP_MATCHES(email, '([^@]+)@([^.]+)\.(.+)') FROM users;

-- Split by regex
SELECT REGEXP_SPLIT_TO_ARRAY(tags, '[,;|]') FROM articles;

-- Split to table
SELECT REGEXP_SPLIT_TO_TABLE(tags, '[,;|]') AS tag FROM articles;

LEFT / RIGHT¶

Extract characters from left or right.

-- Get first N characters
SELECT LEFT(name, 10) FROM users;

-- Get last N characters
SELECT RIGHT(phone, 4) AS last_four FROM contacts;

-- Negative values
SELECT LEFT(name, -2);  -- All except last 2 characters

LPAD / RPAD¶

Pad strings to a specific length.

-- Left pad with zeros
SELECT LPAD(id::text, 8, '0') AS padded_id FROM orders;

-- Right pad with spaces
SELECT RPAD(name, 50, ' ') AS fixed_width FROM users;

-- Pad with custom characters
SELECT LPAD(amount::text, 10, '*') FROM transactions;

REPEAT / REVERSE¶

Repeat or reverse strings.

-- Repeat string
SELECT REPEAT('*', 10);  -- '**********'

-- Reverse string
SELECT REVERSE(name) FROM users;

-- Create separator line
SELECT REPEAT('-', 80) AS separator;

FORMAT¶

Format strings using placeholders.

-- Basic formatting
SELECT FORMAT('Hello, %s!', name) FROM users;

-- Multiple arguments
SELECT FORMAT('%s (%s) - $%s', product_name, sku, price) FROM products;

-- Numbered placeholders (for reuse)
SELECT FORMAT('%1$s, %1$s, %2$s', 'hello', 'world');  -- 'hello, hello, world'

-- Type formatting
SELECT FORMAT('%L', name) AS literal_string FROM users;  -- Quoted literal
SELECT FORMAT('%I', column_name) AS identifier FROM columns;  -- Quoted identifier

Date/Time Functions¶

Functions for working with dates, times, and timestamps.

Current Date/Time¶

-- Current timestamp with timezone
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- Current date
SELECT CURRENT_DATE;

-- Current time
SELECT CURRENT_TIME;

-- Transaction start time
SELECT TRANSACTION_TIMESTAMP();

-- Statement start time
SELECT STATEMENT_TIMESTAMP();

-- Actual clock time
SELECT CLOCK_TIMESTAMP();

EXTRACT / DATE_PART¶

Extract components from date/time values.

-- Extract year
SELECT EXTRACT(YEAR FROM created_at) FROM orders;

-- Extract multiple components
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(HOUR FROM created_at) AS hour,
    EXTRACT(MINUTE FROM created_at) AS minute,
    EXTRACT(SECOND FROM created_at) AS second
FROM events;

-- Alternative syntax
SELECT DATE_PART('year', created_at) FROM orders;

-- Extract day of week (0=Sunday, 6=Saturday)
SELECT EXTRACT(DOW FROM created_at) AS day_of_week FROM events;

-- Extract ISO day of week (1=Monday, 7=Sunday)
SELECT EXTRACT(ISODOW FROM created_at) AS iso_day_of_week FROM events;

-- Extract week number
SELECT EXTRACT(WEEK FROM created_at) AS week_number FROM events;

-- Extract quarter
SELECT EXTRACT(QUARTER FROM created_at) AS quarter FROM orders;

-- Extract epoch (seconds since 1970-01-01)
SELECT EXTRACT(EPOCH FROM created_at) FROM events;

DATE_TRUNC¶

Truncate timestamp to specified precision.

-- Truncate to day
SELECT DATE_TRUNC('day', created_at) FROM orders;

-- Truncate to month
SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- Available precision values
SELECT
    DATE_TRUNC('year', ts) AS year,
    DATE_TRUNC('quarter', ts) AS quarter,
    DATE_TRUNC('month', ts) AS month,
    DATE_TRUNC('week', ts) AS week,
    DATE_TRUNC('day', ts) AS day,
    DATE_TRUNC('hour', ts) AS hour,
    DATE_TRUNC('minute', ts) AS minute,
    DATE_TRUNC('second', ts) AS second
FROM (SELECT NOW() AS ts) t;

Interval Arithmetic¶

-- Add interval
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() + INTERVAL '2 hours 30 minutes';
SELECT NOW() + INTERVAL '1 month';

-- Subtract interval
SELECT NOW() - INTERVAL '7 days';
SELECT created_at - INTERVAL '1 year' FROM users;

-- Multiply interval
SELECT INTERVAL '1 hour' * 24 AS one_day;

-- Date difference
SELECT AGE(NOW(), created_at) FROM users;
SELECT created_at - '2025-01-01'::date AS days_since FROM orders;

TO_CHAR / TO_TIMESTAMP / TO_DATE¶

Format and parse dates.

-- Format timestamp to string
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM orders;
SELECT TO_CHAR(NOW(), 'Day, Month DD, YYYY') AS formatted_date;
SELECT TO_CHAR(NOW(), 'IYYY-IW') AS iso_week;

-- Common format patterns
-- YYYY: 4-digit year
-- MM: 2-digit month
-- DD: 2-digit day
-- HH24: 24-hour format hour
-- MI: minutes
-- SS: seconds
-- Day: full day name
-- Mon: abbreviated month name

-- Parse string to timestamp
SELECT TO_TIMESTAMP('2025-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

-- Parse string to date
SELECT TO_DATE('2025-12-31', 'YYYY-MM-DD');

MAKE_DATE / MAKE_TIME / MAKE_TIMESTAMP¶

Construct date/time values from components.

-- Create date from components
SELECT MAKE_DATE(2025, 12, 31);

-- Create time from components
SELECT MAKE_TIME(14, 30, 45.5);

-- Create timestamp from components
SELECT MAKE_TIMESTAMP(2025, 12, 31, 23, 59, 59.999);

-- Create timestamp with timezone
SELECT MAKE_TIMESTAMPTZ(2025, 12, 31, 23, 59, 59.999, 'America/New_York');

AT TIME ZONE¶

Convert between time zones.

-- Convert to specific timezone
SELECT created_at AT TIME ZONE 'America/New_York' FROM orders;

-- Convert from UTC to local
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London' FROM events;

-- Store with timezone awareness
SELECT NOW() AT TIME ZONE 'UTC';

Date Series Generation¶

-- Generate date series
SELECT generate_series('2025-01-01'::date, '2025-12-31'::date, '1 day'::interval) AS date;

-- Generate month series
SELECT generate_series('2025-01-01'::date, '2025-12-01'::date, '1 month'::interval) AS month_start;

-- Generate hourly series
SELECT generate_series(
    '2025-01-01 00:00:00'::timestamp,
    '2025-01-01 23:00:00'::timestamp,
    '1 hour'::interval
) AS hour;

Mathematical Functions¶

Functions for numeric calculations.

Basic Math¶

-- Absolute value
SELECT ABS(-42);  -- 42

-- Ceiling and floor
SELECT CEIL(4.3);   -- 5
SELECT FLOOR(4.8);  -- 4

-- Round
SELECT ROUND(4.567);       -- 5
SELECT ROUND(4.567, 2);    -- 4.57
SELECT ROUND(4.565, 2);    -- 4.57 (banker's rounding)

-- Truncate
SELECT TRUNC(4.567);       -- 4
SELECT TRUNC(4.567, 2);    -- 4.56

-- Sign
SELECT SIGN(-5);   -- -1
SELECT SIGN(0);    -- 0
SELECT SIGN(5);    -- 1

Power and Roots¶

-- Power
SELECT POWER(2, 10);  -- 1024
SELECT 2 ^ 10;        -- 1024

-- Square root
SELECT SQRT(16);      -- 4

-- Cube root
SELECT CBRT(27);      -- 3

-- Nth root
SELECT POWER(64, 1.0/3);  -- ~4 (cube root)

-- Exponential
SELECT EXP(1);        -- ~2.718 (e)
SELECT EXP(2);        -- ~7.389 (e^2)

Logarithms¶

-- Natural logarithm
SELECT LN(10);        -- ~2.303

-- Base-10 logarithm
SELECT LOG(100);      -- 2
SELECT LOG10(100);    -- 2

-- Custom base logarithm
SELECT LOG(2, 8);     -- 3 (log base 2 of 8)

Trigonometric Functions¶

-- Basic trig
SELECT SIN(PI() / 2);   -- 1
SELECT COS(0);          -- 1
SELECT TAN(PI() / 4);   -- ~1

-- Inverse trig
SELECT ASIN(1);         -- PI/2
SELECT ACOS(0);         -- PI/2
SELECT ATAN(1);         -- PI/4

-- Two-argument arctangent
SELECT ATAN2(1, 1);     -- PI/4

-- Degrees/radians conversion
SELECT DEGREES(PI());   -- 180
SELECT RADIANS(180);    -- PI

Modulo and Division¶

-- Modulo
SELECT MOD(17, 5);      -- 2
SELECT 17 % 5;          -- 2

-- Integer division
SELECT DIV(17, 5);      -- 3
SELECT 17 / 5;          -- 3 (integer division when both operands are integers)
SELECT 17.0 / 5;        -- 3.4 (floating point division)

Random Numbers¶

-- Random number between 0 and 1
SELECT RANDOM();

-- Random integer in range
SELECT FLOOR(RANDOM() * 100 + 1)::int AS random_1_to_100;

-- Set random seed (for reproducibility)
SELECT SETSEED(0.5);
SELECT RANDOM();  -- Always returns same value after setseed

Greatest and Least¶

-- Find greatest value
SELECT GREATEST(1, 5, 3, 9, 2);  -- 9

-- Find least value
SELECT LEAST(1, 5, 3, 9, 2);     -- 1

-- Practical usage: ensure minimum value
SELECT GREATEST(price * 0.9, 10.00) AS discounted_price FROM products;

-- Practical usage: cap maximum
SELECT LEAST(salary * 1.1, 200000) AS capped_raise FROM employees;

JSON/JSONB Functions¶

Functions for working with JSON and JSONB data types.

JSON Operators¶

-- Extract JSON object field as JSON
SELECT data->'user'->'name' FROM events;

-- Extract JSON object field as text
SELECT data->>'status' FROM events;

-- Extract JSON array element
SELECT data->0 FROM arrays;           -- First element as JSON
SELECT data->>0 FROM arrays;          -- First element as text

-- Extract nested path
SELECT data#>'{user,address,city}' FROM events;      -- As JSON
SELECT data#>>'{user,address,city}' FROM events;     -- As text

JSON Path Queries¶

-- Check if path exists
SELECT data ? 'status' FROM events;

-- Check if any key exists
SELECT data ?| ARRAY['status', 'error'] FROM events;

-- Check if all keys exist
SELECT data ?& ARRAY['id', 'name', 'email'] FROM events;

-- Containment
SELECT data @> '{"status": "active"}' FROM events;
SELECT '{"status": "active"}' <@ data FROM events;

JSON Construction¶

-- Build JSON object
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'email', email,
    'created_at', created_at
) FROM users;

-- Build JSON array
SELECT jsonb_build_array(1, 2, 3, 'four');

-- Aggregate to JSON object
SELECT jsonb_object_agg(key, value) FROM settings;

-- Convert row to JSON
SELECT row_to_json(u) FROM users u;
SELECT to_jsonb(u) FROM users u;

JSON Modification¶

-- Set value at path
SELECT jsonb_set(data, '{status}', '"inactive"') FROM events;

-- Set nested value
SELECT jsonb_set(data, '{user,verified}', 'true') FROM events;

-- Insert if key doesn't exist
SELECT jsonb_set(data, '{new_field}', '"value"', true) FROM events;

-- Delete key
SELECT data - 'temporary_field' FROM events;

-- Delete nested key
SELECT data #- '{user,password}' FROM events;

-- Delete array element
SELECT data - 0 FROM arrays;  -- Remove first element

-- Concatenate/merge
SELECT data || '{"updated_at": "2025-01-04"}' FROM events;

JSON Querying¶

-- Extract all keys
SELECT jsonb_object_keys(data) FROM events;

-- Extract all array elements
SELECT jsonb_array_elements(data->'items') FROM events;
SELECT jsonb_array_elements_text(data->'tags') FROM events;

-- Expand to record
SELECT * FROM jsonb_each(data) FROM events;
SELECT * FROM jsonb_each_text(data) FROM events;

-- Get array length
SELECT jsonb_array_length(data->'items') FROM events;

-- Get type
SELECT jsonb_typeof(data->'value') FROM events;

-- Pretty print
SELECT jsonb_pretty(data) FROM events;

-- Strip nulls
SELECT jsonb_strip_nulls(data) FROM events;

JSON Path Expression (SQL/JSON)¶

-- Check if path exists
SELECT jsonb_path_exists(data, '$.user.email') FROM events;

-- Query with path expression
SELECT jsonb_path_query(data, '$.items[*].price') FROM events;

-- Query with filter
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 100)') FROM events;

-- Query with variables
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > $min)', '{"min": 50}') FROM events;

-- Query first match
SELECT jsonb_path_query_first(data, '$.items[*].name') FROM events;

Array Functions¶

Functions for working with array data types.

Array Construction¶

-- Array literal
SELECT ARRAY[1, 2, 3, 4, 5];
SELECT ARRAY['a', 'b', 'c'];

-- Array from query
SELECT ARRAY(SELECT id FROM users WHERE status = 'active');

-- Generate series to array
SELECT ARRAY(SELECT generate_series(1, 10));

Array Access¶

-- Get element (1-indexed)
SELECT arr[1] FROM (SELECT ARRAY[10, 20, 30] AS arr) t;

-- Get slice
SELECT arr[2:4] FROM (SELECT ARRAY[10, 20, 30, 40, 50] AS arr) t;

-- Array length
SELECT array_length(ARRAY[1, 2, 3], 1);  -- 3
SELECT cardinality(ARRAY[1, 2, 3]);       -- 3

Array Operations¶

-- Concatenate arrays
SELECT ARRAY[1, 2] || ARRAY[3, 4];  -- {1,2,3,4}

-- Append element
SELECT ARRAY[1, 2, 3] || 4;  -- {1,2,3,4}

-- Prepend element
SELECT 0 || ARRAY[1, 2, 3];  -- {0,1,2,3}

-- Check containment
SELECT ARRAY[1, 2, 3] @> ARRAY[2, 3];  -- true
SELECT ARRAY[2, 3] <@ ARRAY[1, 2, 3];  -- true

-- Check overlap
SELECT ARRAY[1, 2, 3] && ARRAY[3, 4, 5];  -- true (both contain 3)

-- Check membership
SELECT 2 = ANY(ARRAY[1, 2, 3]);  -- true
SELECT 5 = ALL(ARRAY[5, 5, 5]);  -- true

Array Functions¶

-- Expand array to rows
SELECT unnest(ARRAY[1, 2, 3]) AS value;

-- Array position
SELECT array_position(ARRAY['a', 'b', 'c'], 'b');  -- 2

-- Array positions (all occurrences)
SELECT array_positions(ARRAY[1, 2, 1, 3, 1], 1);  -- {1,3,5}

-- Remove element
SELECT array_remove(ARRAY[1, 2, 3, 2], 2);  -- {1,3}

-- Replace element
SELECT array_replace(ARRAY[1, 2, 3, 2], 2, 9);  -- {1,9,3,9}

-- Array to string
SELECT array_to_string(ARRAY[1, 2, 3], ', ');  -- '1, 2, 3'

-- String to array
SELECT string_to_array('1,2,3', ',');  -- {1,2,3}

-- Array dimensions
SELECT array_dims(ARRAY[[1,2],[3,4]]);  -- '[1:2][1:2]'

Vector Functions¶

HeliosDB-specific functions for vector similarity search and operations.

Vector Distance Operators¶

-- Cosine distance (0 = identical, 2 = opposite)
SELECT embedding <=> query_embedding FROM documents;

-- Euclidean distance (L2)
SELECT embedding <-> query_embedding FROM documents;

-- Inner product (negative, for similarity ranking)
SELECT embedding <#> query_embedding FROM documents;

Vector Similarity Search¶

-- Find most similar documents (cosine)
SELECT id, title, embedding <=> '[0.1, 0.2, 0.3, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, 0.3, ...]'::vector
LIMIT 10;

-- Find nearest neighbors (L2)
SELECT id, title
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3, ...]'::vector
LIMIT 10;

-- Hybrid search with filters
SELECT id, title, embedding <=> query_vec AS distance
FROM documents
WHERE category = 'technology' AND published = true
ORDER BY embedding <=> query_vec
LIMIT 10;

Vector Functions¶

-- Get vector dimensions
SELECT vector_dims(embedding) FROM documents LIMIT 1;

-- Get vector norm (magnitude)
SELECT vector_norm(embedding) FROM documents;

-- Normalize vector to unit length
SELECT vector_normalize(embedding) FROM documents;

-- Average of vectors
SELECT AVG(embedding) FROM documents WHERE category = 'science';

-- Sum of vectors
SELECT SUM(embedding) FROM documents WHERE topic = 'AI';

-- Cosine similarity (1 - cosine_distance)
SELECT 1 - (embedding <=> query_vec) AS similarity
FROM documents;

Vector Index Hints¶

-- Set search parameter for HNSW
SET hnsw.ef_search = 100;

-- Query with hint
SELECT /*+ HNSW_EF_SEARCH(200) */ id, title
FROM documents
ORDER BY embedding <=> query_vec
LIMIT 10;

Window Functions¶

Functions that operate over a window of rows.

Ranking Functions¶

-- Row number (unique sequential)
SELECT id, name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM leaderboard;

-- Rank (same rank for ties, gaps after)
SELECT id, name, score,
       RANK() OVER (ORDER BY score DESC) AS rank
FROM leaderboard;

-- Dense rank (same rank for ties, no gaps)
SELECT id, name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM leaderboard;

-- Percent rank (relative rank as percentage)
SELECT id, name, score,
       PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rank
FROM leaderboard;

-- Ntile (divide into N buckets)
SELECT id, name, score,
       NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM leaderboard;

PARTITION BY¶

-- Rank within groups
SELECT department, employee_name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Running total per group
SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

LAG / LEAD¶

Access values from other rows.

-- Previous row value
SELECT date, value,
       LAG(value) OVER (ORDER BY date) AS prev_value,
       value - LAG(value) OVER (ORDER BY date) AS change
FROM daily_metrics;

-- Next row value
SELECT date, value,
       LEAD(value) OVER (ORDER BY date) AS next_value
FROM daily_metrics;

-- Offset and default
SELECT date, value,
       LAG(value, 7, 0) OVER (ORDER BY date) AS value_7_days_ago
FROM daily_metrics;

FIRST_VALUE / LAST_VALUE / NTH_VALUE¶

-- First value in window
SELECT date, value,
       FIRST_VALUE(value) OVER (ORDER BY date) AS first_value
FROM daily_metrics;

-- Last value in window (requires frame specification)
SELECT date, value,
       LAST_VALUE(value) OVER (
           ORDER BY date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_value
FROM daily_metrics;

-- Nth value
SELECT date, value,
       NTH_VALUE(value, 3) OVER (ORDER BY date) AS third_value
FROM daily_metrics;

Frame Specifications¶

-- Running sum
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) AS running_sum
FROM transactions;

-- Moving average (7-day window)
SELECT date, value,
       AVG(value) OVER (
           ORDER BY date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7
FROM daily_metrics;

-- Centered moving average
SELECT date, value,
       AVG(value) OVER (
           ORDER BY date
           ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
       ) AS centered_avg
FROM daily_metrics;

-- Range-based window (all rows within 7 days)
SELECT date, value,
       AVG(value) OVER (
           ORDER BY date
           RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
       ) AS rolling_7_day_avg
FROM daily_metrics;

Named Windows¶

-- Define reusable window
SELECT date, value,
       SUM(value) OVER w AS running_sum,
       AVG(value) OVER w AS running_avg,
       MIN(value) OVER w AS running_min,
       MAX(value) OVER w AS running_max
FROM daily_metrics
WINDOW w AS (ORDER BY date);

Analytical Functions¶

Advanced analytical and statistical functions.

CUME_DIST¶

Cumulative distribution.

-- Cumulative distribution (fraction of rows with value <= current)
SELECT name, score,
       CUME_DIST() OVER (ORDER BY score) AS cumulative_dist
FROM exam_results;

GROUPING SETS / CUBE / ROLLUP¶

Multi-level aggregations.

-- Grouping sets (specific grouping combinations)
SELECT category, subcategory, SUM(sales)
FROM products
GROUP BY GROUPING SETS (
    (category, subcategory),
    (category),
    ()
);

-- Rollup (hierarchical groupings)
SELECT year, quarter, month, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, quarter, month);

-- Cube (all possible grouping combinations)
SELECT region, product, channel, SUM(sales)
FROM orders
GROUP BY CUBE(region, product, channel);

-- Identify grouping level
SELECT
    CASE WHEN GROUPING(category) = 1 THEN 'Total' ELSE category END AS category,
    SUM(sales)
FROM products
GROUP BY ROLLUP(category);

Time-Series Analysis¶

-- Year-over-year comparison
SELECT
    DATE_TRUNC('month', date) AS month,
    SUM(revenue) AS revenue,
    LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', date)) AS revenue_ly,
    ROUND(100.0 * (SUM(revenue) - LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', date)))
        / NULLIF(LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', date)), 0), 2) AS yoy_pct
FROM daily_sales
GROUP BY DATE_TRUNC('month', date);

-- Moving average with trend
WITH monthly_data AS (
    SELECT DATE_TRUNC('month', date) AS month, SUM(value) AS total
    FROM metrics
    GROUP BY DATE_TRUNC('month', date)
)
SELECT month, total,
       AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3m,
       AVG(total) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS ma_12m
FROM monthly_data;

Mode¶

Find the most frequent value.

-- Mode (most frequent value)
SELECT MODE() WITHIN GROUP (ORDER BY category) AS most_common_category
FROM products;

Conditional Functions¶

Functions for conditional logic.

CASE¶

-- Simple CASE
SELECT name,
    CASE status
        WHEN 'active' THEN 'Active'
        WHEN 'inactive' THEN 'Inactive'
        WHEN 'pending' THEN 'Pending'
        ELSE 'Unknown'
    END AS status_label
FROM users;

-- Searched CASE
SELECT name, age,
    CASE
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 64 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- CASE in aggregation
SELECT
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM users;

COALESCE¶

Return first non-null value.

-- Default value for null
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name FROM users;

-- Null handling in calculations
SELECT price * COALESCE(quantity, 1) AS total FROM order_items;

NULLIF¶

Return null if values are equal.

-- Avoid division by zero
SELECT revenue / NULLIF(expenses, 0) AS margin FROM financials;

-- Convert empty string to null
SELECT NULLIF(TRIM(phone), '') AS phone FROM contacts;

IIF (HeliosDB Extension)¶

Inline if-then-else.

-- Simple conditional
SELECT name, IIF(age >= 18, 'Adult', 'Minor') AS age_category FROM users;

Type Conversion Functions¶

Functions for converting between data types.

CAST¶

-- Standard CAST syntax
SELECT CAST(price AS INTEGER) FROM products;
SELECT CAST('2025-01-04' AS DATE);
SELECT CAST(123 AS VARCHAR);

-- PostgreSQL shorthand
SELECT price::INTEGER FROM products;
SELECT '2025-01-04'::DATE;
SELECT 123::TEXT;

Specific Conversion Functions¶

-- To integer
SELECT TO_NUMBER('12345', '99999');

-- To text with formatting
SELECT TO_CHAR(1234567.89, 'FM$9,999,999.00');  -- '$1,234,567.89'
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

-- To timestamp
SELECT TO_TIMESTAMP('2025-01-04 12:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- To date
SELECT TO_DATE('2025-01-04', 'YYYY-MM-DD');

System Functions¶

Functions for system information and administration.

Current User and Session¶

-- Current user
SELECT CURRENT_USER;
SELECT SESSION_USER;

-- Current database
SELECT CURRENT_DATABASE();
SELECT CURRENT_CATALOG;

-- Current schema
SELECT CURRENT_SCHEMA;
SELECT CURRENT_SCHEMAS(true);  -- Include implicit schemas

-- Transaction ID
SELECT txid_current();

-- Process ID
SELECT pg_backend_pid();

Object Information¶

-- Table size
SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_table_size('users'));
SELECT pg_size_pretty(pg_indexes_size('users'));

-- Column type
SELECT pg_typeof(column_name) FROM table_name LIMIT 1;

-- Check if table exists
SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_name = 'users'
);

UUID Generation¶

-- Generate UUID v4
SELECT gen_random_uuid();

-- UUID from string
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;

Hashing¶

-- MD5 hash
SELECT MD5('hello world');

-- SHA-256 hash
SELECT encode(sha256('hello world'::bytea), 'hex');

-- Password hashing
SELECT crypt('password', gen_salt('bf'));