SQL functions & operators - Materialize

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

Functions

Unmaterializable functions

Several functions in Materialize are unmaterializable because their output depends upon state besides their input parameters, like the value of a session parameter or the timestamp of the current transaction. You cannot create an index or materialized view that depends on an unmaterializable function, but you can use them in non-materialized views and one-off SELECT statements.

Unmaterializable functions are marked as such in the table below.

Side-effecting functions

Several functions in Materialize are side-effecting because their evaluation changes system state. For example, the pg_cancel_backend function allows canceling a query running on another connection.

Materialize offers only limited support for these functions. They may be called only at the top level of a SELECT statement, like so:

mzsql

SELECT side_effecting_function(arg, ...);

You cannot manipulate or alias the function call expression, call multiple side-effecting functions in the same SELECT statement, nor add any additional clauses to the SELECT statement (e.g., FROM, WHERE).

Side-effecting functions are marked as such in the table below.

Operators

Generic operators

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

Boolean operators

Operator	Computes
`AND`	Boolean "and"
`OR`	Boolean "or"
`=`	Equality. Do not use with `NULL` as `= NULL` always evaluates to `NULL`; instead, use `IS NULL` for null checks.
`<>`	Inequality. Do not use with `NULL` as `<> NULL` always evaluates to `NULL`; instead, use `IS NOT NULL` for null checks.
`!=`	Inequality. Do not use with `NULL` as `!= NULL` always evaluates to `NULL`; instead, use `IS NOT NULL` for null checks.
`<`	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`	Evaluates to true if the value of a is `NULL`.
`a ISNULL`	Evaluates to true if the value of a is `NULL`.
`a IS NOT NULL`	Evaluates to true if the value of a is NOT `NULL`.
`a IS TRUE`	`a` is true, requiring `a` to be a boolean
`a IS NOT TRUE`	`a` is not true, requiring `a` to be a boolean
`a IS FALSE`	`a` is false, requiring `a` to be a boolean
`a IS NOT FALSE`	`a` is not false, requiring `a` to be a boolean
`a IS UNKNOWN`	`a = NULL`, requiring `a` to be a boolean
`a IS NOT UNKNOWN`	`a != NULL`, requiring `a` to be a boolean
`a LIKE match_expr [ ESCAPE escape_char ]`	`a` matches `match_expr`, using SQL LIKE matching
`a ILIKE match_expr [ ESCAPE escape_char ]`	`a` matches `match_expr`, using case-insensitive SQL LIKE matching

Numbers operators

Operator	Computes
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`%`	Modulo
`&`	Bitwise AND
<code>\|</code>	Bitwise OR
`#`	Bitwise XOR
`~`	Bitwise NOT
`<<`	Bitwise left shift
`>>`	Bitwise right shift

String operators

Operator	Computes
<code>\|\|</code>	Concatenation
`~~`	Matches LIKE pattern case sensitively, see SQL LIKE matching
`~~*`	Matches LIKE pattern case insensitively (ILIKE), see SQL LIKE matching
`!~~`	Matches NOT LIKE pattern (case sensitive), see SQL LIKE matching
`!~~*`	Matches NOT ILIKE pattern (case insensitive), see SQL LIKE matching
`~`	Matches regular expression, case sensitive
`~*`	Matches regular expression, case insensitive
`!~`	Matches regular expression case sensitively, and inverts the match
`!~*`	Match regular expression case insensitively, and inverts the match

The regular expression syntax supported by Materialize is documented by the Rust regex crate. The maximum length of a regular expression is 1 MiB in its raw form, and 10 MiB after compiling it.

{{< warning >}} Materialize regular expressions are similar to, but not identical to, PostgreSQL regular expressions. {{< /warning >}}

Time-like operators

Operation	Computes
`date` `+` `interval`	`timestamp`
`date` `-` `interval`	`timestamp`
`date` `+` `time`	`timestamp`
`date` `-` `date`	`integer`
`timestamp` `+` `interval`	`timestamp`
`timestamp` `-` `interval`	`timestamp`
`timestamp` `-` `timestamp`	`interval`
`time` `+` `interval`	`time`
`time` `-` `interval`	`time`
`time` `-` `time`	`interval`

JSON operators

Map operators

List operators

List operators are polymorphic.