Functions

Examples on this page use this small demo schema:

CREATE TABLE users  (id BIGINT PRIMARY KEY, username TEXT, email TEXT, status TEXT, created_at TIMESTAMP);
CREATE TABLE sales  (id BIGINT PRIMARY KEY, date DATE, amount DOUBLE PRECISION, region TEXT, category TEXT);
CREATE TABLE events (id BIGINT PRIMARY KEY, name TEXT, payload JSONB, occurred_at TIMESTAMP);

ScramDB provides over 100 scalar functions, plus aggregate and window functions, and JSON, array, and system helpers. A call with the wrong number of arguments, or an ENUM argument in a position PostgreSQL's own function catalog has no overload for, is refused with SQLSTATE 42883 (function ... does not exist), matching PostgreSQL, rather than silently reading a default value or misreading the ordinal as text.

Scalar Functions

String Functions

Function	Syntax	Description
LENGTH	LENGTH(str)	Number of characters for text, number of bytes for BYTEA (PostgreSQL's own polymorphic length())
OCTET_LENGTH	OCTET_LENGTH(str)	Number of bytes
BIT_LENGTH	BIT_LENGTH(str)	Number of bits (8 times OCTET_LENGTH)
CHAR_LENGTH / CHARACTER_LENGTH	CHAR_LENGTH(str)	Number of characters; text only, refused with SQLSTATE 42883 over BYTEA
UPPER	UPPER(str)	Convert to uppercase
LOWER	LOWER(str)	Convert to lowercase
SUBSTRING	SUBSTRING(str FROM start FOR len)	Extract substring
TRIM	TRIM(str)	Remove leading/trailing whitespace
LTRIM	LTRIM(str)	Remove leading whitespace
RTRIM	RTRIM(str)	Remove trailing whitespace
BTRIM	BTRIM(str, chars)	Remove specified characters from both ends
CONCAT	CONCAT(str1, str2, ...)	Concatenate strings
CONCAT_WS	CONCAT_WS(sep, str1, str2, ...)	Concatenate with separator
REPLACE	REPLACE(str, from, to)	Replace all occurrences
POSITION	POSITION(substr IN str)	Find substring position (1-based)
STRPOS	STRPOS(str, substr)	Find substring position (1-based); its own function, with str and substr in the opposite order from POSITION
REGEXP_MATCH / REGEXP_MATCHES	REGEXP_MATCH(str, pattern [, flags])	The first match's capture groups (or the whole match, with no group) as a TEXT[]
REGEXP_REPLACE	REGEXP_REPLACE(str, pattern, replacement [, flags])	Replace the first match, or every match with the g flag
LEFT	LEFT(str, n)	First n characters
RIGHT	RIGHT(str, n)	Last n characters
LPAD	LPAD(str, len, fill)	Pad left to length
RPAD	RPAD(str, len, fill)	Pad right to length
REPEAT	REPEAT(str, n)	Repeat string n times
REVERSE	REVERSE(str)	Reverse string
INITCAP	INITCAP(str)	Capitalize first letter of each word
SPLIT_PART	SPLIT_PART(str, delim, n)	Extract nth field
STARTS_WITH	STARTS_WITH(str, prefix)	Check prefix
ENDS_WITH	ENDS_WITH(str, suffix)	Check suffix
ASCII	ASCII(str)	ASCII code of first character
CHR	CHR(code)	Character from ASCII code
MD5	MD5(str)	MD5 hash as hex string
FORMAT	FORMAT(fmt, ...)	Format a string with %s/%I/%L-style placeholders
QUOTE_IDENT	QUOTE_IDENT(str)	Quote a string for use as an SQL identifier, only if it needs it
QUOTE_LITERAL	QUOTE_LITERAL(value)	Quote a value for use as an SQL literal
QUOTE_NULLABLE	QUOTE_NULLABLE(value)	Like QUOTE_LITERAL, but a NULL argument renders as the unquoted text NULL
TO_HEX	TO_HEX(n)	Convert an integer to its hexadecimal text
REGEXP_COUNT	REGEXP_COUNT(str, pattern [, start [, flags]])	Count non-overlapping regular expression matches

FORMAT accepts a DECIMAL/NUMERIC, TIME, MONEY, INET, TID, CIDR, or TIMESTAMPTZ value passed as a literal, but not one read straight from a column of one of those types; cast the column to text first (format('%s', num::text)).

FORMAT, CONCAT, and CONCAT_WS also accept a call-position VARIADIC argument, spreading an array into the function's trailing arguments: format('%s %s', VARIADIC ARRAY['a', 'b']) is the same call as format('%s %s', 'a', 'b'). A NULL VARIADIC array spreads to zero arguments, not to one NULL argument. GREATEST and LEAST do not accept VARIADIC at all, matching PostgreSQL: they are grammar productions, not ordinary functions, and PostgreSQL itself refuses the syntax.

Math Functions

Function	Syntax	Description
ABS	ABS(x)	Absolute value
CEIL / CEILING	CEIL(x)	Round up to nearest integer
FLOOR	FLOOR(x)	Round down to nearest integer
ROUND	ROUND(x, d)	Round to d decimal places
TRUNC	TRUNC(x, d)	Truncate to d decimal places
SQRT	SQRT(x)	Square root
CBRT	CBRT(x)	Cube root
POWER	POWER(x, y)	x raised to power y
EXP	EXP(x)	e^x
LN	LN(x)	Natural logarithm
LOG	LOG(base, x)	Logarithm with base
LOG10	LOG10(x)	Base-10 logarithm
MOD	MOD(x, y)	Modulo (remainder)
SIGN	SIGN(x)	Sign (-1, 0, or 1)
PI	PI()	π constant
DEGREES	DEGREES(radians)	Radians to degrees
RADIANS	RADIANS(degrees)	Degrees to radians
GCD	GCD(a, b)	Greatest common divisor
LCM	LCM(a, b)	Least common multiple
SIN	SIN(x)	Sine
COS	COS(x)	Cosine
TAN	TAN(x)	Tangent
ASIN	ASIN(x)	Arc sine
ACOS	ACOS(x)	Arc cosine
ATAN	ATAN(x)	Arc tangent
ATAN2	ATAN2(y, x)	Two-argument arc tangent
GREATEST	GREATEST(a, b, ...)	Largest value
LEAST	LEAST(a, b, ...)	Smallest value
SCALE	SCALE(x)	Number of digits after the decimal point a NUMERIC is stored with
MIN_SCALE	MIN_SCALE(x)	Smallest scale that represents a NUMERIC value exactly
TRIM_SCALE	TRIM_SCALE(x)	A NUMERIC value re-expressed at its minimal scale

Over a NUMERIC argument, CEIL, FLOOR, ROUND, and TRUNC answer NUMERIC at the places asked for, as PostgreSQL does: ROUND(v, 1) is a one-place numeric, TRUNC(v), CEIL(v) and FLOOR(v) whole numbers, and a negative d rounds to tens or hundreds. A tie rounds away from zero (ROUND(2.5) is 3). Over DOUBLE PRECISION the one-argument ROUND(x) ties to even like PostgreSQL's (ROUND(2.5::float8) is 2); ROUND(x, d) over a double is an extension PostgreSQL does not have and rounds a tie away from zero. d must be a constant when x is NUMERIC; a number of places that varies by row rounds x as a double.

GREATEST and LEAST answer the unified type of every argument, the same way a CASE branch or a UNION arm does. When a NUMERIC(p,s) argument wins, the result is unconstrained NUMERIC rather than that same fixed precision and scale, matching PostgreSQL.

SCALE, MIN_SCALE, and TRIM_SCALE describe a NUMERIC value's own display scale: SCALE(1.50) is 2 (the value's own stored scale), MIN_SCALE(1.50) is 1 (the smallest scale that still represents the value exactly), and TRIM_SCALE(1.50) is the value 1.5, re-expressed at that minimal scale. SCALE and MIN_SCALE answer NULL for NaN, Infinity, and -Infinity; TRIM_SCALE passes one of them through unchanged, since there are no trailing zeros to trim on a special value.

