SQL Support in Mach5

Mach5 SQL lets you query Mach5 data with familiar SQL and connect through PostgreSQL-compatible clients. Use SQL when you want to filter, aggregate, join, sort, transform, and analyze data with standard SQL expressions while still using Mach5 search features where appropriate.

This page applies to the unreleased next version of Mach5.

Connecting with PostgreSQL-compatible clients

Mach5 exposes a PostgreSQL-compatible endpoint for SQL clients. You can use tools such as psql, JDBC clients, BI tools, notebooks, and applications that speak the PostgreSQL wire protocol.

Connection fields

The warehouse is required for normal SQL queries. The database selects the Mach5 namespace; the warehouse selects the compute resource within that namespace.

psql examples

Connect to namespace default and warehouse it-wh:

psql "postgresql://mach5.example.com:5432/default?options=-cwarehouse=it-wh" \
  --username alice

If your password is required, psql prompts for it. You can also use standard PostgreSQL environment variables such as PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD.

export PGHOST=mach5.example.com
export PGPORT=5432
export PGDATABASE=default
export PGUSER=alice
psql "options=-cwarehouse=it-wh"

Change the target warehouse after connecting:

SET warehouse = 'analytics-wh';

Set a PostgreSQL search path for client compatibility:

SET search_path = public;

JDBC example

Use the PostgreSQL JDBC driver and pass the warehouse through the options connection property.

jdbc:postgresql://mach5.example.com:5432/default?options=-cwarehouse%3Dit-wh

Java example:

Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", "secret");
props.setProperty("options", "-cwarehouse=it-wh");

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://mach5.example.com:5432/default",
    props
);

Python example

Most PostgreSQL Python clients can connect by using the PostgreSQL connection string.

import psycopg

conn = psycopg.connect(
    "postgresql://alice:secret@mach5.example.com:5432/default?options=-cwarehouse%3Dit-wh"
)

with conn.cursor() as cur:
    cur.execute("SELECT service, COUNT(*) FROM logs GROUP BY service")
    for row in cur.fetchall():
        print(row)

Prepared statements and parameters

PostgreSQL clients can use prepared statements and text parameters.

PREPARE errors_by_service(text, int) AS
SELECT service, COUNT(*) AS errors
FROM logs
WHERE service = $1 AND status >= $2
GROUP BY service;

EXECUTE errors_by_service('api', 500);

Querying data

A typical query selects from a Mach5 index or table, filters rows, and returns results:

SELECT timestamp, service, level, message
FROM logs
WHERE level = 'error'
ORDER BY timestamp DESC
LIMIT 100;

Aggregate data with GROUP BY:

SELECT service, COUNT(*) AS events
FROM logs
WHERE timestamp >= NOW() - INTERVAL '1 hour'
GROUP BY service
ORDER BY events DESC;

Use SQL expressions in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY:

SELECT LOWER(service) AS service_name, AVG(duration_ms) AS avg_duration_ms
FROM logs
WHERE status >= 500
GROUP BY LOWER(service)
HAVING AVG(duration_ms) > 100
ORDER BY avg_duration_ms DESC;

SQL search predicates

Mach5 SQL includes search predicates for indexed search behavior. Use these functions in WHERE clauses when you want full-text, term, prefix, wildcard, query-string, field-existence, or relevance-scored search.

Use ordinary SQL predicates such as =, <, >, LIKE, and regular expression operators when you want SQL comparison semantics over stored column values.

Search scoring

SCORE() returns the relevance score for rows matched by a SQL search predicate.

SELECT id, message, SCORE() AS relevance
FROM logs
WHERE MATCH(message, 'connection refused')
ORDER BY SCORE() DESC
LIMIT 10;

Search function reference

TERM

Signature: TERM(field, value) -> BOOLEAN

Matches documents where field contains the exact term represented by value. Use this for exact keyword, token, numeric, date, boolean, or IP value lookup.

SELECT id, message
FROM logs
WHERE TERM(service, 'api');

TERMS

Signature: TERMS(field, value [, ...]) -> BOOLEAN

Matches documents where field contains any one of the supplied exact values.

SELECT id, service
FROM logs
WHERE TERMS(service, 'api', 'worker', 'scheduler');

PREFIX

Signature: PREFIX(field, prefix) -> BOOLEAN

Matches terms in field that start with prefix. Use this for prefix searches on text, keyword, or IP fields.

SELECT id, host
FROM logs
WHERE PREFIX(host, 'web-');

WILDCARD

Signature: WILDCARD(field, pattern) -> BOOLEAN

Matches terms in field with a wildcard pattern. Use * for any sequence of characters and ? for a single character.

SELECT id, path
FROM logs
WHERE WILDCARD(path, '/api/*/login');

REGEXP

Signature: REGEXP(field, pattern) -> BOOLEAN

Matches text or keyword terms in field with a regular expression pattern.

SELECT id, user_agent
FROM logs
WHERE REGEXP(user_agent, '.*Firefox/[0-9]+.*');

FUZZY

Signature: FUZZY(field, value) -> BOOLEAN

Matches terms in field that are similar to value. Use fuzzy search for typo-tolerant term matching.

SELECT id, message
FROM logs
WHERE FUZZY(message, 'authentcation');

MATCH

Signature: MATCH(field, query) -> BOOLEAN

Runs an analyzed full-text match query against field.

SELECT id, message, SCORE() AS relevance
FROM logs
WHERE MATCH(message, 'authentication failure')
ORDER BY SCORE() DESC;

MATCH_PHRASE

Signature: MATCH_PHRASE(field, query) -> BOOLEAN

Matches query as a phrase in field.

SELECT id, message
FROM logs
WHERE MATCH_PHRASE(message, 'connection refused');

MATCH_PHRASE_PREFIX

Signature: MATCH_PHRASE_PREFIX(field, query) -> BOOLEAN

Matches a phrase where the last term is treated as a prefix.

SELECT id, message
FROM logs
WHERE MATCH_PHRASE_PREFIX(message, 'failed log');

MULTI_MATCH

Signature: MULTI_MATCH(query, field [, ...]) -> BOOLEAN

Runs an analyzed full-text match query across multiple fields. A row matches when the query matches at least one listed field.

SELECT id, title, message
FROM logs
WHERE MULTI_MATCH('timeout error', title, message, exception);

QUERY

Signature: QUERY(query_string) -> BOOLEAN

Runs a Lucene/OpenSearch-style query-string search. Use scoped fields such as field:value, boolean operators such as AND and OR, grouping, and * field expansion.

SELECT id, service, level
FROM logs
WHERE QUERY('service:api AND level:error');

Search all eligible fields:

SELECT id, message
FROM logs
WHERE QUERY('*:timeout');

EXISTS_FIELD

Signature: EXISTS_FIELD(field) -> BOOLEAN

Matches rows where field is present.

SELECT id
FROM logs
WHERE EXISTS_FIELD(trace_id);

SPAN_NEAR

Signature: SPAN_NEAR(field, term1, term2, slop, in_order) -> BOOLEAN

