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 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:

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.

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.
bool_and(x: T) -> T
NULL if all values of x are NULL, otherwise true if all values of x are true, otherwise false.
bool_or(x: T) -> T
NULL if all values of x are NULL, otherwise true if any values of x are true, otherwise false.
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 or smallint, numeric if x is bigint or uint8, uint8 if x is uint4 or uint2, else returns same type as x.
variance(x: T) -> U
Historical alias for var_samp. (imprecise)

Returns numeric if x is int, double if x is real, else returns same type as x.
var_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.
var_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_length(l: listany) -> int
Return the number of elements in l.
list_prepend(e: listelementany, l: listany) -> listany
Prepends e to l.

Map

Map functions take map arguments, and are polymorphic.

Function Computes
map_length(m: mapany) -> int
Return the number of elements in m.

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
ceiling(x: N) -> N
Alias of ceil
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.
trunc(x: N) -> N
x truncated toward zero to a whole number

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.
atan(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.
concat(f: any, r: any...) -> text
Concatenates the text representation of non-NULL arguments`.
concat_ws(sep: str, f: any, r: any...) -> text
Concatenates the text representation of non-NULL arguments from f and r separated by sep.
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)
get_byte(b: bytea, n: int) -> int
Return the nth byte from b, where the left-most byte in b is at the 0th position.
constant_time_eq(a: bytea, b: bytea) -> bool
Performs a constant-time comparison between byte arrays a and b. Returns true if the arrays are identical, otherwise returns false. This method is designed to mitigate timing attacks by ensuring the comparison takes the same amount of time regardless of the content of the arrays.
constant_time_eq(a: text, b: text) -> bool
Performs a constant-time comparison between strings a and b by first converting them to byte sequences. Returns true if the strings are identical, otherwise returns false. This method is designed to mitigate timing attacks by ensuring the comparison takes the same amount of time regardless of the content of the strings.
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
parse_ident(ident: str[, strict_mode: bool]) -> str[]
Splits a qualified identifier into an array of identifiers, removing any quoting of individual identifiers. Extra characters after the last identifier are considered an error, unless strict_mode is false or elided, then such extra characters are ignored. (This behavior is useful for parsing names for objects like functions.) Note that this function does not truncate over-length identifiers. (From PG)
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.
regexp_replace(source: str, pattern: str, replacement: str [, flags: str]]) -> str
Replaces the first occurrence of pattern with replacement in source. No match will return source unchanged. If flags is set to g, all occurrences are replaced. If flags is set to i, matches case-insensitively. $N or $name in replacement can be used to match capture groups. ${N} must be used to disambiguate capture group indexes from names if other characters follow N. A $$ in replacement will write a literal $. See the rust regex docs for more details about replacement.
regexp_split_to_array(text: str, pattern: str [, flags: str]]) -> str[]
Splits text by the regular expression pattern into an array. If flags is set to 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
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)
translate(s: str, from: str, to: str) -> str
Any character in s that matches a character in from is replaced by the corresponding character in to. If from is longer than to, occurrences of the extra characters in from are removed.
trim([BOTH | LEADING | TRAILING]? ['c'? FROM]? 's') -> str
Trims any character in c from s on the specified side.

Defaults:
• Side: BOTH
'c': ' ' (space)
try_parse_monotonic_iso8601_timestamp(s: str) -> timestamp
Parses a specific subset of ISO8601 timestamps, returning NULL instead of error on failure: YYYY-MM-DDThh:mm:ss.sssZ (docs)
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
age(timestamp, timestamp) -> interval
Subtracts one timestamp from another, producing a “symbolic” result that uses years and months, rather than just days.
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_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_now() -> mz_timestamp
The logical time at which a query executes. Used for temporal filters and query timestamp introspection.

Note: This function generally behaves like an unmaterializable 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)
timezone_offset(zone: str, when: timestamptz) ->
(abbrev: str, base_utc_offset: interval, dst_offset: interval)
Describes a time zone’s offset from UTC at a specified moment. zone must be a valid IANA Time Zone Database identifier. when is a timestamp with time zone that specifies the moment at which to determine zone’s offset from UTC. abbrev is the abbreviation for zone that is in use at the specified moment (e.g., EST or EDT). base_utc_offset is the base offset from UTC at the specified moment (e.g., -5 hours). Positive offsets mean east of Greenwich; negative offsets mean west of Greenwich. dst_offset is the additional offset at the specified moment due to Daylight Saving Time rules (e.g., 1 hours). If non-zero, Daylight Saving Time is in effect.
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
uuid_generate_v5(namespace: uuid, name: text) -> uuid
Generates a version 5 UUID (SHA-1) in the given namespace using the specified input name.

JSON

Function Computes
jsonb_agg(expression) -> jsonb
Aggregate values (including nulls) as a jsonb array. (docs)
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
Output each element of x as 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_agg(keys, values) -> jsonb
Aggregate keys and values (including nulls) as a jsonb 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
regexp_split_to_table(text: str, pattern: str [, flags: str]]) -> Col<string>
Splits text by the regular expression pattern. If flags is set to i, matches case-insensitively.
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_fill(anyelement, int[], [, int[]]) -> anyarray
Returns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1. (From PG)
array_position(haystack: anycompatiblearray, needle: anycompatible) -> int
Returns the subscript of needle in haystack. Returns null if not found.
array_position(haystack: anycompatiblearray, needle: anycompatible, skip: int) -> int
Returns the subscript of needle in haystack, skipping the first skip elements. Returns null if not found.
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 row. For example, you can use a window aggregation to smooth measurement data by computing the average of the last 5 measurements before every row as follows: SELECT avg(measurement) OVER (ORDER BY time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) FROM measurements;. Window functions always need an OVER clause. For the OVER clause, Materialize supports the same syntax as PostgreSQL, but currently only with the ROWS frame mode (except for the default frame, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and is supported). In addition to the below window functions, you can use the OVER clause with any aggregation function (e.g., sum, avg) as well. Using an aggregation with an OVER clause is called a window aggregation. A window aggregation computes the aggregate not on the groups specified by the GROUP BY clause, but on the frames specified inside the OVER clause. (Note that a window aggregation produces exactly one output value for each input row. This is different from a standard aggregation, which produces one output value for each group specified by the GROUP BY clause.)

Function Computes
dense_rank() -> int
Returns the rank of the current row within its partition without gaps, counting from 1. Rows that compare equal will have the same rank.
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.
rank() -> int
Returns the rank of the current row within its partition with gaps (counting from 1): rows that compare equal will have the same rank, and then the rank is incremented by the number of rows that compared equal.
row_number() -> int
Returns the number of the current row within its partition, counting from 1. Rows that compare equal will be ordered in an unspecified way.

System information

Functions that return information about the system.

Function Computes
mz_environment_id() -> text
Returns a string containing a uuid uniquely identifying the Materialize environment.

Note: This function is unmaterializable.
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.
mz_version_num() -> int
Returns the server’s version as an integer having the format XXYYYZZ, where XX is the major version, YYY is the minor version and ZZ is the patch version.

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 initiated the database connection.

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.
current_setting(setting_name: text[, missing_ok: bool]) -> text
Returns the value of the named setting or error if it does not exist. If missing_ok is true, return NULL if it does not exist.

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_cancel_backend(connection_id: int) -> bool
Cancels an in-progress query on the specified connection ID. Returns whether the connection ID existed (not if it cancelled a query).

Note: This function is side-effecting.
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_ruledef(rule_oid: oid[, pretty bool]) -> text
Reconstructs the creating command for a rule. This function always returns NULL because Materialize does not support rules.
pg_get_userbyid(role: oid) -> text
Returns the role (user) name for the given oid. If no role matches the specified OID, the string unknown (OID=oid) is returned.
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_has_role([user: name or oid,] role: text or oid, privilege: text) -> bool
Alias for has_role for PostgreSQL compatibility.
pg_is_in_recovery() -> bool
Returns if the a recovery is still in progress.
pg_table_is_visible(relation: oid) -> boolean
Reports whether the relation with the specified OID is visible in the search path.
pg_tablespace_location(tablespace: oid) -> text
Returns the path in the file system that the provided tablespace is on.
pg_type_is_visible(relation: oid) -> boolean
Reports whether the type with the specified OID is visible in the search path.
pg_function_is_visible(relation: oid) -> boolean
Reports whether the function 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.
pg_relation_size(relation: regclass[, fork: text]) -> bigint
Disk space used by the specified fork (‘main’, ‘fsm’, ‘vm’, or ‘init’) of the specified table or index. If no fork is specified, it defaults to ‘main’. This function always returns -1 because Materialize does not store tables and indexes on local disk.
pg_stat_get_numscans(oid: oid) -> bigint
Number of sequential scans done when argument is a table, or number of index scans done when argument is an index. This function always returns -1 because Materialize does not collect statistics.
version() -> text
Returns a PostgreSQL-compatible version string.

Note: This function is unmaterializable.

Access privilege inquiry

Functions that allow querying object access privileges. All of the following functions have limitations involved in resolving the text names passed in. None of the object names can have any schema or database qualifiers. For example, has_table_privilege('public.t', 'SELECT') will not work properly. Additionally, the name lookup does not consider the current search path or active database.

Function Computes
has_cluster_privilege([role: text or oid,] cluster: text, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the cluster with the specified cluster name. If the role is omitted then the current_role is assumed.
has_connection_privilege([role: text or oid,] connection: text or oid, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the connection with the specified connection name or OID. If the role is omitted then the current_role is assumed.
has_database_privilege([role: text or oid,] database: text or oid, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the database with the specified database name or OID. If the role is omitted then the current_role is assumed.
has_schema_privilege([role: text or oid,] schema: text or oid, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the schema with the specified schema name or OID. If the role is omitted then the current_role is assumed.
has_role([user: name or oid,] role: text or oid, privilege: text) -> bool
Reports whether the user has the privilege for role. privilege can either be MEMBER or USAGE, however currently this value is ignored. The PUBLIC pseudo-role cannot be used for the user nor the role. If the user is omitted then the current_role is assumed.
has_secret_privilege([role: text or oid,] secret: text or oid, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the secret with the specified secret name or OID. If the role is omitted then the current_role is assumed.
has_system_privilege([role: text or oid,] privilege: text) -> bool
Reports whether the role with the specified role name or OID has the system privilege. If the role is omitted then the current_role is assumed.
has_table_privilege([role: text or oid,] relation: text or oid, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the relation with the specified relation name or OID. If the role is omitted then the current_role is assumed.
has_type_privilege([role: text or oid,] type: text or oid, privilege: text) -> bool
Reports whether the role with the specified role name or OID has the privilege on the type with the specified type name or OID. If the role is omitted then the current_role is assumed.
mz_is_superuser() -> bool
Reports whether the current_role is a superuser.

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 OR
# Bitwise XOR
~ Bitwise NOT
<< Bitwise left shift
>> Bitwise right shift

String

Operator Computes
|| 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.

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.
Back to top ↑