Date/Time Functions

Function	Syntax	Description
EXTRACT	EXTRACT(field FROM source)	Extract date/time field; returns NUMERIC, at PostgreSQL's own scale for the field
DATE_PART	DATE_PART('field', source)	Same fields as EXTRACT, function form, but always returns DOUBLE PRECISION
DATE_TRUNC	DATE_TRUNC('field', source)	Truncate to specified precision
NOW	NOW()	Current timestamp, frozen for the whole transaction
CURRENT_DATE	CURRENT_DATE	Current date, frozen for the whole transaction
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Current timestamp, frozen for the whole transaction
TRANSACTION_TIMESTAMP	TRANSACTION_TIMESTAMP()	Alias for NOW(): frozen for the whole transaction
STATEMENT_TIMESTAMP	STATEMENT_TIMESTAMP()	Re-taken at the start of each top-level statement
CLOCK_TIMESTAMP	CLOCK_TIMESTAMP()	Reads the wall clock fresh on every evaluation
AGE	AGE(ts1, ts2)	Interval between timestamps
TO_CHAR	TO_CHAR(value, format)	Format to string
TO_DATE	TO_DATE(str, format)	Parse date from string
TO_TIMESTAMP	TO_TIMESTAMP(str, format)	Parse timestamp from string
MAKE_DATE	MAKE_DATE(year, month, day)	Construct date
MAKE_TIMESTAMP	MAKE_TIMESTAMP(y,m,d,h,m,s)	Construct timestamp

