Functions + Operators

This page details Materialize’s supported SQL functions and operators.

Functions

Generic

Generic functions can typically take arguments of any type.

Function	Computes
`CAST (cast_expr) -> T`	Value as type `T` (docs)
`coalesce(x: T...) -> T?`	First non-NULL arg, or NULL if all are NULL
`nullif(x: T, y: T) -> T?`	NULL if `x == y`, else `x`

Aggregate

Aggregate functions take one or more of the same element type as arguments.

Function	Computes
`avg(x: T) -> U`	Average of `T`'s values. Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`count(x: T) -> int`	Number of non-NULL inputs.
`jsonb_agg(expression) -> jsonb`	Aggregate values (including nulls) as a jsonb array. (docs)
`max(x: T) -> T`	Maximum value among `T`
`min(x: T) -> T`	Minimum value among `T`
`stddev(x: T) -> U`	Historical alias for `stddev_samp`. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`stddev_pop(x: T) -> U`	Population standard deviation of `T`'s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`stddev_samp(x: T) -> U`	Sample standard deviation of `T`'s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same typ as `x`.
`sum(x: T) -> T`	Sum of `T`'s values
`variance(x: T) -> U`	Historical alias for `variance_samp`. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`variance_pop(x: T) -> U`	Population variance of `T`'s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`variance_samp(x: T) -> U`	Sample variance of `T`'s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.

Numbers

Number functions take number-like arguments, e.g. int, float, numeric.

Function	Computes
`abs(x: N) -> N`	The absolute value of `x`
`ceil(x: N) -> N`	The largest integer >= `x`
`floor(x: N) -> N`	The largest integer <= `x`
`mod(x: N, y: N) -> N`	`x % y`
`round(x: N) -> N`	`x` rounded to the nearest whole number; halves are rounded up
`round(x: numeric, y: int) -> numeric`	`x` rounded to `y` decimal places, while retaining the same `numeric` scale; halves are rounded up

String

Function	Computes
`ascii(s: str) -> int`	The ASCII value of `s`'s left-most character
`btrim(s: str) -> str`	Trim all spaces from both sides of `s`.
`btrim(s: str, c: str) -> str`	Trim any character in `c` from both sides of `s`.
`bit_length(s: str) -> int`	Number of bits in `s`
`bit_length(b: bytea) -> int`	Number of bits in `b`
`char_length(s: str) -> int`	Number of graphemes in `s`
`length(s: str) -> int`	Number of graphemes in `s` (docs)
`length(b: bytea) -> int`	Number of bytes in `s` (docs)
`length(s: bytea, encoding_name: str) -> int`	Number of graphemes in `s` after encoding (docs)
`ltrim(s: str) -> str`	Trim all spaces from the left side of `s`.
`ltrim(s: str, c: str) -> str`	Trim any character in `c` from the left side of `s`.
`octet_length(s: str) -> int`	Number of bytes in `s`
`octet_length(b: bytea) -> int`	Number of bytes in `b`
`regexp_extract(regex: str, haystack: str) -> Col<string>`	Values of the capture groups of `regex` as matched in `haystack`
`replace(s: str, f: str, r: str) -> str`	`s` with all instances of `f` replaced with `r`
`rtrim(s: str) -> str`	Trim all spaces from the right side of `s`.
`rtrim(s: str, c: str) -> str`	Trim any character in `c` from the right side of `s`.
`substring(s: str, start_pos: int) -> str`	Substring of `s` starting at `start_pos` (docs)
`substring(s: str, start_pos: int, l: int) -> str`	Substring starting at `start_pos` of length `l` (docs)
`trim([BOTH \| LEADING \| TRAILING]? 'c'? FROM 's') -> str`	Trims any character in `c` from `s` on the specified side. Defaults: • Side: `BOTH` • `'c'`: `' '` (space)

Scalar

Scalar functions take a list of scalar expressions

