SQL functions & operators

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 unmaterialized views and one-off SELECT statements.

Unmaterializable functions are marked as such in the table below.

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
`greatest(x: T...) -> T?`	The maximum argument, or NULL if all are NULL
`least(x: T...) -> T?`	The minimum argument, 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
`array_agg(x: T) -> T[]`	Aggregate values (including nulls) as an array. (docs)
`avg(x: T) -> U`	Average of `T`’s values. Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`count(x: T) -> int`	Number of non-NULL inputs.
`jsonb_agg(expression) -> jsonb`	Aggregate values (including nulls) as a jsonb array. (docs)
`jsonb_object_agg(keys, values) -> jsonb`	Aggregate keys and values (including nulls) as a jsonb object. (docs)
`max(x: T) -> T`	Maximum value among `T`
`min(x: T) -> T`	Minimum value among `T`
`stddev(x: T) -> U`	Historical alias for `stddev_samp`. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`stddev_pop(x: T) -> U`	Population standard deviation of `T`’s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`stddev_samp(x: T) -> U`	Sample standard deviation of `T`’s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`string_agg(value: text, delimiter: text) -> text`	Concatenates the non-null input values into text. Each value after the first is preceded by the corresponding delimiter. (docs)
`sum(x: T) -> U`	Sum of `T`’s values Returns `bigint` if `x` is `int`, `numeric` if `x` is `bigint`, else returns same type as `x`.
`variance(x: T) -> U`	Historical alias for `variance_samp`. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`variance_pop(x: T) -> U`	Population variance of `T`’s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.
`variance_samp(x: T) -> U`	Sample variance of `T`’s values. (imprecise) Returns `numeric` if `x` is `int`, `double` if `x` is `real`, else returns same type as `x`.

List

List functions take list arguments, and are polymorphic.

Function	Computes
`list_agg(x: any) -> L`	Aggregate values (including nulls) as a list. (docs)
`list_append(l: listany, e: listelementany) -> L`	Appends `e` to `l`.
`list_cat(l1: listany, l2: listany) -> L`	Concatenates `l1` and `l2`.
`list_n_layers(l: listany) -> int`	Experimental––Returns the number of dimensions on `l`.
`list_length(l: listany) -> int`	Return the number of elements in `l`.
`list_length_max(l: listany, d: int) -> int`	Experimental––Return the greatest length among all lists on dimension `d` of `l`.
`list_prepend(e: listelementany, l: listany) -> listany`	Prepends `e` to `l`.
`list_remove(l: listany, e: listelementany) -> listany`	Experimental––Returns the list `l` without any elements equal to the given value `e`. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.

Numbers

Number functions take number-like arguments, e.g. int, float, numeric, unless otherwise specified.

Function	Computes
`abs(x: N) -> N`	The absolute value of `x`
`cbrt(x: double precision) -> double precision`	The cube root of `x`.
`ceil(x: N) -> N`	The smallest integer >= `x`
`exp(x: N) -> N`	Exponential of `x` (e raised to the given power)
`floor(x: N) -> N`	The largest integer <= `x`
`ln(x: double precision) -> double precision`	Natural logarithm of `x`
`ln(x: numeric) -> numeric`	Natural logarithm of `x`
`log(x: double precision) -> double precision`	Base 10 logarithm of `x`
`log(x: numeric) -> numeric`	Base 10 logarithm of `x`
`log10(x: double precision) -> double precision`	Base 10 logarithm of `x`, same as `log`
`log10(x: numeric) -> numeric`	Base 10 logarithm of `x`, same as `log`
`log(b: numeric, x: numeric) -> numeric`	Base `b` logarithm of `x`
`mod(x: N, y: N) -> N`	`x % y`
`pow(x: double precision, y: double precision) -> double precision`	Alias of `power`
`pow(x: numeric, y: numeric) -> numeric`	Alias of `power`
`power(x: double precision, y: double precision) -> double precision`	`x` raised to the power of `y`
`power(x: numeric, y: numeric) -> numeric`	`x` raised to the power of `y`
`round(x: N) -> N`	`x` rounded to the nearest whole number. If `N` is `real` or `double precision`, rounds ties to the nearest even number. If `N` is `numeric`, rounds ties away from zero.
`round(x: numeric, y: int) -> numeric`	`x` rounded to `y` decimal places, while retaining the same `numeric` scale; rounds ties away from zero.
`sqrt(x: numeric) -> numeric`	The square root of `x`.
`sqrt(x: double precision) -> double precision`	The square root of `x`.

Trigonometric

Trigonometric functions take and return double precision values.