EXTRACT fields: YEAR, MONTH, DAY, EPOCH, DOW (day of week), DOY (day of year), QUARTER, and HOUR, MINUTE, SECOND (timestamp/time source only, not a bare DATE).

WEEK is not a supported EXTRACT/DATE_PART field. To truncate to the start of the week, use DATE_TRUNC('week', source), which rounds down to the Monday of the containing week.

JSON Functions and Operators

Work with JSONB columns using operators and functions:

Operator / Function	Description
json -> 'key'	Get a field or element as JSON
json ->> 'key'	Get a field or element as text
json #> '{a,b}'	Get a nested value by path
json @> other	Does the left JSON contain the right?
json ? 'key'	Does the object have this key (or the array this string element)?
json @? 'path'	Does the SQL/JSON path match anything? (JSONB_PATH_EXISTS)
json @@ 'predicate'	The SQL/JSON path predicate's result (JSONB_PATH_MATCH)
JSONB_BUILD_OBJECT(k, v, ...), JSON_BUILD_OBJECT(k, v, ...)	Build a JSON object from key/value pairs, each value by its own type (a NUMERIC stays a number, a time a string), also over VARIADIC arrays
JSONB_ARRAY_ELEMENTS(json), JSONB_ARRAY_ELEMENTS_TEXT(json)	Expand a JSON array into rows, as JSONB or as text
JSONB_EACH(json), JSONB_EACH_TEXT(json)	Expand a JSON object into (key, value) rows, the value as JSONB or as text
JSONB_OBJECT_KEYS(json)	Expand a JSON object into its keys, one row each
JSONB_EXTRACT_PATH(json, ...)	Get a value at a path
JSONB_SET(json, path, value)	Return the JSON with a value replaced
JSONB_ARRAY_LENGTH(json)	Number of elements in a JSON array
JSONB_TYPEOF(json)	Type of the top-level JSON value
JSONB_PATH_QUERY_ARRAY(json, path)	Every match of a SQL/JSON path, as a JSONB array
JSONB_PATH_QUERY(json, path)	Every match of a SQL/JSON path, one row per match
JSONB_PATH_QUERY_FIRST(json, path)	The first match of a SQL/JSON path, or NULL
JSONB_PATH_EXISTS(json, path), JSONB_PATH_MATCH(json, path)	Whether the path matches; the path predicate's result
JSONB_TO_RECORDSET(json)	Expand a JSON array of objects into rows with declared columns (FROM clause only, needs AS alias(col type, ...))

