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 on 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
EXTRACT(extract_expr) -> float
Specified time component from value (docs)
now() -> timestamptz
The timestamptz representing the time at which this dataflow was created.

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