Functions + Operators

This page details Materialize’s supported SQL functions and operators.

Functions

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 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 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
`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 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.

Operator	Computes
`val::type`	Cast of `val` as `type` (docs)

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