The expanding functions (JSONB_ARRAY_ELEMENTS, JSONB_EACH, JSONB_OBJECT_KEYS and their _TEXT forms) take a literal or a column, in the FROM clause (each row of the table beside them expanded, LEFT JOIN LATERAL ... ON true keeping a row that expands to nothing) or in the SELECT list, where each row yields as many rows as its value has elements, as in PostgreSQL:

SELECT * FROM JSONB_ARRAY_ELEMENTS('[1, 2, 3]');
SELECT * FROM JSONB_EACH('{"a": 1, "b": 2}');
SELECT JSONB_ARRAY_ELEMENTS_TEXT('["a", "b"]');
SELECT k FROM JSONB_OBJECT_KEYS('{"a": 1, "b": 2}'::jsonb) k ORDER BY k;

SQL/JSON path. JSONB_PATH_QUERY, JSONB_PATH_QUERY_ARRAY, JSONB_PATH_QUERY_FIRST, JSONB_PATH_EXISTS, JSONB_PATH_MATCH and the @? and @@ operators run a SQL/JSON path against a JSONB value, and JSONB_TO_RECORDSET expands an array of objects into typed rows:

SELECT e.id, item::text FROM events e, jsonb_path_query(e.payload->'items', '$[*]') item;
SELECT r.a, r.b FROM jsonb_to_recordset('[{"a":1,"b":"x"},{"a":2,"b":"y"}]'::jsonb)
    AS r(a INT, b TEXT);

JSONB_PATH_QUERY_ARRAY(payload, '$.items[*].sku') collects the same matches into one JSONB array instead of one row per match.

The path language covers $ (root), $name (a variable from the vars argument), .key / ."quoted key" (member accessor), .* (object wildcard), [n] (array index), [*] (array wildcard), .** and .**{m,n} (recursive descent, optionally level-bounded), and ?(...) filters with comparisons, &&, ||, !, exists(...), like_regex and starts with, in lax mode (the default) or strict mode, with the silent argument. Array slices ([a to b]), last, arithmetic inside a path and item methods (.size(), .type(), .datetime() and the rest) are refused, naming the construct.

SELECT jsonb_path_query_array('{"items": [{"sku": "a", "n": 2}, {"sku": "b", "n": 7}]}',
    '$.items[*] ? (@.n > 5).sku');
SELECT '{"a": [1, 2, 3]}'::jsonb @? '$.a[*] ? (@ > 2)';

Array Functions

Function	Description
UNNEST(array)	Expand an array into rows
ARRAY_AGG(expr)	Collect values into an array (aggregate)
ARRAY_LENGTH(array, dim)	Length of an array dimension
CARDINALITY(array)	Total number of elements (0 for an empty array, NULL only for a NULL array)
ARRAY_UPPER(array, dim)	Upper bound of a dimension; for dimension 1 this engine's 1-based, 1-D arrays make it the same as ARRAY_LENGTH
ARRAY_LOWER(array, dim)	Lower bound of a dimension; 1 for a non-empty array's dimension 1, NULL wherever ARRAY_LENGTH is NULL
ARRAY_TO_STRING(array, sep)	Join array elements into text
ARRAY_APPEND(array, elem)	Add an element to the end of an array
ARRAY_PREPEND(elem, array)	Add an element to the start of an array
ARRAY_CAT(array1, array2)	Concatenate two arrays
ARRAY_REMOVE(array, elem)	Remove every occurrence of an element
ARRAY_REPLACE(array, from, to)	Replace every occurrence of a value
ARRAY_POSITION(array, elem, start)	1-based position of the first match, or NULL
ARRAY_POSITIONS(array, elem)	Positions of every match, as an array
STRING_TO_ARRAY(str, delim, null_str)	Split a string into a text array
ARRAY_FILL(value, dims)	An array of the given dimensions, filled with one value
ARRAY_DIMS(array)	The subscript range of each dimension, as text
REGEXP_SPLIT_TO_ARRAY(str, pattern)	Split a string into an array wherever the pattern matches

ARRAY_NDIMS (the number of dimensions) is not supported.

Vector Functions