Matches rows where two terms occur near each other in field. slop controls how far apart the terms may be. in_order controls whether the terms must appear in the supplied order.

SELECT id, message
FROM logs
WHERE SPAN_NEAR(message, 'connection', 'refused', 3, true);

BOOST

Signature: BOOST(predicate, factor) -> BOOLEAN

Applies a relevance boost to a search predicate. Use this when one part of a search condition should contribute more strongly to SCORE().

SELECT id, message, SCORE() AS relevance
FROM logs
WHERE BOOST(MATCH(message, 'critical failure'), 2.0)
   OR MATCH(message, 'failure')
ORDER BY SCORE() DESC;

CONSTANT_SCORE

Signature: CONSTANT_SCORE(predicate, score) -> BOOLEAN

Assigns a constant relevance score to rows matched by a search predicate.

SELECT id, message, SCORE() AS relevance
FROM logs
WHERE CONSTANT_SCORE(TERM(level, 'error'), 1.0)
ORDER BY SCORE() DESC;

SCORE

Signature: SCORE() -> DOUBLE

Returns the relevance score for the current row when the query uses a search predicate.

SELECT id, message, SCORE() AS relevance
FROM logs
WHERE MATCH(message, 'database timeout')
ORDER BY SCORE() DESC
LIMIT 20;

Function reference

The following sections list SQL functions available in Mach5. Function names are case-insensitive.

String functions

LOWER

Signature: LOWER(s STRING) -> STRING

Converts s to lowercase.

SELECT LOWER('Mach5 Search') AS value;
-- mach5 search

UPPER

Signature: UPPER(s STRING) -> STRING

Converts s to uppercase.

SELECT UPPER('Mach5 Search') AS value;
-- MACH5 SEARCH

LENGTH

Signature: LENGTH(s STRING) -> INT

Returns the number of characters in s.

SELECT LENGTH('mach5') AS value;
-- 5

CHAR_LENGTH

Signature: CHAR_LENGTH(s STRING) -> INT

Returns the number of characters in s.

SELECT CHAR_LENGTH('mach5') AS value;
-- 5

CHARACTER_LENGTH

Signature: CHARACTER_LENGTH(s STRING) -> INT

Returns the number of characters in s.

SELECT CHARACTER_LENGTH('mach5') AS value;
-- 5

OCTET_LENGTH

Signature: OCTET_LENGTH(s STRING) -> INT

Returns the number of bytes used to represent s.

SELECT OCTET_LENGTH('mach5') AS value;
-- 5

SUBSTRING

Signature: SUBSTRING(s STRING, start INT, len INT) -> STRING

Signature: SUBSTRING(s STRING FROM start INT FOR len INT) -> STRING

Returns a substring using SQL 1-based character positions.

SELECT SUBSTRING('abcdef', 2, 3) AS value;
-- bcd

SELECT SUBSTRING('abcdef' FROM 2 FOR 3) AS value;
-- bcd

CONCAT

Signature: CONCAT(value STRING [, ...]) -> STRING

Concatenates all arguments into one string.

SELECT CONCAT('mach', '5', '-', 'sql') AS value;
-- mach5-sql

CONCAT_WS

Signature: CONCAT_WS(separator STRING, value STRING [, ...]) -> STRING

Concatenates values with separator between non-null inputs. A null separator returns null.

SELECT CONCAT_WS('/', 'api', 'v1', 'users') AS value;
-- api/v1/users

LEFT

Signature: LEFT(s STRING, n INT) -> STRING

Returns the leftmost n characters from s.

SELECT LEFT('abcdef', 3) AS value;
-- abc

RIGHT

Signature: RIGHT(s STRING, n INT) -> STRING

Returns the rightmost n characters from s.

SELECT RIGHT('abcdef', 3) AS value;
-- def

LPAD

Signature: LPAD(s STRING, len INT [, fill STRING]) -> STRING

Pads the left side of s until the result is len characters long. fill supplies the padding text.

SELECT LPAD('42', 5, '0') AS value;
-- 00042

RPAD

Signature: RPAD(s STRING, len INT [, fill STRING]) -> STRING

Pads the right side of s until the result is len characters long. fill supplies the padding text.

SELECT RPAD('api', 6, '.') AS value;
-- api...

LTRIM

Signature: LTRIM(s STRING) -> STRING

Removes leading whitespace from s.

SELECT LTRIM('  api') AS value;
-- api

RTRIM

Signature: RTRIM(s STRING) -> STRING

Removes trailing whitespace from s.

SELECT RTRIM('api  ') AS value;
-- api

TRIM

Signature: TRIM(s STRING) -> STRING

Removes leading and trailing whitespace from s.

SELECT TRIM('  api  ') AS value;
-- api

BTRIM

Signature: BTRIM(s STRING, chars STRING) -> STRING

Removes leading and trailing characters from s when those characters appear in chars.

SELECT BTRIM('---api---', '-') AS value;
-- api

POSITION

Signature: POSITION(sub STRING IN s STRING) -> INT

Returns the SQL 1-based position of sub in s, or 0 when it is not found.

SELECT POSITION('cd' IN 'abcdef') AS value;
-- 3

STRPOS

Signature: STRPOS(s STRING, sub STRING) -> INT

Returns the SQL 1-based position of sub in s, or 0 when it is not found.

SELECT STRPOS('abcdef', 'cd') AS value;
-- 3

REPLACE

Signature: REPLACE(s STRING, from STRING, to STRING) -> STRING

Replaces all occurrences of from in s with to.

SELECT REPLACE('api-error-error', 'error', 'warn') AS value;
-- api-warn-warn

TRANSLATE

Signature: TRANSLATE(s STRING, from STRING, to STRING) -> STRING

Replaces individual characters in s by mapping characters from from to the corresponding characters in to.

SELECT TRANSLATE('abc123', 'abc', 'xyz') AS value;
-- xyz123

REVERSE

Signature: REVERSE(s STRING) -> STRING

Returns s with characters in reverse order.

SELECT REVERSE('abc') AS value;
-- cba

REPEAT

Signature: REPEAT(s STRING, n INT) -> STRING

Repeats s n times.

SELECT REPEAT('ha', 3) AS value;
-- hahaha

SPLIT_PART

Signature: SPLIT_PART(s STRING, delim STRING, n INT) -> STRING

Splits s by delim and returns the nth field using SQL 1-based positions.

SELECT SPLIT_PART('a/b/c', '/', 2) AS value;
-- b

ASCII

Signature: ASCII(s STRING) -> INT

Returns the character code of the first character in s.

SELECT ASCII('A') AS value;
-- 65

CHR

Signature: CHR(n INT) -> STRING

Returns the character with character code n.

SELECT CHR(65) AS value;
-- A

INITCAP

Signature: INITCAP(s STRING) -> STRING

Capitalizes the first letter of each word in s and lowercases the remaining letters.

SELECT INITCAP('mach5 search') AS value;
-- Mach5 Search

OVERLAY

Signature: OVERLAY(s STRING PLACING replacement STRING FROM start INT FOR count INT) -> STRING

