Materialize Documentation
Join the Community github/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
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)
array_agg(x: T) -> T[]
Aggregate values (including nulls) as an array.
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, delimeter: 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.
list_append(l: listany, e: listelementany) -> L
Appends e to l.
list_cat(l1: listany, l2: listany) -> L
Concatenates l1 and l2.
list_ndims(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.

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.
cosh(x: double precision) -> double precision
The hyperbolic cosine of x, with x as a hyperbolic angle.
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.
sinh(x: double precision) -> double precision
The hyperbolic sine of x, with x as a hyperbolic angle.
tan(x: double precision) -> double precision
The tangent of x, with x in radians.
tanh(x: double precision) -> double precision
The hyperbolic tangent of x, with x as a hyperbolic angle.

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
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)
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: Users cannot define views with queries containing current_timestamp().
date_trunc(time_component: str, val: timestamp) -> timestamp
Largest time_component <= val (docs)
EXTRACT(extract_expr) -> 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. (docs)
now() -> timestamptz
The timestamp with time zone representing when the query was executed.

NOTE: Users cannot define views with queries containing now(). (docs)
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)

UUID

Function Computes
mz_cluster_id() -> uuid
The uuid uniquely identifying this Materialize cluster.

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

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.

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.
mz_version() -> text
Returns the server’s version information as a human-readable string.
mz_workers() -> bigint
Returns the number of workers in use by the server.
format_type(oid: int, typemod: int) -> text
Returns the canonical SQL name for the type specified by oid with typemod applied.
current_role() -> text
Alias for current_user.
current_user() -> text
Returns the name of the user who executed the containing query.
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

Function Computes
current_schema() -> text
Returns the name of the first non-implicit schema on the search path, or NULL if the search path is empty.
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.
obj_description(oid: oid, catalog: text) -> text
PostgreSQL compatibility shim. Currently always returns NULL.
pg_column_size(expr: any) -> int
Returns the number of bytes used to store any individual data value.
pg_table_is_visible(relation: oid) -> boolean
Reports whether the relation 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.
version() -> text
Returns a PostgreSQL-compatible version string.

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 LIKE match_expr a matches match_expr, using SQL LIKE matching
a ILIKE match_expr a matches match_expr, using case-insensitive SQL LIKE matching

Numbers

Operator Computes
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo
& Bitwise AND
| Bitwise OR
# 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.
Did this info help?
Yes No