Functions + Operators
This page details Materialize’s supported SQL functions and operators.
Functions
Generic functions can typically take arguments of any type.
Function
|
Computes
|
|
Value as type T
(docs)
|
|
First non-NULL arg, or NULL if all are NULL
|
|
NULL if x == y , else x
|
Aggregate functions take one or more of the same element type as arguments.
Function
|
Computes
|
|
Number on non-NULL inputs
|
|
Maximum value among T
|
|
Minimum value among T
|
|
Sum of T ’s values
|
|
Historical alias for stddev_samp (imprecise)
|
|
Population standard deviation of T ’s values (imprecise)
|
|
Sample standard deviation of T ’s values (imprecise)
|
|
Historical alias for variance_samp (imprecise)
|
|
Population variance of T ’s values (imprecise)
|
|
Sample variance of T ’s values (imprecise)
|
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
|
Number functions take number-like arguments, e.g. int
, float
, decimal
.
Function
|
Computes
|
|
The absolute value of x
|
|
The largest integer >= x
|
|
The largest integer <= x
|
|
x % y
|
Function
|
Computes
|
|
The ASCII value of s ’s left-most character
|
|
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 functions take a time-like type, e.g. date
, timestamp
, timestamptz
.
Function
|
Computes
|
EXTRACT(extract_expr) -> float
|
Specified time component from value
(docs)
|
|
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 |