Function	Computes
`expression bool_op ALL(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
`expression bool_op ANY(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
`expression IN(s: Scalars) -> bool`	`true` for each value in `expression` if it matches at least one element of `s`
`expression NOT IN(s: Scalars) -> bool`	`true` for each value in `expression` if it does not match any elements of `s`
`expression bool_op SOME(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

Subquery

Subquery functions take a query, e.g. SELECT

Function	Computes
`expression bool_op ALL(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
`expression bool_op ANY(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
`EXISTS(s: Query) -> bool`	`true` if `s` returns at least one row
`expression IN(s: Query) -> bool`	`s` must return exactly one column; `true` for each value in `expression` if it matches at least one element of `s`
`NOT EXISTS(s: Query) -> bool`	`true` if `s` returns zero rows
`expression NOT IN(s: Query) -> bool`	`s` must return exactly one column; `true` for each value in `expression` if it does not match any elements of `s`
`expression bool_op SOME(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

Time

Time functions take or produce a time-like type, e.g. date, timestamp, timestamptz.

Function	Computes
`current_timestamp() -> timestamptz`	The `timestamptz` representing when the query was executed. NOTE: Users cannot define views with queries containing `current_timestamp()`.
`date_trunc(time_component: str, val: timestamp) -> timestamp`	Largest `time_component` <= `val` (docs)
`EXTRACT(extract_expr) -> float`	Specified time component from value (docs)
`mz_logical_timestamp() -> numeric`	The logical time at which a query executes. NOTE: Users cannot define views with queries containing `mz_logical_timestamp()`.
`now() -> timestamptz`	The `timestamptz` representing when the query was executed. NOTE: Users cannot define views with queries containing `now()`.
`to_timestamp(val: double precision) -> timestamptz`	Converts Unix epoch (seconds since 00:00:00 UTC on January 1, 1970) to timestamp

JSON

Function	Computes
`jsonb_array_elements(j: jsonb) -> Col<jsonb>`	`j`'s elements if `j` is an array. (docs)
`jsonb_array_elements_text(j: jsonb) -> Col<string>`	`j`'s elements if `j` is an array. (docs)
`jsonb_array_length(j: jsonb) -> int`	Number of elements in `j`'s outermost array. (docs)
`jsonb_build_array(x: ...) -> jsonb`	The elements of `x` in a `jsonb` array. Elements can be of heterogenous types. (docs)
`jsonb_build_object(x: ...) -> jsonb`	The elements of x as a `jsonb` object. The argument list alternates between keys and values. (docs)
`jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>`	`j`'s outermost elements if `j` is an object. (docs)
`jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>`	`j`'s outermost elements if `j` is an object. (docs)
`jsonb_object_keys(j: jsonb) -> Col<string>`	`j`'s outermost keys if `j` is an object. (docs)
`jsonb_pretty(j: jsonb) -> string`	Pretty printed (i.e. indented) `j`. (docs)
`jsonb_typeof(j: jsonb) -> string`	Type of `j`'s outermost value. One of `object`, `array`, `string`, `number`, `boolean`, and `null`. (docs)
`jsonb_strip_nulls(j: jsonb) -> jsonb`	`j` with all object fields with a value of `null` removed. Other `null` values remain. (docs)
`to_jsonb(v: T) -> jsonb`	`v` as `jsonb` (docs)

Table

Table functions evaluate to a set of rows, rather than a single expression.

Function	Computes
`generate_series (start: int, stop: int) -> Col<int>`	Generate all integer values between `start` and `stop`, inclusive.

Operators

Generic

Operator	Computes
`val::type`	Cast of `val` as `type` (docs)

Boolean

Operator	Computes
`AND`	Boolean “and”
`OR`	Boolean “or”
`=`	Equality
`<>`	Inequality
`!=`	Inequality
`<`	Less than
`>`	Greater than
`<=`	Less than or equal to
`>=`	Greater than or equal to
`a BETWEEN x AND y`	`a >= x AND a <= y`
`a NOT BETWEEN x AND y`	`a < x OR a > y`
`a IS NULL`	`a = NULL`
`a IS NOT NULL`	`a != NULL`
`a LIKE match_expr`	`a` matches `match_expr`, using SQL LIKE matching

Numbers

Operator	Computes
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`%`	Modulo

String

Operator	Computes
`\|\|`	Concatenation

Time-like

Operation	Computes
`date` `+` `interval`	`timestamp`
`date` `-` `interval`	`timestamp`
`date` `+` `time`	`timestamp`
`date` `-` `date`	`interval`
`timestamp` `+` `interval`	`timestamp`
`timestamp` `-` `interval`	`timestamp`
`timestamp` `-` `timestamp`	`interval`
`time` `+` `interval`	`time`
`time` `-` `interval`	`time`
`time` `-` `time`	`interval`

JSON

Operator	RHS Type	Description
`->`	`string`, `int`	Access field by name or index position, and return `jsonb` (docs)
`->>`	`string`, `int`	Access field by name or index position, and return `string` ([docs]/sql/types/jsonb/#field-access-as-string–))
`\|\|`	`jsonb`	Concatenate LHS and RHS (docs)
`-`	`string`	Delete all values with key of RHS (docs)
`@>`	`jsonb`	Does element contain RHS? (docs)
`<@`	`jsonb`	Does RHS contain element? (docs)
`?`	`string`	Is RHS a top-level key? (docs)