Function	Syntax	Description
L2_DISTANCE	L2_DISTANCE(a, b)	Euclidean distance; same as <->
INNER_PRODUCT	INNER_PRODUCT(a, b)	Inner product; the <#> operator returns its negation, so ascending order still means "closest first"
COSINE_DISTANCE	COSINE_DISTANCE(a, b)	Cosine distance, 1 - cosine similarity; same as <=>
L1_DISTANCE	L1_DISTANCE(a, b)	Taxicab distance; same as <+>
HAMMING_DISTANCE	HAMMING_DISTANCE(a, b)	Popcount of the XOR of two same-length BIT values; same as <~>
JACCARD_DISTANCE	JACCARD_DISTANCE(a, b)	1 - |A ∩ B| / |A ∪ B| over two BIT values; same as <%>
VECTOR_DIMS	VECTOR_DIMS(v)	Number of dimensions
VECTOR_NORM	VECTOR_NORM(v)	Euclidean norm of a VECTOR, accumulated in DOUBLE PRECISION
L2_NORM	L2_NORM(v)	Euclidean norm of a HALFVEC or SPARSEVEC
L2_NORMALIZE	L2_NORMALIZE(v)	v divided by its own Euclidean norm; a zero vector is returned unchanged
BINARY_QUANTIZE	BINARY_QUANTIZE(v)	One bit per element, 1 where the element is strictly greater than zero, else 0, most significant bit first
SUBVECTOR	SUBVECTOR(v, start, count)	A VECTOR slice, 1-based like SUBSTRING; the range clamps to the vector's own bounds rather than erroring on an out-of-range span, but still errors when the clamped span is empty

Every vector function is STRICT: a NULL argument answers NULL. L2_DISTANCE, INNER_PRODUCT, and L1_DISTANCE overflow to Infinity rather than erroring, matching pgvector; COSINE_DISTANCE answers NaN when either argument is the zero vector. A dimension mismatch between the two arguments of any of the six distance functions is refused, naming both dimension counts (or, for the BIT functions, both lengths).

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, embedding VECTOR(4));
INSERT INTO vector_demo VALUES (1, '[3,0,0,4]');

SELECT vector_dims(embedding), vector_norm(embedding), l2_normalize(embedding),
       binary_quantize(embedding), subvector(embedding, 2, 2)
FROM vector_demo;

DROP TABLE vector_demo;

Vector Casts

From	To	Context	Notes
VECTOR	VECTOR	implicit	Typmod only; re-checks the dimension count
VECTOR	REAL[]	implicit	Always succeeds, element for element
INTEGER[], REAL[], DOUBLE PRECISION[], NUMERIC[]	VECTOR	assignment	Each element converted and range-checked; a NULL element or a multi-dimensional array is refused
HALFVEC	HALFVEC	implicit	Typmod only
HALFVEC	VECTOR	assignment
VECTOR	HALFVEC	implicit	Refused past HALFVEC's 65504 magnitude
HALFVEC	REAL[]	assignment
INTEGER[], REAL[], DOUBLE PRECISION[], NUMERIC[]	HALFVEC	assignment
SPARSEVEC	SPARSEVEC	implicit	Typmod only
SPARSEVEC	VECTOR	assignment	Densifies: every implicit zero becomes an explicit 0
VECTOR	SPARSEVEC	implicit	Counts the non-zero elements and applies SPARSEVEC's own limits
SPARSEVEC	HALFVEC	assignment
HALFVEC	SPARSEVEC	implicit
INTEGER[], REAL[], DOUBLE PRECISION[], NUMERIC[]	SPARSEVEC	assignment

A typmod-dimension mismatch on any of these casts is refused, naming both dimension counts. Casting narrows precision only in the direction that already loses it (VECTOR to HALFVEC, either to SPARSEVEC): the reverse direction is an assignment cast, so it takes an explicit CAST or a column assignment, never an implicit widening. A text literal casts to any of the three through the same input grammar as an INSERT.

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, embedding VECTOR(3));
INSERT INTO vector_demo VALUES (1, '[1,2,3]');

SELECT embedding::halfvec(3), embedding::sparsevec(3), embedding::real[]
FROM vector_demo;