Function	Computes
`cos(x: double precision) -> double precision`	The cosine of `x`, with `x` in radians.
`acos(x: double precision) -> double precision`	The inverse cosine of `x`, result in radians.
`cosh(x: double precision) -> double precision`	The hyperbolic cosine of `x`, with `x` as a hyperbolic angle.
`acosh(x: double precision) -> double precision`	The inverse hyperbolic cosine of `x`.
`cot(x: double precision) -> double precision`	The cotangent of `x`, with `x` in radians.
`sin(x: double precision) -> double precision`	The sine of `x`, with `x` in radians.
`asin(x: double precision) -> double precision`	The inverse sine of `x`, result in radians.
`sinh(x: double precision) -> double precision`	The hyperbolic sine of `x`, with `x` as a hyperbolic angle.
`asinh(x: double precision) -> double precision`	The inverse hyperbolic sine of `x`.
`tan(x: double precision) -> double precision`	The tangent of `x`, with `x` in radians.
`asin(x: double precision) -> double precision`	The inverse tangent of `x`, result in radians.
`tanh(x: double precision) -> double precision`	The hyperbolic tangent of `x`, with `x` as a hyperbolic angle.
`atanh(x: double precision) -> double precision`	The inverse hyperbolic tangent of `x`.
`radians(x: double precision) -> double precision`	Converts degrees to radians.
`degrees(x: double precision) -> double precision`	Converts radians to degrees.

String