Replaces count characters in s starting at SQL 1-based position start with replacement.

SELECT OVERLAY('abcdef' PLACING 'ZZ' FROM 3 FOR 2) AS value;
-- abZZef

STARTS_WITH

Signature: STARTS_WITH(s STRING, prefix STRING) -> BOOLEAN

Returns true when s starts with prefix.

SELECT STARTS_WITH('api-server', 'api') AS value;
-- true

ENDS_WITH

Signature: ENDS_WITH(s STRING, suffix STRING) -> BOOLEAN

Returns true when s ends with suffix.

SELECT ENDS_WITH('api-server', 'server') AS value;
-- true

REGEXP_MATCH

Signature: REGEXP_MATCH(s STRING, pattern STRING) -> STRING[]

Returns the captured substrings for the first match of pattern in s.

SELECT REGEXP_MATCH('status=500', 'status=([0-9]+)') AS value;
-- {500}

REGEXP_MATCHES

Signature: REGEXP_MATCHES(s STRING, pattern STRING, flags STRING) -> SETOF STRING[]

Returns captured substrings for each match of pattern in s. Use flags to control regular-expression behavior.

SELECT REGEXP_MATCHES('a1 b2', '([a-z])([0-9])', 'g') AS value;
-- {a,1}
-- {b,2}

REGEXP_REPLACE

Signature: REGEXP_REPLACE(s STRING, pattern STRING, replacement STRING) -> STRING

Replaces text matching pattern with replacement.

SELECT REGEXP_REPLACE('status=500', '[0-9]+', 'XXX') AS value;
-- status=XXX

REGEXP_SPLIT_TO_ARRAY

Signature: REGEXP_SPLIT_TO_ARRAY(s STRING, pattern STRING) -> STRING[]

Splits s wherever pattern matches and returns an array of fields.

SELECT REGEXP_SPLIT_TO_ARRAY('api, worker; scheduler', '[,;]\\s*') AS value;
-- {api,worker,scheduler}

FORMAT

Signature: FORMAT(format STRING, value ANY [, ...]) -> STRING

Formats values into a string using PostgreSQL-style format specifiers.

SELECT FORMAT('service=%s status=%s', 'api', 500) AS value;
-- service=api status=500

QUOTE_IDENT

Signature: QUOTE_IDENT(s STRING) -> STRING

Quotes s as a SQL identifier when quoting is required.

SELECT QUOTE_IDENT('select') AS value;
-- "select"

QUOTE_LITERAL

Signature: QUOTE_LITERAL(s STRING) -> STRING

Quotes s as a SQL string literal.

SELECT QUOTE_LITERAL('api') AS value;
-- 'api'

ENCODE

Signature: ENCODE(data BYTES, format STRING) -> STRING

Encodes bytes as text. Supported formats include base64 and hex.

SELECT ENCODE(DECODE('6d61636835', 'hex'), 'base64') AS value;
-- bWFjaDU=

DECODE

Signature: DECODE(s STRING, format STRING) -> BYTES

Decodes text into bytes. Supported formats include base64 and hex.

SELECT DECODE('6d61636835', 'hex') AS value;

MD5

Signature: MD5(s STRING) -> STRING

Returns the MD5 hash of s as hexadecimal text.

SELECT MD5('mach5') AS value;

SHA256

Signature: SHA256(s STRING) -> STRING

Returns the SHA-256 hash of s as hexadecimal text.

SELECT SHA256('mach5') AS value;

Numeric and math functions

ABS

Signature: ABS(x NUMERIC) -> NUMERIC

Returns the absolute value of x.

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

CEIL

Signature: CEIL(x NUMERIC) -> NUMERIC

Returns the smallest integer value greater than or equal to x.

SELECT CEIL(4.2) AS value;
-- 5

CEILING

Signature: CEILING(x NUMERIC) -> NUMERIC

Returns the smallest integer value greater than or equal to x.

SELECT CEILING(4.2) AS value;
-- 5

FLOOR

Signature: FLOOR(x NUMERIC) -> NUMERIC

Returns the largest integer value less than or equal to x.

SELECT FLOOR(4.8) AS value;
-- 4

ROUND

Signature: ROUND(x NUMERIC [, digits INT]) -> NUMERIC

Rounds x to the nearest value. When digits is supplied, rounds to that number of decimal places.

SELECT ROUND(123.456, 2) AS value;
-- 123.46

TRUNC

Signature: TRUNC(x NUMERIC [, digits INT]) -> NUMERIC

Truncates x toward zero. When digits is supplied, keeps that number of decimal places.

SELECT TRUNC(123.456, 2) AS value;
-- 123.45

TRUNCATE

Signature: TRUNCATE(x NUMERIC [, digits INT]) -> NUMERIC

Truncates x toward zero. When digits is supplied, keeps that number of decimal places.

SELECT TRUNCATE(123.456, 2) AS value;
-- 123.45

MOD

Signature: MOD(a NUMERIC, b NUMERIC) -> NUMERIC

Returns the remainder after dividing a by b.

SELECT MOD(10, 3) AS value;
-- 1

POWER

Signature: POWER(x NUMERIC, y NUMERIC) -> DOUBLE

Returns x raised to the power of y.

SELECT POWER(2, 10) AS value;
-- 1024

SQRT

Signature: SQRT(x NUMERIC) -> DOUBLE

Returns the square root of x.

SELECT SQRT(81) AS value;
-- 9

CBRT

Signature: CBRT(x NUMERIC) -> DOUBLE

Returns the cube root of x.

SELECT CBRT(27) AS value;
-- 3

EXP

Signature: EXP(x NUMERIC) -> DOUBLE

Returns Euler’s number raised to the power of x.

SELECT EXP(1) AS value;

LN

Signature: LN(x NUMERIC) -> DOUBLE

Returns the natural logarithm of x.

SELECT LN(10) AS value;

LOG

Signature: LOG(base NUMERIC, x NUMERIC) -> DOUBLE

Returns the logarithm of x in the supplied base.

SELECT LOG(10, 1000) AS value;
-- 3

LOG10

Signature: LOG10(x NUMERIC) -> DOUBLE

Returns the base-10 logarithm of x.

SELECT LOG10(1000) AS value;
-- 3

SIGN

Signature: SIGN(x NUMERIC) -> INT

Returns -1, 0, or 1 depending on whether x is negative, zero, or positive.

SELECT SIGN(-5) AS value;
-- -1

PI

Signature: PI() -> DOUBLE

Returns the mathematical constant pi.

SELECT PI() AS value;

DEGREES

Signature: DEGREES(x NUMERIC) -> DOUBLE

Converts radians to degrees.

SELECT DEGREES(PI()) AS value;
-- 180

RADIANS

Signature: RADIANS(x NUMERIC) -> DOUBLE

Converts degrees to radians.

SELECT RADIANS(180) AS value;

SIN

Signature: SIN(x NUMERIC) -> DOUBLE

Returns the sine of x, where x is in radians.

SELECT SIN(RADIANS(90)) AS value;
-- 1

COS

Signature: COS(x NUMERIC) -> DOUBLE