DROP TABLE vector_demo;

System Functions

Function	Description
PG_TYPEOF(expr)	Data type of an expression
TO_REGCLASS(name)	The relation name names, as a regclass, or NULL when no relation has the name: searched for in the session's temporary schema first, then along search_path; digits are the oid itself. A text value cast to regclass (name::regclass) reads the same name and refuses one nothing holds with 42P01, a schema that does not exist with 3F000
TO_REGTYPE(name)	The type name names, as a regtype, or NULL when no type has the name
ENUM_FIRST(e), ENUM_LAST(e)	The first or the last value of the enum type of e in declaration order, as a value of that type (enum_first(NULL::mood)), so an enum column takes it and pg_typeof names the enum
ENUM_RANGE(e), ENUM_RANGE(lower, upper)	The labels of the enum type in declaration order, from lower through upper (a NULL bound is open, lower after upper is empty), as a text array; PostgreSQL answers an array of the enum type, a known difference
VERSION()	Server version string, PostgreSQL 18.6 (ScramDB <version>, commit <sha>) on <platform>, 64-bit. A program that needs one fact reads it from the scram_build_info view instead, one (name, value) row each: engine_version, git_sha (unknown when the build could not tell, marked -dirty for a build from uncommitted changes), build_unix_seconds, server_version and server_version_num
CURRENT_USER	Current role name
CURRENT_ROLE	Same as CURRENT_USER
CURRENT_DATABASE()	Current database name
CURRENT_SCHEMA()	Current schema name
PG_NOTIFY(channel, payload)	Send a notification on channel when the transaction commits; see LISTEN, UNLISTEN and NOTIFY
PG_LISTENING_CHANNELS()	The channels this session listens on, one row each
PG_NOTIFICATION_QUEUE_USAGE()	The fraction of the notification budget queued notifications hold, from 0 to 1
PG_EXPORT_SNAPSHOT()	The id of the snapshot the transaction reads at, for another session's SET TRANSACTION SNAPSHOT; see Sharing a Snapshot
CURRENT_SETTING(name [, missing_ok]), SET_CONFIG(name, value, is_local)	Read or set a session setting; the name and value may be read per row
SCRAM.CREATE_RESTORE_POINT(name), or PostgreSQL's own PG_CREATE_RESTORE_POINT(name)	Superusers only: name the current position (a single node's WAL position, or a cluster's commit position) so a later restore can target it by name. SCRAM.CREATE_RESTORE_POINT answers the position as a bigint; PG_CREATE_RESTORE_POINT answers the same point in PostgreSQL's pg_lsn text form. Works on a single node (scramdb restore --target-name) and on a cluster. See Cluster Restore

CURRENT_USER, CURRENT_ROLE, SESSION_USER, and CURRENT_CATALOG are identity keywords, not functions, matching PostgreSQL: the bare keyword resolves, and calling one with parentheses (current_user()) is a syntax error, SQLSTATE 42601. Bare USER is unaffected and still works either way.

---

Aggregate Functions

Function	Syntax	Description
COUNT	COUNT(*) or COUNT(expr)	Count rows
COUNT(DISTINCT)	COUNT(DISTINCT expr)	Count distinct values
SUM	SUM(expr)	Sum of values
AVG	AVG(expr)	Average
MIN	MIN(expr)	Minimum value
MAX	MAX(expr)	Maximum value
STDDEV / STDDEV_SAMP	STDDEV(expr)	Sample standard deviation
STDDEV_POP	STDDEV_POP(expr)	Population standard deviation
VARIANCE / VAR_SAMP	VARIANCE(expr)	Sample variance
VAR_POP	VAR_POP(expr)	Population variance
STRING_AGG	STRING_AGG(expr, delimiter)	Concatenate with delimiter
ARRAY_AGG	ARRAY_AGG(expr)	Collect values into an array
JSON_AGG / JSONB_AGG	JSONB_AGG(expr)	Collect values into a JSON array, a NULL as null
JSON_OBJECT_AGG / JSONB_OBJECT_AGG	JSONB_OBJECT_AGG(key, value)	Collect key/value pairs into a JSON object; a repeated key keeps its last value, and a NULL key is refused
BOOL_AND / EVERY	BOOL_AND(expr)	True if all true
BOOL_OR	BOOL_OR(expr)	True if any true
ANY_VALUE	ANY_VALUE(expr)	One non-NULL value of the group, or NULL when it has none