Function	Computes
`ascii(s: str) -> int`	The ASCII value of `s`’s left-most character
`btrim(s: str) -> str`	Trim all spaces from both sides of `s`.
`btrim(s: str, c: str) -> str`	Trim any character in `c` from both sides of `s`.
`bit_length(s: str) -> int`	Number of bits in `s`
`bit_length(b: bytea) -> int`	Number of bits in `b`
`char_length(s: str) -> int`	Number of code points in `s`
`chr(i: int) -> str`	Character with the given Unicode codepoint. Only supports codepoints that can be encoded in UTF-8. The NULL (0) character is not allowed.
`convert_from(b: bytea, src_encoding: text) -> text`	Convert data `b` from original encoding specified by `src_encoding` into `text`.
`decode(s: text, format: text) -> bytea`	Decode `s` using the specified textual representation. (docs)
`encode(b: bytea, format: text) -> text`	Encode `b` using the specified textual representation. (docs)
`left(s: str, n: int) -> str`	The first `n` characters of `s`. If `n` is negative, all but the last `\|n\|` characters of `s`.
`length(s: str) -> int`	Number of code points in `s` (docs)
`length(b: bytea) -> int`	Number of bytes in `s` (docs)
`length(s: bytea, encoding_name: str) -> int`	Number of code points in `s` after encoding (docs)
`lower(s: str) -> str`	Convert `s` to lowercase.
`lpad(s: str, len: int) -> str`	Prepend `s` with spaces up to length `len`, or right truncate if `len` is less than the length of `s`.
`lpad(s: str, len: int, p: str) -> str`	Prepend `s` with characters pulled from `p` up to length `len`, or right truncate if `len` is less than the length of `s`.
`ltrim(s: str) -> str`	Trim all spaces from the left side of `s`.
`ltrim(s: str, c: str) -> str`	Trim any character in `c` from the left side of `s`.
`octet_length(s: str) -> int`	Number of bytes in `s`
`octet_length(b: bytea) -> int`	Number of bytes in `b`
`position(sub: str IN s: str) -> int`	The starting index of `sub` within `s` or `0` if `sub` is not a substring of `s`.
`regexp_match(haystack: str, needle: str [, flags: str]]) -> str[]`	Matches the regular expression `needle` against haystack, returning a string array that contains the value of each capture group specified in `needle`, in order. If `flags` is set to the string `i` matches case-insensitively.
`repeat(s: str, n: int) -> str`	Replicate the string `n` times.
`replace(s: str, f: str, r: str) -> str`	`s` with all instances of `f` replaced with `r`
`right(s: str, n: int) -> str`	The last `n` characters of `s`. If `n` is negative, all but the first `\|n\|` characters of `s`.
`rtrim(s: str) -> str`	Trim all spaces from the right side of `s`.
`rtrim(s: str, c: str) -> str`	Trim any character in `c` from the right side of `s`.
`split_part(s: str, d: s, i: int) -> str`	Split `s` on delimiter `d`. Return the `str` at index `i`, counting from 1.
`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)
`substring('s' [FROM 'start_pos']? [FOR 'l']?) -> str`	Substring starting at `start_pos` of length `l` (docs)
`trim([BOTH \| LEADING \| TRAILING]? 'c'? FROM 's') -> str`	Trims any character in `c` from `s` on the specified side. Defaults: • Side: `BOTH` • `'c'`: `' '` (space)
`upper(s: str) -> str`	Convert `s` to uppercase.

Scalar

Scalar functions take a list of scalar expressions

Function	Computes
`expression bool_op ALL(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
`expression bool_op ANY(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
`expression IN(s: Scalars) -> bool`	`true` for each value in `expression` if it matches at least one element of `s`
`expression NOT IN(s: Scalars) -> bool`	`true` for each value in `expression` if it does not match any elements of `s`
`expression bool_op SOME(s: Scalars) -> bool`	`true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

Subquery

Subquery functions take a query, e.g. SELECT

Function	Computes
`expression bool_op ALL(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and every value of `s` evaluates to `true`
`expression bool_op ANY(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`
`csv_extract(num_csv_col: int, col_name: string) -> col1: string, ... coln: string`	Extracts separated values from a column containing a CSV file formatted as a string. (docs)
`EXISTS(s: Query) -> bool`	`true` if `s` returns at least one row
`expression IN(s: Query) -> bool`	`s` must return exactly one column; `true` for each value in `expression` if it matches at least one element of `s`
`NOT EXISTS(s: Query) -> bool`	`true` if `s` returns zero rows
`expression NOT IN(s: Query) -> bool`	`s` must return exactly one column; `true` for each value in `expression` if it does not match any elements of `s`
`expression bool_op SOME(s: Query) -> bool`	`s` must return exactly one column; `true` if applying bool_op to `expression` and any value of `s` evaluates to `true`

Date and Time

Time functions take or produce a time-like type, e.g. date, timestamp, timestamp with time zone.

Function	Computes
`current_timestamp() -> timestamptz`	The `timestamp with time zone` representing when the query was executed. Note: This function is unmaterializable.
`date_bin(stride: interval, source: timestamp, origin: timestamp) -> timestamp`	Align `source` with `origin` along `stride`. (docs)
`date_bin(stride: interval, source: timestamp) -> timestamp`	Experimental: Align `source` with the UNIX epoch along `stride`. (docs)
`date_trunc(time_component: str, val: timestamp) -> timestamp`	Largest `time_component` <= `val` (docs)
`date_trunc(time_component: str, val: interval) -> interval`	Largest `time_component` <= `val` (docs)
`EXTRACT(extract_expr) -> numeric`	Specified time component from value (docs)
`date_part(time_component: str, val: timestamp) -> float`	Specified time component from value (docs)
`mz_logical_timestamp() -> numeric`	The logical time at which a query executes. Used for temporal filters and internal debugging. Note: This function generally behaves like a [non-pure] function, but can be used in limited contexts in materialized views as a temporal filter. (docs) Note: This function is unmaterializable.
`now() -> timestamptz`	The `timestamp with time zone` representing when the query was executed. (docs) Note: This function is unmaterializable.
`timestamp AT TIME ZONE zone -> timestamptz`	Converts `timestamp` to the specified time zone, expressed as an offset from UTC. Known limitation: You must explicitly cast the type for the time zone. (docs)
`timestamptz AT TIME ZONE zone -> timestamp`	Converts `timestamp with time zone` from UTC to the specified time zone, expressed as the local time. Known limitation: You must explicitly cast the type for the time zone. (docs)
`timezone(zone, timestamp) -> timestamptz`	Converts `timestamp` to specified time zone, expressed as an offset from UTC. Known limitation: You must explicitly cast the type for the time zone. (docs)
`timezone(zone, timestamptz) -> timestamp`	Converts `timestamp with time zone` from UTC to specified time zone, expressed as the local time. Known limitation: You must explicitly cast the type for the time zone. (docs)
`to_timestamp(val: double precision) -> timestamptz`	Converts Unix epoch (seconds since 00:00:00 UTC on January 1, 1970) to timestamp
`to_char(val: timestamp, format: str)`	Converts a timestamp into a string using the specified format. (docs)
`justify_days(val: interval) -> interval`	Adjust interval so 30-day time periods are represented as months. (docs)
`justify_hours(val: interval) -> interval`	Adjust interval so 24-hour time periods are represented as days. (docs)
`justify_interval(val: interval) -> interval`	Adjust interval using justify_days and justify_hours, with additional sign adjustments. (docs)

UUID

Function	Computes
`mz_cluster_id() -> uuid`	The `uuid` uniquely identifying this Materialize cluster. Note: This function is unmaterializable.

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)

Table

Table functions evaluate to a set of rows, rather than a single expression.

Function	Computes
`generate_series(start: int, stop: int) -> Col<int>`	Generate all integer values between `start` and `stop`, inclusive.
`generate_series(start: int, stop: int, step: int) -> Col<int>`	Generate all integer values between `start` and `stop`, inclusive, incrementing by `step` each time.
`generate_series(start: timestamp, stop: timestamp, step: interval) -> Col<timestamp>`	Generate all timestamp values between `start` and `stop`, inclusive, incrementing by `step` each time.
`generate_subscripts(a: anyarray, dim: int) -> Col<int>`	Generates a series comprising the valid subscripts of the `dim`‘th dimension of the given array `a`.
`regexp_extract(regex: str, haystack: str) -> Col<string>`	Values of the capture groups of `regex` as matched in `haystack`
`unnest(a: anyarray)`	Expands the array `a` into a set of rows.
`unnest(l: anylist)`	Expands the list `l` into a set of rows.

Array

Function	Computes
`array_cat(a1: arrayany, a2: arrayany) -> arrayany`	Concatenates `a1` and `a2`.
`array_to_string(a: anyarray, sep: text [, ifnull: text]) -> text`	Concatenates the elements of `array` together separated by `sep`. Null elements are omitted unless `ifnull` is non-null, in which case null elements are replaced with the value of `ifnull`.
`array_remove(a: anyarray, e: anyelement) -> anyarray`	Returns the array `a` without any elements equal to the given value `e`. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.

Cryptography

Function	Computes
`digest(data: text, type: text) -> bytea`	Computes a binary hash of the given text `data` using the specified `type` algorithm. Supported hash algorithms are: `md5`, `sha1`, `sha224`, `sha256`, `sha384`, and `sha512`.
`digest(data: bytea, type: text) -> bytea`	Computes a binary hash of the given bytea `data` using the specified `type` algorithm. The supported hash algorithms are the same as for the text variant of this function.
`hmac(data: text, key: text, type: text) -> bytea`	Computes a hashed MAC of the given text `data` using the specified `key` and `type` algorithm. Supported hash algorithms are the same as for `digest`.
`hmac(data: bytea, key: bytea, type: text) -> bytea`	Computes a hashed MAC of the given bytea `data` using the specified `key` and `type` algorithm. The supported hash algorithms are the same as for `digest`.
`md5(data: bytea) -> text`	Computes the MD5 hash of the given bytea `data`. For PostgreSQL compatibility, returns a hex-encoded value of type `text` rather than `bytea`.
`sha224(data: bytea) -> bytea`	Computes the SHA-224 hash of the given bytea `data`.
`sha256(data: bytea) -> bytea`	Computes the SHA-256 hash of the given bytea `data`.
`sha384(data: bytea) -> bytea`	Computes the SHA-384 hash of the given bytea `data`.
`sha512(data: bytea) -> bytea`	Computes the SHA-512 hash of the given bytea `data`.

Window

Window functions compute values across sets of rows related to the current query.

Function	Computes
`dense_rank() -> int`	Returns the rank of the current row within its partition without gaps, counting from 1.
`first_value(value anycompatible) -> anyelement`	Returns `value` evaluated at the first row of the window frame. The default window frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.
`lag(value anycompatible [, offset integer [, default anycompatible ]]) -> int`	Returns `value` evaluated at the row that is `offset` rows before the current row within the partition; if there is no such row, instead returns `default` (which must be of a type compatible with `value`). If `offset` is `NULL`, `NULL` is returned instead. Both `offset` and `default` are evaluated with respect to the current row. If omitted, `offset` defaults to 1 and `default` to `NULL`.
`last_value(value anycompatible) -> anyelement`	Returns `value` evaluated at the last row of the window frame. The default window frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.
`lead(value anycompatible [, offset integer [, default anycompatible ]]) -> int`	Returns `value` evaluated at the row that is `offset` rows after the current row within the partition; if there is no such row, instead returns `default` (which must be of a type compatible with `value`). If `offset` is `NULL`, `NULL` is returned instead. Both `offset` and `default` are evaluated with respect to the current row. If omitted, `offset` defaults to 1 and `default` to `NULL`.
`row_number() -> int`	Returns the number of the current row within its partition, counting from 1.

System information

Functions that return information about the system

Function	Computes
`mz_uptime() -> interval`	Returns the length of time that the materialized process has been running. Note: This function is unmaterializable.
`mz_version() -> text`	Returns the server’s version information as a human-readable string. Note: This function is unmaterializable.
`current_database() -> text`	Returns the name of the current database. Note: This function is unmaterializable.
`current_role() -> text`	Alias for `current_user`. Note: This function is unmaterializable.
`current_user() -> text`	Returns the name of the user who executed the containing query. Note: This function is unmaterializable.
`session_user() -> text`	Returns the name of the user who executed the containing query. Note: This function is unmaterializable.
`mz_row_size(expr: Record) -> int`	Returns the number of bytes used to store a row.

PostgreSQL compatibility

Functions whose primary purpose is to facilitate compatibility with PostgreSQL tools. These functions may have suboptimal performance characteristics.

Function	Computes
`format_type(oid: int, typemod: int) -> text`	Returns the canonical SQL name for the type specified by `oid` with `typemod` applied.
`current_schema() -> text`	Returns the name of the first non-implicit schema on the search path, or `NULL` if the search path is empty. Note: This function is unmaterializable.
`current_schemas(include_implicit: bool) -> text[]`	Returns the names of the schemas on the search path. The `include_implicit` parameter controls whether implicit schemas like `mz_catalog` and `pg_catalog` are included in the output. Note: This function is unmaterializable.
`obj_description(oid: oid, catalog: text) -> text`	PostgreSQL compatibility shim. Currently always returns `NULL`.
`pg_backend_pid() -> int`	Returns the internal connection ID. Note: This function is unmaterializable.
`pg_column_size(expr: any) -> int`	Returns the number of bytes used to store any individual data value.
`pg_get_constraintdef(oid: oid[, pretty: bool]) -> text`	Returns the constraint definition for the given `oid`. Currently always returns NULL since constraints aren’t supported.
`pg_get_indexdef(index: oid[, column: integer, pretty: bool]) -> text`	Reconstructs the creating command for an index. (This is a decompiled reconstruction, not the original text of the command.) If column is supplied and is not zero, only the definition of that column is reconstructed.
`pg_get_viewdef(view_name: text[, pretty: bool]) -> text`	Returns the underlying SELECT command for the given view.
`pg_get_viewdef(view_oid: oid[, pretty: bool]) -> text`	Returns the underlying SELECT command for the given view.
`pg_get_viewdef(view_oid: oid[, wrap_column: integer]) -> text`	Returns the underlying SELECT command for the given view.
`pg_table_is_visible(relation: oid) -> boolean`	Reports whether the relation with the specified OID is visible in the search path.
`pg_type_is_visible(relation: oid) -> boolean`	Reports whether the type with the specified OID is visible in the search path.
`pg_typeof(expr: any) -> text`	Returns the type of its input argument as a string.
`pg_encoding_to_char(encoding_id: integer) -> text`	PostgreSQL compatibility shim. Not intended for direct use.
`pg_postmaster_start_time() -> timestamptz`	Returns the time when the server started. Note: This function is unmaterializable.
`version() -> text`	Returns a PostgreSQL-compatible version string. Note: This function is unmaterializable.

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 ISNULL`	`a = NULL`
`a IS NOT NULL`	`a != 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

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

String

Operator	Computes
`\|\|`	Concatenation
`~`	Matches regular expression, case sensitive
`~*`	Matches regular expression, case insensitive
`!~`	Does not match regular expression, case insensitive
`!~*`	Does not match regular expression, case insensitive

The regular expression syntax supported by Materialize is documented by the Rust regex crate.

WARNING! Materialize regular expressions are similar to, but not identical to, PostgreSQL regular expressions.

Time-like

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

JSON

Operator	RHS Type	Description
`->`	`text`, `int`	Access field by name or index position, and return `jsonb` (docs)
`->>`	`text`, `int`	Access field by name or index position, and return `text` (docs)
`#>`	`text[]`	Access field by path, and return `jsonb` (docs)
`#>>`	`text[]`	Access field by path, and return `text` (docs)
`\|\|`	`jsonb`	Concatenate LHS and RHS (docs)
`-`	`text`	Delete all values with key of RHS (docs)
`@>`	`jsonb`	Does element contain RHS? (docs)
`<@`	`jsonb`	Does RHS contain element? (docs)
`?`	`text`	Is RHS a top-level key? (docs)

Map

Operator	RHS Type	Description
`->`	`string`	Access field by name, and return target field (docs)
`@>`	`map`	Does element contain RHS? (docs)
`<@`	`map`	Does RHS contain element? (docs)
`?`	`string`	Is RHS a top-level key? (docs)
`?&`	`string[]`	Does LHS contain all RHS top-level keys? (docs)
`?\|`	`string[]`	Does LHS contain any RHS top-level keys? (docs)

List

List operators are polymorphic.

Operator	Description
`listany \|\| listany`	Concatenate the two lists.
`listany \|\| listelementany`	Append the element to the list.
`listelementany \|\| listany`	Prepend the element to the list.