Materialize

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)