Over NUMERIC, SUM, AVG, MIN, MAX, and COUNT(DISTINCT) all keep each value's own scale rather than rounding to one fixed scale: SUM answers at the widest scale among its input values, MIN and MAX answer at the winning value's own scale (a tie keeps the later row's scale, not the wider one, as PostgreSQL does), and AVG follows the same value-dependent division scale a plain / does. AVG over a bare NUMERIC column answers unconstrained NUMERIC; over an integer column it answers NUMERIC at a fixed display scale of 16, not the unconstrained form; over REAL or DOUBLE PRECISION it answers DOUBLE PRECISION.

STDDEV, STDDEV_SAMP, STDDEV_POP, VARIANCE/VAR_SAMP, and VAR_POP over a bare NUMERIC column compute PostgreSQL's exact numeric result. Over a NUMERIC(p,s) column or an integer column, they currently compute a double-precision approximation of that result instead of the exact numeric value PostgreSQL returns. All five also take DISTINCT (STDDEV(DISTINCT x)): the distinct set is collected and finished with the same arithmetic, and all five work as window functions.

DISTINCT is also accepted by SUM, AVG, ARRAY_AGG, and STRING_AGG (in addition to COUNT), collapsing the input to its distinct values before aggregating:

SELECT SUM(DISTINCT amount) FROM sales;
SELECT ARRAY_AGG(DISTINCT category) FROM sales;
SELECT STRING_AGG(DISTINCT region, ', ') FROM sales;

All aggregate functions support FILTER (WHERE ...) clause:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count
FROM users;

Every aggregate also takes ORDER BY inside its call, which decides the order of the collecting aggregates (ARRAY_AGG, STRING_AGG, and the JSON aggregates) and changes nothing for the others:

SELECT JSONB_AGG(name ORDER BY occurred_at) FILTER (WHERE payload IS NOT NULL) FROM events;
SELECT JSONB_OBJECT_AGG(name, payload ORDER BY occurred_at) FROM events WHERE name IS NOT NULL;

The JSON aggregates render each value as to_jsonb does: a number as a number, a JSONB value as the JSON it holds, a timestamp in ISO form, an enum as its label. JSON values are stored as JSONB, so JSON_AGG and JSON_OBJECT_AGG answer JSONB's text (a JSON_OBJECT_AGG object keeps one value per key, its keys in JSONB's order). DISTINCT, ORDER BY and FILTER on a function that is not an aggregate are refused (SQLSTATE 42809), as is WITHIN GROUP on an aggregate that is not an ordered-set one (SQLSTATE 42883); the ordered-set aggregates (PERCENTILE_CONT, PERCENTILE_DISC, MODE) are not supported yet.

AVG and SUM over a VECTOR or HALFVEC column compute pgvector's own element-wise result, not a scalar: AVG is the element-wise mean across the group (each element summed in DOUBLE PRECISION, divided by the row count, converted back at the end), and SUM is the element-wise total. Both answer NULL over zero rows (or, for SUM, over a group that is all NULL). A dimension mismatch between rows in the same group is refused, naming both dimension counts, and an element-wise sum past its type's finite range is refused as an overflow, the same as the + operator raises. Neither accepts DISTINCT over a vector argument.

CREATE TABLE vector_demo (id BIGINT PRIMARY KEY, embedding VECTOR(2));
INSERT INTO vector_demo VALUES (1, '[1,2]'), (2, '[3,4]'), (3, '[5,6]');

SELECT avg(embedding), sum(embedding) FROM vector_demo;

DROP TABLE vector_demo;

GROUPING

GROUPING(...) tells you which columns of a GROUP BY GROUPING SETS / ROLLUP / CUBE query were rolled up (aggregated over) versus grouped by, in the current output row - the single-argument form, PostgreSQL's multi-argument form, and arithmetic chains of single-argument calls are all supported:

