Materialize Logo

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)