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)

Column

Column functions take column name arguments.

Function Computes
lhs bool_op ALL(c: C) -> bool
true if applying bool_op to lhs and every value of C evaluates to true
lhs bool_op ANY(c: C) -> bool
true if applying bool_op to lhs and any value of C evaluates to true

Numbers

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

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

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)

Time

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

Function Computes
date_trunc(time_component: str, val: timestamp) -> timestamp
Largest time_component <= val (docs)
EXTRACT(extract_expr) -> float
Specified time component from value (docs)
now() -> timestamptz
The timestamptz representing the time at which this dataflow was created.

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

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)