Returns the cosine of x, where x is in radians.

SELECT COS(0) AS value;
-- 1

TAN

Signature: TAN(x NUMERIC) -> DOUBLE

Returns the tangent of x, where x is in radians.

SELECT TAN(RADIANS(45)) AS value;
-- 1

ASIN

Signature: ASIN(x NUMERIC) -> DOUBLE

Returns the arc sine of x in radians.

SELECT ASIN(1) AS value;

ACOS

Signature: ACOS(x NUMERIC) -> DOUBLE

Returns the arc cosine of x in radians.

SELECT ACOS(1) AS value;
-- 0

ATAN

Signature: ATAN(x NUMERIC) -> DOUBLE

Returns the arc tangent of x in radians.

SELECT ATAN(1) AS value;

ATAN2

Signature: ATAN2(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the angle in radians from the positive x-axis to the point (x, y).

SELECT ATAN2(1, 1) AS value;

RANDOM

Signature: RANDOM() -> DOUBLE

Returns a random value.

SELECT RANDOM() AS value;

GREATEST

Signature: GREATEST(value ANY [, ...]) -> ANY

Returns the greatest non-null value from the supplied arguments using the common comparable type.

SELECT GREATEST(10, 42, 7) AS value;
-- 42

LEAST

Signature: LEAST(value ANY [, ...]) -> ANY

Returns the least non-null value from the supplied arguments using the common comparable type.

SELECT LEAST(10, 42, 7) AS value;
-- 7

WIDTH_BUCKET

Signature: WIDTH_BUCKET(x NUMERIC, min NUMERIC, max NUMERIC, buckets INT) -> INT

Returns the bucket number for x in an equal-width histogram spanning min to max.

SELECT WIDTH_BUCKET(42, 0, 100, 10) AS value;
-- 5

DIV

Signature: DIV(a INT, b INT) -> INT

Returns integer division of a by b.

SELECT DIV(10, 3) AS value;
-- 3

GCD

Signature: GCD(a INT, b INT) -> INT

Returns the greatest common divisor of a and b.

SELECT GCD(24, 18) AS value;
-- 6

LCM

Signature: LCM(a INT, b INT) -> INT

Returns the least common multiple of a and b.

SELECT LCM(6, 8) AS value;
-- 24

FACTORIAL

Signature: FACTORIAL(n INT) -> INT

Returns n!, the product of positive integers from 1 through n.

SELECT FACTORIAL(5) AS value;
-- 120

Conditional and null functions

COALESCE

Signature: COALESCE(value ANY [, ...]) -> ANY

Returns the first non-null value from its arguments.

SELECT COALESCE(NULL, 'fallback') AS value;
-- fallback

NULLIF

Signature: NULLIF(a ANY, b ANY) -> ANY

Returns null when a equals b; otherwise returns a.

SELECT NULLIF(status, 0) AS nonzero_status
FROM logs;

IF

Signature: IF(condition BOOLEAN, then_value ANY, else_value ANY) -> ANY

Returns then_value when condition is true; otherwise returns else_value.

SELECT IF(status >= 500, 'server_error', 'other') AS status_class
FROM logs;

IFNULL

Signature: IFNULL(value ANY, fallback ANY) -> ANY

Returns fallback when value is null; otherwise returns value.

SELECT IFNULL(user_id, 'anonymous') AS user_id
FROM logs;

Date and time functions

NOW

Signature: NOW() -> TIMESTAMP

Returns the current timestamp.

SELECT NOW() AS value;

CURRENT_TIMESTAMP

Signature: CURRENT_TIMESTAMP -> TIMESTAMP

Returns the current timestamp.

SELECT CURRENT_TIMESTAMP AS value;

CURRENT_DATE

Signature: CURRENT_DATE -> DATE

Returns the current date.

SELECT CURRENT_DATE AS value;

CURRENT_TIME

Signature: CURRENT_TIME -> TIME

Returns the current time.

SELECT CURRENT_TIME AS value;

LOCALTIME

Signature: LOCALTIME -> TIME

Returns the current local time.

SELECT LOCALTIME AS value;

LOCALTIMESTAMP

Signature: LOCALTIMESTAMP -> TIMESTAMP

Returns the current local timestamp.

SELECT LOCALTIMESTAMP AS value;

EXTRACT

Signature: EXTRACT(field FROM ts TIMESTAMP) -> NUMERIC

Extracts a date/time field such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or EPOCH from a timestamp.

SELECT EXTRACT(HOUR FROM timestamp) AS hour
FROM logs;

SELECT EXTRACT(EPOCH FROM timestamp) AS epoch_seconds
FROM logs;

DATE_PART

Signature: DATE_PART(field STRING, ts TIMESTAMP) -> NUMERIC

Extracts a date/time field from a timestamp. The field name is supplied as a string.

SELECT DATE_PART('hour', timestamp) AS hour
FROM logs;

DATE_TRUNC

Signature: DATE_TRUNC(precision STRING, ts TIMESTAMP) -> TIMESTAMP

Truncates ts to the requested precision, such as minute, hour, day, or month.

SELECT DATE_TRUNC('hour', timestamp) AS hour_bucket, COUNT(*)
FROM logs
GROUP BY DATE_TRUNC('hour', timestamp);

DATE_ADD

Signature: DATE_ADD(ts TIMESTAMP, interval INTERVAL) -> TIMESTAMP

Adds an interval to a timestamp.

SELECT DATE_ADD(timestamp, INTERVAL '15 minutes') AS plus_15m
FROM logs;

DATE_DIFF

Signature: DATE_DIFF(part STRING, start TIMESTAMP, end TIMESTAMP) -> INT

Returns the difference between two timestamps measured in the requested part.

SELECT DATE_DIFF('minute', start_time, end_time) AS duration_minutes
FROM jobs;

AGE

Signature: AGE(ts1 TIMESTAMP, ts2 TIMESTAMP) -> INTERVAL

Returns the interval between two timestamps.

SELECT AGE(end_time, start_time) AS duration
FROM jobs;

MAKE_DATE

Signature: MAKE_DATE(year INT, month INT, day INT) -> DATE

Builds a date from year, month, and day parts.

SELECT MAKE_DATE(2026, 5, 7) AS value;
-- 2026-05-07

MAKE_TIMESTAMP

Signature: MAKE_TIMESTAMP(year INT, month INT, day INT, hour INT, minute INT, second NUMERIC) -> TIMESTAMP

Builds a timestamp from date and time parts.

SELECT MAKE_TIMESTAMP(2026, 5, 7, 13, 30, 0) AS value;

MAKE_INTERVAL

Signature: MAKE_INTERVAL([years INT] [, months INT] [, weeks INT] [, days INT] [, hours INT] [, mins INT] [, secs NUMERIC]) -> INTERVAL

Builds an interval from named date/time parts.

SELECT MAKE_INTERVAL(days => 1, hours => 2) AS value;

TO_CHAR

Signature: TO_CHAR(value ANY, format STRING) -> STRING

Formats a date, time, timestamp, or numeric value as text.

SELECT TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS formatted_time
FROM logs;

TO_DATE

Signature: TO_DATE(s STRING, format STRING) -> DATE

Parses text into a date using the supplied format.

SELECT TO_DATE('2026-05-07', 'YYYY-MM-DD') AS value;

TO_TIMESTAMP

Signature: TO_TIMESTAMP(s STRING, format STRING) -> TIMESTAMP

Parses text into a timestamp using the supplied format.

SELECT TO_TIMESTAMP('2026-05-07 13:30:00', 'YYYY-MM-DD HH24:MI:SS') AS value;

TO_NUMBER

Signature: TO_NUMBER(s STRING, format STRING) -> NUMERIC

Parses text into a numeric value using the supplied format.

SELECT TO_NUMBER('1,234.50', '9,999.99') AS value;
-- 1234.50

AT TIME ZONE

Signature: timestamp AT TIME ZONE zone STRING -> TIMESTAMP

Converts a timestamp using the supplied time zone.

SELECT timestamp AT TIME ZONE 'UTC' AS utc_time
FROM logs;

DATE_BIN

Signature: DATE_BIN(stride INTERVAL, source TIMESTAMP, origin TIMESTAMP) -> TIMESTAMP

Places source into an interval bucket of length stride anchored at origin.

SELECT DATE_BIN(INTERVAL '15 minutes', timestamp, TIMESTAMP '2026-01-01 00:00:00') AS bucket,
       COUNT(*)
FROM logs
GROUP BY bucket;

Type conversion functions

CAST

Signature: CAST(value ANY AS target_type) -> target_type

Converts value to target_type. The query fails if the value cannot be converted.

SELECT CAST('500' AS INT) AS status_code;

PostgreSQL double-colon cast

Signature: value::target_type -> target_type

PostgreSQL-compatible shorthand for CAST.

SELECT '500'::INT AS status_code;

TRY_CAST

Signature: TRY_CAST(value ANY AS target_type) -> target_type

Converts value to target_type. Returns null when the value cannot be converted.

SELECT TRY_CAST('not-a-number' AS INT) AS value;
-- NULL

Aggregate functions

Aggregate functions summarize rows. Use them with optional GROUP BY clauses.

COUNT

Signature: COUNT(*) -> BIGINT

Signature: COUNT(expr ANY) -> BIGINT

Counts rows. COUNT(expr) counts non-null expression values.

SELECT COUNT(*) AS events
FROM logs;

COUNT DISTINCT

Signature: COUNT(DISTINCT expr ANY) -> BIGINT

Counts distinct non-null expression values.

SELECT COUNT(DISTINCT service) AS services
FROM logs;

SUM

Signature: SUM(expr NUMERIC) -> NUMERIC

Returns the sum of non-null numeric values.

SELECT SUM(bytes) AS total_bytes
FROM logs;

AVG

Signature: AVG(expr NUMERIC) -> DOUBLE

Returns the average of non-null numeric values.

SELECT AVG(duration_ms) AS avg_duration_ms
FROM logs;

MIN

Signature: MIN(expr ORDERABLE) -> ORDERABLE

Returns the minimum non-null value.

SELECT MIN(timestamp) AS first_event
FROM logs;

MAX

Signature: MAX(expr ORDERABLE) -> ORDERABLE

Returns the maximum non-null value.

SELECT MAX(timestamp) AS last_event
FROM logs;

STDDEV

Signature: STDDEV(expr NUMERIC) -> DOUBLE

Returns the sample standard deviation of non-null numeric values.

SELECT STDDEV(duration_ms) AS duration_stddev
FROM logs;

STDDEV_SAMP

Signature: STDDEV_SAMP(expr NUMERIC) -> DOUBLE

Returns the sample standard deviation of non-null numeric values.

SELECT STDDEV_SAMP(duration_ms) AS duration_stddev
FROM logs;

STDDEV_POP

Signature: STDDEV_POP(expr NUMERIC) -> DOUBLE

Returns the population standard deviation of non-null numeric values.

SELECT STDDEV_POP(duration_ms) AS duration_stddev_pop
FROM logs;

VARIANCE

Signature: VARIANCE(expr NUMERIC) -> DOUBLE

Returns the sample variance of non-null numeric values.

SELECT VARIANCE(duration_ms) AS duration_variance
FROM logs;

VAR_SAMP

Signature: VAR_SAMP(expr NUMERIC) -> DOUBLE

Returns the sample variance of non-null numeric values.

SELECT VAR_SAMP(duration_ms) AS duration_variance
FROM logs;

VAR_POP

Signature: VAR_POP(expr NUMERIC) -> DOUBLE

Returns the population variance of non-null numeric values.

SELECT VAR_POP(duration_ms) AS duration_variance_pop
FROM logs;

STRING_AGG

Signature: STRING_AGG(expr STRING, delimiter STRING) -> STRING

Signature: STRING_AGG(expr STRING, delimiter STRING ORDER BY sort_expr [ASC|DESC]) -> STRING

Concatenates non-null string values separated by delimiter. The ORDER BY form controls the order of concatenated values.

SELECT service, STRING_AGG(host, ',' ORDER BY host) AS hosts
FROM logs
GROUP BY service;

ARRAY_AGG

Signature: ARRAY_AGG(expr ANY) -> ANY[]

Collects non-null expression values into an array.

SELECT service, ARRAY_AGG(host) AS hosts
FROM logs
GROUP BY service;

BOOL_AND

Signature: BOOL_AND(expr BOOLEAN) -> BOOLEAN

Returns true when all non-null input values are true.

SELECT BOOL_AND(status < 500) AS all_successful
FROM logs;

EVERY

Signature: EVERY(expr BOOLEAN) -> BOOLEAN

Returns true when all non-null input values are true.

SELECT EVERY(status < 500) AS all_successful
FROM logs;

BOOL_OR

Signature: BOOL_OR(expr BOOLEAN) -> BOOLEAN

Returns true when at least one non-null input value is true.

SELECT BOOL_OR(status >= 500) AS has_errors
FROM logs;

BIT_AND

Signature: BIT_AND(expr INT) -> INT

Returns the bitwise AND of non-null integer values.

SELECT BIT_AND(flags) AS combined_flags
FROM events;

BIT_OR

Signature: BIT_OR(expr INT) -> INT

Returns the bitwise OR of non-null integer values.

SELECT BIT_OR(flags) AS combined_flags
FROM events;

BIT_XOR

Signature: BIT_XOR(expr INT) -> INT

Returns the bitwise XOR of non-null integer values.

SELECT BIT_XOR(flags) AS xor_flags
FROM events;

PERCENTILE_CONT

Signature: PERCENTILE_CONT(fraction DOUBLE) WITHIN GROUP (ORDER BY expr NUMERIC) -> NUMERIC

Returns a continuous percentile. The result may interpolate between neighboring ordered values.

SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95_duration_ms
FROM logs;

PERCENTILE_DISC

Signature: PERCENTILE_DISC(fraction DOUBLE) WITHIN GROUP (ORDER BY expr ANY) -> ANY

Returns a discrete percentile selected from the ordered input values.

SELECT PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95_duration_ms
FROM logs;

MODE

Signature: MODE() WITHIN GROUP (ORDER BY expr ANY) -> ANY

Returns the most frequent value from the ordered input values.

SELECT MODE() WITHIN GROUP (ORDER BY status) AS common_status
FROM logs;

CORR

Signature: CORR(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the correlation coefficient between y and x.

SELECT CORR(duration_ms, bytes) AS duration_bytes_corr
FROM logs;

COVAR_POP

Signature: COVAR_POP(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the population covariance of y and x.

SELECT COVAR_POP(duration_ms, bytes) AS covariance
FROM logs;

COVAR_SAMP

Signature: COVAR_SAMP(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the sample covariance of y and x.

SELECT COVAR_SAMP(duration_ms, bytes) AS covariance
FROM logs;

REGR_SLOPE

Signature: REGR_SLOPE(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the slope of the least-squares regression line for y as a function of x.

SELECT REGR_SLOPE(duration_ms, bytes) AS slope
FROM logs;

REGR_INTERCEPT

Signature: REGR_INTERCEPT(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the y-intercept of the least-squares regression line.

SELECT REGR_INTERCEPT(duration_ms, bytes) AS intercept
FROM logs;

REGR_COUNT

Signature: REGR_COUNT(y NUMERIC, x NUMERIC) -> BIGINT

Returns the number of non-null (y, x) pairs used by regression aggregates.

SELECT REGR_COUNT(duration_ms, bytes) AS points
FROM logs;

REGR_R2

Signature: REGR_R2(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the square of the correlation coefficient for the regression.

SELECT REGR_R2(duration_ms, bytes) AS r_squared
FROM logs;

REGR_AVGX

Signature: REGR_AVGX(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the average of the independent variable x for non-null (y, x) pairs.

SELECT REGR_AVGX(duration_ms, bytes) AS avg_bytes
FROM logs;

REGR_AVGY

Signature: REGR_AVGY(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the average of the dependent variable y for non-null (y, x) pairs.

SELECT REGR_AVGY(duration_ms, bytes) AS avg_duration_ms
FROM logs;

REGR_SXX

Signature: REGR_SXX(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the sum of squares of the independent variable x around its mean.

SELECT REGR_SXX(duration_ms, bytes) AS sxx
FROM logs;

REGR_SXY

Signature: REGR_SXY(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the sum of products of x and y around their means.

SELECT REGR_SXY(duration_ms, bytes) AS sxy
FROM logs;

REGR_SYY

Signature: REGR_SYY(y NUMERIC, x NUMERIC) -> DOUBLE

Returns the sum of squares of the dependent variable y around its mean.

SELECT REGR_SYY(duration_ms, bytes) AS syy
FROM logs;

Aggregate FILTER

Signature: aggregate(args) FILTER (WHERE condition BOOLEAN) -> aggregate_result

Applies an aggregate only to rows that satisfy condition.

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status >= 500) AS errors,
  SUM(bytes) FILTER (WHERE status < 500) AS successful_bytes
FROM logs;

JSON_AGG

Signature: JSON_AGG(expr ANY) -> JSON

Collects input values into a JSON array.

SELECT service, JSON_AGG(message) AS messages
FROM logs
GROUP BY service;

JSON_OBJECT_AGG

Signature: JSON_OBJECT_AGG(key STRING, value ANY) -> JSON

Collects key/value pairs into a JSON object.

SELECT JSON_OBJECT_AGG(service, event_count) AS counts_by_service
FROM (
  SELECT service, COUNT(*) AS event_count
  FROM logs
  GROUP BY service
) s;

Window and analytic functions

Window functions compute values across rows related to the current row. Use an OVER clause with optional PARTITION BY and ORDER BY.

ROW_NUMBER

Signature: ROW_NUMBER() OVER (window_spec) -> BIGINT

Returns a unique sequential number for each row in the window ordering.

SELECT service, timestamp,
       ROW_NUMBER() OVER (PARTITION BY service ORDER BY timestamp DESC) AS row_num
FROM logs;

RANK

Signature: RANK() OVER (window_spec) -> BIGINT

Returns the rank of each row in the window ordering, leaving gaps after ties.

SELECT service, duration_ms,
       RANK() OVER (PARTITION BY service ORDER BY duration_ms DESC) AS duration_rank
FROM logs;

DENSE_RANK

Signature: DENSE_RANK() OVER (window_spec) -> BIGINT

Returns the rank of each row in the window ordering without gaps after ties.

SELECT service, duration_ms,
       DENSE_RANK() OVER (PARTITION BY service ORDER BY duration_ms DESC) AS duration_rank
FROM logs;

NTILE

Signature: NTILE(bucket_count INT) OVER (window_spec) -> INT

Divides ordered rows in each window partition into bucket_count buckets and returns the bucket number for each row.

SELECT service, duration_ms,
       NTILE(4) OVER (PARTITION BY service ORDER BY duration_ms) AS quartile
FROM logs;

CUME_DIST

Signature: CUME_DIST() OVER (window_spec) -> DOUBLE

Returns the cumulative distribution of the current row within the ordered window partition.

SELECT service, duration_ms,
       CUME_DIST() OVER (PARTITION BY service ORDER BY duration_ms) AS cume_dist
FROM logs;

PERCENT_RANK

Signature: PERCENT_RANK() OVER (window_spec) -> DOUBLE

Returns the relative rank of the current row within the ordered window partition.

SELECT service, duration_ms,
       PERCENT_RANK() OVER (PARTITION BY service ORDER BY duration_ms) AS percent_rank
FROM logs;

LAG

Signature: LAG(expr ANY [, offset INT [, default ANY]]) OVER (window_spec) -> ANY

Returns the value of expr from a previous row in the window partition. offset defaults to 1.

SELECT service, timestamp, duration_ms,
       LAG(duration_ms, 1, 0) OVER (PARTITION BY service ORDER BY timestamp) AS previous_duration_ms
FROM logs;

LEAD

Signature: LEAD(expr ANY [, offset INT [, default ANY]]) OVER (window_spec) -> ANY

Returns the value of expr from a following row in the window partition. offset defaults to 1.

SELECT service, timestamp, duration_ms,
       LEAD(duration_ms, 1, 0) OVER (PARTITION BY service ORDER BY timestamp) AS next_duration_ms
FROM logs;

FIRST_VALUE

Signature: FIRST_VALUE(expr ANY) OVER (window_spec) -> ANY

Returns the first value in the current window frame.

SELECT service, timestamp,
       FIRST_VALUE(timestamp) OVER (PARTITION BY service ORDER BY timestamp) AS first_seen
FROM logs;

LAST_VALUE

Signature: LAST_VALUE(expr ANY) OVER (window_spec) -> ANY

Returns the last value in the current window frame.

SELECT service, timestamp,
       LAST_VALUE(timestamp) OVER (PARTITION BY service ORDER BY timestamp) AS last_seen_so_far
FROM logs;

NTH_VALUE

Signature: NTH_VALUE(expr ANY, n INT) OVER (window_spec) -> ANY

Returns the nth value in the current window frame.

SELECT service, timestamp,
       NTH_VALUE(timestamp, 2) OVER (PARTITION BY service ORDER BY timestamp) AS second_seen
FROM logs;

Aggregate window functions

Signature: SUM(expr NUMERIC) OVER (window_spec) -> NUMERIC

Signature: AVG(expr NUMERIC) OVER (window_spec) -> DOUBLE

Signature: COUNT(expr ANY) OVER (window_spec) -> BIGINT

Signature: MIN(expr ORDERABLE) OVER (window_spec) -> ORDERABLE

Signature: MAX(expr ORDERABLE) OVER (window_spec) -> ORDERABLE

Use aggregate functions with OVER to compute running or partition-level aggregates.

SELECT service, timestamp, bytes,
       SUM(bytes) OVER (PARTITION BY service ORDER BY timestamp) AS running_bytes,
       AVG(bytes) OVER (PARTITION BY service ORDER BY timestamp) AS running_avg_bytes,
       COUNT(*) OVER (PARTITION BY service) AS service_events,
       MIN(timestamp) OVER (PARTITION BY service) AS first_event,
       MAX(timestamp) OVER (PARTITION BY service) AS last_event
FROM logs;

JSON functions

JSON functions work with JSON/Dynamic values.

JSON_VALUE

Signature: JSON_VALUE(json JSON, path STRING) -> STRING

Extracts a scalar value from JSON using a SQL JSON path and returns it as text.

SELECT JSON_VALUE(payload, '$.user.id') AS user_id
FROM logs;

JSON_QUERY

Signature: JSON_QUERY(json JSON, path STRING) -> JSON

Extracts a JSON object or array from JSON using a SQL JSON path.

SELECT JSON_QUERY(payload, '$.request.headers') AS headers
FROM logs;

JSON_EXISTS

Signature: JSON_EXISTS(json JSON, path STRING) -> BOOLEAN

Returns true when the supplied SQL JSON path exists in the JSON value.

SELECT id
FROM logs
WHERE JSON_EXISTS(payload, '$.trace_id');

JSON_TABLE

Signature: JSON_TABLE(json JSON, path STRING COLUMNS (...)) -> TABLE

Projects JSON data into relational rows and columns.

SELECT jt.key, jt.value
FROM logs,
JSON_TABLE(payload, '$.tags[*]' COLUMNS (
  key STRING PATH '$.key',
  value STRING PATH '$.value'
)) AS jt;

JSON_ARRAY

Signature: JSON_ARRAY(value ANY [, ...]) -> JSON

Builds a JSON array from the supplied values.

SELECT JSON_ARRAY(service, status, duration_ms) AS event_summary
FROM logs;

JSON_OBJECT

Signature: JSON_OBJECT(key VALUE value [, ...]) -> JSON

Builds a JSON object from key/value pairs.

SELECT JSON_OBJECT('service' VALUE service, 'status' VALUE status) AS event_object
FROM logs;

JSONB_EXTRACT_PATH

Signature: JSONB_EXTRACT_PATH(json JSON, path_element STRING [, ...]) -> JSON

Extracts a nested JSON value by following the supplied path elements.

SELECT JSONB_EXTRACT_PATH(payload, 'request', 'headers') AS headers
FROM logs;

JSONB_EXTRACT_PATH_TEXT

Signature: JSONB_EXTRACT_PATH_TEXT(json JSON, path_element STRING [, ...]) -> STRING

Extracts a nested JSON value by following the supplied path elements and returns it as text.

SELECT JSONB_EXTRACT_PATH_TEXT(payload, 'user', 'id') AS user_id
FROM logs;

JSONB_EACH

Signature: JSONB_EACH(json JSON) -> SETOF (key STRING, value JSON)

Expands the top-level JSON object into key/value rows.

SELECT e.key, e.value
FROM logs, JSONB_EACH(payload) AS e;

JSONB_OBJECT_KEYS

Signature: JSONB_OBJECT_KEYS(json JSON) -> SETOF STRING

Returns the top-level keys of a JSON object as rows.

SELECT JSONB_OBJECT_KEYS(payload) AS key
FROM logs;

JSONB_ARRAY_ELEMENTS

Signature: JSONB_ARRAY_ELEMENTS(json JSON) -> SETOF JSON

Expands a JSON array into one row per element.

SELECT tag
FROM logs, JSONB_ARRAY_ELEMENTS(JSONB_EXTRACT_PATH(payload, 'tags')) AS tag;

JSONB_TYPEOF

Signature: JSONB_TYPEOF(json JSON) -> STRING

Returns the JSON type name, such as object, array, string, number, boolean, or null.

SELECT JSONB_TYPEOF(payload) AS payload_type
FROM logs;

JSONB_STRIP_NULLS

Signature: JSONB_STRIP_NULLS(json JSON) -> JSON

Returns JSON with object fields that have null values removed.

SELECT JSONB_STRIP_NULLS(payload) AS compact_payload
FROM logs;

JSONB_SET

Signature: JSONB_SET(json JSON, path STRING[], value JSON) -> JSON

Returns JSON with the value at path replaced by value.

SELECT JSONB_SET(payload, ARRAY['normalized'], TO_JSONB(true)) AS updated_payload
FROM logs;

JSONB_BUILD_OBJECT

Signature: JSONB_BUILD_OBJECT(key ANY, value ANY [, ...]) -> JSON

Builds a JSON object from alternating key and value arguments.

SELECT JSONB_BUILD_OBJECT('service', service, 'status', status) AS event_object
FROM logs;

JSONB_BUILD_ARRAY

Signature: JSONB_BUILD_ARRAY(value ANY [, ...]) -> JSON

Builds a JSON array from the supplied values.

SELECT JSONB_BUILD_ARRAY(service, status, duration_ms) AS event_array
FROM logs;

JSONB_PRETTY

Signature: JSONB_PRETTY(json JSON) -> STRING

Formats JSON as readable text.

SELECT JSONB_PRETTY(payload) AS pretty_payload
FROM logs;

TO_JSON

Signature: TO_JSON(value ANY) -> JSON

Converts a SQL value to JSON.

SELECT TO_JSON(status) AS status_json
FROM logs;

TO_JSONB

Signature: TO_JSONB(value ANY) -> JSON

Converts a SQL value to JSON.

SELECT TO_JSONB(service) AS service_json
FROM logs;

ROW_TO_JSON

Signature: ROW_TO_JSON(record RECORD) -> JSON

Converts a SQL row value to JSON.

SELECT ROW_TO_JSON(r) AS event_json
FROM (
  SELECT service, status, duration_ms
  FROM logs
  LIMIT 1
) r;

JSONB_PATH_QUERY

Signature: JSONB_PATH_QUERY(json JSON, path STRING) -> SETOF JSON

Returns JSON values selected by a JSON path as rows.

SELECT JSONB_PATH_QUERY(payload, '$.events[*]') AS event
FROM logs;

JSONB_PATH_EXISTS

Signature: JSONB_PATH_EXISTS(json JSON, path STRING) -> BOOLEAN

Returns true when a JSON path selects at least one value.

SELECT id
FROM logs
WHERE JSONB_PATH_EXISTS(payload, '$.user.id');

JSONB_PATH_MATCH

Signature: JSONB_PATH_MATCH(json JSON, path STRING) -> BOOLEAN

Evaluates a JSON path predicate and returns a boolean result.

SELECT id
FROM logs
WHERE JSONB_PATH_MATCH(payload, '$.status == "error"');

Array functions

ARRAY_LENGTH

Signature: ARRAY_LENGTH(arr ANY[]) -> INT

Returns the length of an array.

SELECT ARRAY_LENGTH(ARRAY['api', 'worker']) AS value;
-- 2

ARRAY_CONCAT

Signature: ARRAY_CONCAT(a ANY[], b ANY[]) -> ANY[]

Concatenates two arrays.

SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[3, 4]) AS value;
-- {1,2,3,4}

ARRAY_CAT

Signature: ARRAY_CAT(a ANY[], b ANY[]) -> ANY[]

Concatenates two arrays.

SELECT ARRAY_CAT(ARRAY[1, 2], ARRAY[3, 4]) AS value;
-- {1,2,3,4}

ARRAY_APPEND

Signature: ARRAY_APPEND(arr ANY[], elem ANY) -> ANY[]

Appends elem to the end of arr.

SELECT ARRAY_APPEND(ARRAY['api'], 'worker') AS value;
-- {api,worker}

ARRAY_PREPEND

Signature: ARRAY_PREPEND(elem ANY, arr ANY[]) -> ANY[]

Prepends elem to the beginning of arr.

SELECT ARRAY_PREPEND('api', ARRAY['worker']) AS value;
-- {api,worker}

ARRAY_POSITION

Signature: ARRAY_POSITION(arr ANY[], elem ANY) -> INT

Returns the SQL 1-based position of the first occurrence of elem in arr, or null when it is not present.

SELECT ARRAY_POSITION(ARRAY['api', 'worker'], 'worker') AS value;
-- 2

ARRAY_POSITIONS

Signature: ARRAY_POSITIONS(arr ANY[], elem ANY) -> INT[]

Returns all SQL 1-based positions where elem appears in arr.

SELECT ARRAY_POSITIONS(ARRAY['api', 'worker', 'api'], 'api') AS value;
-- {1,3}

ARRAY_REMOVE

Signature: ARRAY_REMOVE(arr ANY[], elem ANY) -> ANY[]

Returns arr with all elements equal to elem removed.

SELECT ARRAY_REMOVE(ARRAY['api', 'worker', 'api'], 'api') AS value;
-- {worker}

ARRAY_REPLACE

Signature: ARRAY_REPLACE(arr ANY[], from_elem ANY, to_elem ANY) -> ANY[]

Returns arr with each occurrence of from_elem replaced by to_elem.

SELECT ARRAY_REPLACE(ARRAY['api', 'worker'], 'api', 'frontend') AS value;
-- {frontend,worker}

ARRAY_DIMS

Signature: ARRAY_DIMS(arr ANY[]) -> STRING

Returns a text description of array dimensions.

SELECT ARRAY_DIMS(ARRAY[1, 2, 3]) AS value;

ARRAY_NDIMS

Signature: ARRAY_NDIMS(arr ANY[]) -> INT

Returns the number of dimensions in arr.

SELECT ARRAY_NDIMS(ARRAY[1, 2, 3]) AS value;
-- 1

ARRAY_UPPER

Signature: ARRAY_UPPER(arr ANY[], dim INT) -> INT

Returns the upper bound of the requested array dimension.

SELECT ARRAY_UPPER(ARRAY[1, 2, 3], 1) AS value;
-- 3

ARRAY_LOWER

Signature: ARRAY_LOWER(arr ANY[], dim INT) -> INT

Returns the lower bound of the requested array dimension.

SELECT ARRAY_LOWER(ARRAY[1, 2, 3], 1) AS value;
-- 1

ARRAY_TO_STRING

Signature: ARRAY_TO_STRING(arr ANY[], delimiter STRING) -> STRING

Joins array elements into a string separated by delimiter.

SELECT ARRAY_TO_STRING(ARRAY['api', 'worker'], ',') AS value;
-- api,worker

STRING_TO_ARRAY

Signature: STRING_TO_ARRAY(s STRING, delimiter STRING) -> STRING[]

Splits s into an array using delimiter.

SELECT STRING_TO_ARRAY('api,worker', ',') AS value;
-- {api,worker}

CARDINALITY

Signature: CARDINALITY(arr ANY[]) -> INT

Returns the total number of elements in arr.

SELECT CARDINALITY(ARRAY[1, 2, 3]) AS value;
-- 3

UNNEST

Signature: UNNEST(arr ANY[]) -> SETOF ANY

Expands an array into one row per element.

SELECT value
FROM UNNEST(ARRAY['api', 'worker']) AS value;

GENERATE_SERIES

Signature: GENERATE_SERIES(start INT, stop INT [, step INT]) -> SETOF INT

Signature: GENERATE_SERIES(start TIMESTAMP, stop TIMESTAMP, step INTERVAL) -> SETOF TIMESTAMP

Generates a row for each value from start through stop using step.

SELECT value
FROM GENERATE_SERIES(1, 5) AS value;

SELECT bucket
FROM GENERATE_SERIES(
  TIMESTAMP '2026-01-01 00:00:00',
  TIMESTAMP '2026-01-01 01:00:00',
  INTERVAL '15 minutes'
) AS bucket;

System and utility functions

PG_TYPEOF

Signature: PG_TYPEOF(expr ANY) -> STRING

Returns the PostgreSQL-style type name for an expression.

SELECT PG_TYPEOF(status) AS status_type
FROM logs
LIMIT 1;

CURRENT_USER

Signature: CURRENT_USER -> STRING

Returns the current session user.

SELECT CURRENT_USER AS value;

SESSION_USER

Signature: SESSION_USER -> STRING

Returns the session user.

SELECT SESSION_USER AS value;

CURRENT_SCHEMA

Signature: CURRENT_SCHEMA -> STRING

Returns the current schema name used for PostgreSQL client compatibility.

SELECT CURRENT_SCHEMA AS value;

VERSION

Signature: VERSION() -> STRING

Returns the server version string exposed to the SQL client.

SELECT VERSION() AS value;

Result types for PostgreSQL clients

Mach5 returns SQL results to PostgreSQL-compatible clients using PostgreSQL type names. Common mappings include:

Client compatibility commands

Many PostgreSQL tools issue setup and metadata queries automatically. Mach5 supports common client commands and catalog lookups so tools can connect, discover tables and columns, and run SQL queries.

Common commands include:

SET warehouse = 'it-wh';
SHOW warehouse;
SELECT current_database();
SELECT current_schema();
BEGIN;
COMMIT;
ROLLBACK;

Transaction commands are accepted for PostgreSQL client compatibility. Mach5 queries execute according to Mach5 query semantics.