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
count(x: T) -> int
Number of non-NULL inputs
max(x: T) -> T
Maximum value among T
min(x: T) -> T
Minimum value among T
sum(x: T) -> T
Sum of T’s values
stddev(x: T) -> T
Historical alias for stddev_samp (imprecise)
stddev_pop(x: T) -> T
Population standard deviation of T’s values (imprecise)
stddev_samp(x: T) -> T
Sample standard deviation of T’s values (imprecise)
variance(x: T) -> T
Historical alias for variance_samp (imprecise)
variance_pop(x: T) -> T
Population variance of T’s values (imprecise)
variance_samp(x: T) -> T
Sample variance of T’s values (imprecise)
jsonb_agg(expression) -> jsonb
Aggregate values (including nulls) as a jsonb array. (docs)

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
length(s: str) -> int
Number of graphemes in s (docs)
length(s: str, encoding_name: str) -> int
Number of graphemes in s using encoding_name (docs)
replace(s: str, f: str, r: str) -> str
s with all instances of f replaced with r
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)
regexp_extract(regex: str, haystack: str) -> Col<string>
Values of the capture groups of regex as matched in haystack

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)

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)
|| 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)