SELECT region, category, SUM(amount), GROUPING(region), GROUPING(category)
FROM sales
GROUP BY GROUPING SETS ((region), (category), (region, category), ());

-- Multi-argument form, and an arithmetic chain, are equivalent
SELECT region, category, GROUPING(region, category)
FROM sales
GROUP BY GROUPING SETS ((region), (category), (region, category), ());

SELECT region, category, GROUPING(region) + GROUPING(category) AS lochierarchy
FROM sales
GROUP BY GROUPING SETS ((region), (category), (region, category), ());

---

Window Functions

Window functions operate over a partition of rows defined by OVER().

Ranking Functions

Function	Description
ROW_NUMBER()	Unique sequential number per partition
RANK()	Rank with gaps for ties
DENSE_RANK()	Rank without gaps for ties
NTILE(n)	Distribute rows into n buckets
PERCENT_RANK()	Relative rank (0 to 1)
CUME_DIST()	Cumulative distribution

Value Functions

Function	Description
LAG(expr, offset, default)	Value from preceding row
LEAD(expr, offset, default)	Value from following row
FIRST_VALUE(expr)	First value in window frame
LAST_VALUE(expr)	Last value in window frame
NTH_VALUE(expr, n)	Nth value in window frame

Aggregate Windows

All aggregate functions (SUM, COUNT, AVG, MIN, MAX) can be used as window functions:

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_total,
    AVG(amount) OVER (PARTITION BY category ORDER BY date) AS running_avg
FROM sales;

Frame Clauses

-- Supported frame specifications
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- default
ROWS BETWEEN N PRECEDING AND N FOLLOWING
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
GROUPS BETWEEN N PRECEDING AND CURRENT ROW

RANGE and GROUPS group tied peer rows (rows with equal ORDER BY key values) into one frame position, unlike ROWS, which always treats every row as its own position. A RANGE frame with a numeric offset (RANGE BETWEEN 1.5 PRECEDING AND CURRENT ROW) takes a plain number over a numeric ORDER BY key, or an INTERVAL over a DATE, TIME, or TIMESTAMP key (RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW). A RANGE frame with an offset is refused, rather than answering a wrong number, when the ORDER BY key is not numeric or date/time-typed, when a date/time key's offset is a plain number instead of an INTERVAL, when there is more than one ORDER BY column, or when there is no ORDER BY column at all. An ORDER BY inside a window function's own argument list is not supported; order the window in its OVER clause instead.

---

Analytics Packages

Beyond the built-in functions above, ScramDB ships 15 analytics packages, covering full-text search (fts, bm25), geospatial (geo, h3), vector and embedding math (embeddings), clustering and modeling (kmeans, dbscan, regression, anomaly), approximate aggregates (quantile, sketch, theta, topk), and data hygiene (anonymize, jsonschema). You can also write your own functions in JavaScript, TypeScript, Rust, Go, C, C++, Python, and Ruby (see Programmability for UDFs).

Installing a package registers its functions under their natural SQL signatures - one step, then call directly:

-- 1. Install the package (a registry coordinate, or the .afb bytes)
SELECT scram.install('scramdb/geo_haversine_distance');

-- 2. Call it
SELECT geo_haversine_distance(40.7128, -74.0060, 34.0522, -118.2437);

To bind a DIFFERENT name (or your own inline body) to an installed package, use CREATE OR REPLACE FUNCTION ... LANGUAGE js AS 'namespace/pkg'.

The .afb package files ship in the distribution's package directory, or you can build them yourself from the packages source tree. The inline-source form (LANGUAGE js AS $$...$$) and referencing an installed package by name (LANGUAGE js AS 'namespace/pkg') are both supported and tested. Use the package reference for anything built from a compiled language, and inline source when you are writing the body yourself.

The h3 package is a real, self-consistent hex-grid spatial index, but it is not bit-compatible with Uber's H3 format: it produces different cell IDs than industry- standard H3 tooling. Do not expect interoperability with existing H3 data.