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 code points in `s`
`length(s: str) -> int`	Number of code points in `s` (docs)
`length(b: bytea) -> int`	Number of bytes in `s` (docs)
`length(s: bytea, encoding_name: str) -> int`	Number of code points in `s` after encoding (docs)
`lpad(s: str, len: int) -> str`	Prepend `s` with spaces up to length `len`, or right truncate if `len` is less than the length of `s`.
`lpad(s: str, len: int, p: str) -> str`	Prepend `s` with characters pulled from `p` up to length `len`, or right truncate if `len` is less than the length of `s`.
`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`.
`split_part(s: str, d: s, i: int) -> str`	Split `s` on delimiter `d`. Return the `str` at index `i`, counting from 1.
`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

UUID

Function	Computes
`mz_cluster_id() -> uuid`	The `uuid` uniquely identifying this Materialize cluster.

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
`~`	Matches regular expression, case sensitive
`~*`	Matches regular expression, case insensitive
`!~`	Does not match regular expression, case insensitive
`!~*`	Does not match regular expression, case insensitive

The regular expression syntax supported by Materialize is documented by the Rust regex crate.

WARNING! Materialize regular expressions are similar to, but not identical to, PostgreSQL regular expressions.

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)