# SQL functions & operators Learn more about the SQL functions and operators supported in Materialize > For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt). This page details Materialize's supported SQL [functions](#functions) and [operators](#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](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-index/index.md) or materialized view that depends on an unmaterializable function, but you can use them in non-materialized views and one-off [`SELECT`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/select/index.md) 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. ### Generic functionsGeneric functions can typically take arguments of any type.#### `CAST (cast_expr) -> T` Value as type T [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/cast/index.md)#### `TRY_CAST (cast_expr) -> T?` Value as type T, or NULL if the conversion fails. [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/cast/index.md#try_cast)#### `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 functionsAggregate functions take one or more of the same element type as arguments.#### `array_agg(x: T) -> T[]` Aggregate values (including nulls) as an array [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/array_agg/index.md)#### `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.

For real and double precision inputs, see aggregate precision.

#### `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) -> bigint` Number of non-NULL inputs.#### `jsonb_agg(expression) -> jsonb` Aggregate values (including nulls) as a jsonb array [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/jsonb_agg/index.md)#### `jsonb_object_agg(keys, values) -> jsonb` Aggregate keys and values (including nulls) as a jsonb object [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/jsonb_object_agg/index.md)#### `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)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/string_agg/index.md)#### `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.

For real and double precision inputs, see aggregate precision.

#### `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 functionsList functions take list arguments, and are polymorphic.#### `list_agg(x: any) -> L` Aggregate values (including nulls) as a list [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/list_agg/index.md)#### `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 functionsMap functions take map arguments, and are polymorphic.#### `map_length(m: mapany) -> int` Return the number of elements in m.#### `map_build(kvs: list record(text, T)) -> map[text=>T]` Builds a map from a list of records whose fields are two elements, the first of which is text. In the face of duplicate keys, map_build retains value from the record in the latest positition. This function is purpose-built to process Kafka headers.#### `map_agg(keys: text, values: T) -> map[text=>T]` Aggregate keys and values (including nulls) as a map [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/map_agg/index.md)### Numbers functionsNumber functions take number-like arguments, e.g. int, float, numeric, unless otherwise specified.#### `abs(x: N) -> N` The absolute value of x.#### `cbrt(x: double precision) -> double precision` The cube root of x.#### `ceil(x: N) -> double precision` The smallest integer >= x.#### `ceiling(x: N) -> double precision` Alias of ceil.#### `exp(x: N) -> double precision` Exponential of x (e raised to the given power)#### `floor(x: N) -> double precision` 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) -> double precision` 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) -> double precision` x truncated toward zero to a whole number.### Trigonometric functionsTrigonometric functions take and return double precision values.#### `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 functions#### `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_count(b: bytea) -> bigint` Returns the number of bits set in the bit string (aka popcount).#### `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. The maximum length of the result string is 100 MiB.#### `concat_ws(sep: str, f: any, r: any...) -> text` Concatenates the text representation of non-NULL arguments from f and r separated by sep. The maximum length of the result string is 100 MiB.#### `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. The maximum size of the result is 100 MiB. [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/encode/index.md)#### `encode(b: bytea, format: text) -> text` Encode b using the specified textual representation [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/encode/index.md)#### `get_bit(b: bytea, n: int) -> int` Return the nth bit from b, where the left-most bit in b is at the 0th position.#### `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` Returns true if the arrays are identical, otherwise returns false. The implementation mitigates timing attacks by making a best-effort attempt to execute in constant time if the arrays have the same length, regardless of their contents.#### `constant_time_eq(a: text, b: text) -> bool` Returns true if the strings are identical, otherwise returns false. The implementation mitigates timing attacks by making a best-effort attempt to execute in constant time if the strings have the same length, regardless of their contents.#### `initcap(a: text) -> text` Returns a with the first character of every word in upper case and all other characters in lower case. Words are separated by non-alphanumeric characters.#### `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)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/length/index.md)#### `length(b: bytea) -> int` Number of bytes in s. [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/length/index.md)#### `length(s: bytea, encoding_name: str) -> int` Number of code points in s after encoding [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/length/index.md)#### `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. The maximum length of the result string is 100 MiB.#### `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. The maximum length of the result string is 100 MiB.#### `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.#### `normalize(s: str) -> str` Normalize s to Unicode Normalization Form NFC (default). [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/normalize/index.md)#### `normalize(s: str, form: keyword) -> str` Normalize s to the specified Unicode normalization form (NFC, NFD, NFKC, or NFKD). [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/normalize/index.md)#### `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[]` Given a qualified identifier like a."b".c, splits into an array of the constituent identifiers with quoting removed and escape sequences decoded. Extra characters after the last identifier are ignored unless the strict_mode parameter is true (defaults to false).#### `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` > **Warning:** This function has the potential to produce very large strings and > may cause queries to run out of memory or crash. Use with caution.

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_matches(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. If flags is set to the string g all matches are returned, otherwise only the first match is returned. Without the g flag, the behavior is the same as regexp_match.#### `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. The maximum length of the result string is 100 MiB.#### `replace(s: str, f: str, r: str) -> str` s with all instances of f replaced with r. The maximum length of the result string is 100 MiB.#### `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.#### `starts_with(s: str, prefix: str) -> bool` Report whether s starts with prefix.#### `substring(s: str, start_pos: int) -> str` Substring of s starting at start_pos [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/substring/index.md)#### `substring(s: str, start_pos: int, l: int) -> str` Substring starting at start_pos of length l [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/substring/index.md)#### `substring('s' [FROM 'start_pos']? [FOR 'l']?) -> str` Substring starting at start_pos of length l [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/substring/index.md)#### `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)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/pushdown/index.md)#### `upper(s: str) -> str` Convert s to uppercase.#### `reverse(s: str) -> str` Reverse the characters in s.#### `string_to_array(s: str, delimiter: str [, null_string: str]) -> str[]`

Splits the string at occurrences of delimiter and returns a text array of the split segments.

If delimiter is NULL, each character in the string will become a separate element in the array.

If delimiter is an empty string, then the string is treated as a single field.

If null_string is supplied and is not NULL, fields matching that string are replaced by NULL.

If s is NULL, the result is NULL. If s is the empty string, the result is an empty array (regardless of delimiter or null_string).

For example: string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz}

### Scalar functionsScalar functions take a list of scalar expressions#### `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. Avoid using in equi-join conditions as its use in the equi-join condition can lead to a significant increase in memory usage. See idiomatic Materialize SQL for the alternative.#### `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 functionsSubquery functions take a query, e.g. SELECT#### `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)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/csv_extract/index.md)#### `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.

> **Note:** When evaluating a `WHERE fieldX NOT IN ()` predicate involving > possible `NULL` values for `fieldX` or ``, Materialize performs a > cross join between the outer relation and the subquery to preserve SQL > `NULL` semantics, which can significantly increase memory usage. If > possible, rewrite using [idiomatic Materialize > SQL](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/not-in/index.md).#### `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 functionsTime functions take or produce a time-like type, e.g. date, timestamp, timestamp with time zone.#### `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](#unmaterializable-functions).#### `date_bin(stride: interval, source: timestamp, origin: timestamp) -> timestamp` Align source with origin along stride [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-bin/index.md)#### `date_trunc(time_component: str, val: timestamp) -> timestamp` Largest time_component <= val [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-trunc/index.md)#### `date_trunc(time_component: str, val: interval) -> interval` Largest time_component <= val [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-trunc/index.md)#### `EXTRACT(extract_expr) -> numeric` Specified time component from value [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/extract/index.md)#### `date_part(time_component: str, val: timestamp) -> float` Specified time component from value [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-part/index.md)#### `mz_now() -> mz_timestamp` The logical time at which a query executes. Used for temporal filters and query timestamp introspection [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/now_and_mz_now/index.md) **Note:** This function is [unmaterializable](#unmaterializable-functions), but can be used in limited contexts in materialized views as a [temporal filter](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/patterns/temporal-filters/index.md).#### `now() -> timestamptz` The timestamp with time zone representing when the query was executed [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/now_and_mz_now/index.md) **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `timestamp AT TIME ZONE zone -> timestamptz` Converts timestamp to the specified time zone, expressed as an offset from UTC [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/timezone-and-at-time-zone/index.md) **Known limitation:** You must explicitly cast the type for the time zone.#### `timestamptz AT TIME ZONE zone -> timestamp` Converts timestamp with time zone from UTC to the specified time zone, expressed as the local time [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/timezone-and-at-time-zone/index.md) **Known limitation:** You must explicitly cast the type for the time zone.#### `timezone(zone, timestamp) -> timestamptz` Converts timestamp to specified time zone, expressed as an offset from UTC [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/timezone-and-at-time-zone/index.md) **Known limitation:** You must explicitly cast the type for the time zone.#### `timezone(zone, timestamptz) -> timestamp` Converts timestamp with time zone from UTC to specified time zone, expressed as the local time [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/timezone-and-at-time-zone/index.md) **Known limitation:** You must explicitly cast the type for the time zone.#### `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)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/to_char/index.md)#### `justify_days(val: interval) -> interval` Adjust interval so 30-day time periods are represented as months [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/justify-days/index.md)#### `justify_hours(val: interval) -> interval` Adjust interval so 24-hour time periods are represented as days [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/justify-hours/index.md)#### `justify_interval(val: interval) -> interval` Adjust interval using justify_days and justify_hours, with additional sign adjustments [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/justify-interval/index.md)### UUID functions#### `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 functions#### `jsonb_agg(expression) -> jsonb` Aggregate values (including nulls) as a jsonb array [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/jsonb_agg/index.md)#### `jsonb_array_elements(j: jsonb) -> Col` j’s elements if j is an array [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_array_elements)#### `jsonb_array_elements_text(j: jsonb) -> Col` j’s elements if j is an array [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_array_elements_text)#### `jsonb_array_length(j: jsonb) -> int` Number of elements in j’s outermost array [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_array_length)#### `jsonb_build_array(x: ...) -> jsonb` Output each element of x as a jsonb array. Elements can be of heterogenous types [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_build_array)#### `jsonb_build_object(x: ...) -> jsonb` The elements of x as a jsonb object. The argument list alternates between keys and values [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_build_object)#### `jsonb_each(j: jsonb) -> Col<(key: string, value: jsonb)>` j’s outermost elements if j is an object [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_each)#### `jsonb_each_text(j: jsonb) -> Col<(key: string, value: string)>` j’s outermost elements if j is an object [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_each_text)#### `jsonb_object_agg(keys, values) -> jsonb` Aggregate keys and values (including nulls) as a jsonb object [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/jsonb_object_agg/index.md)#### `jsonb_object_keys(j: jsonb) -> Col` j’s outermost keys if j is an object [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_object_keys)#### `jsonb_pretty(j: jsonb) -> string` Pretty printed (i.e. indented) j [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_pretty)#### `jsonb_typeof(j: jsonb) -> string` Type of j’s outermost value. One of object, array, string, number, boolean, and null [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_typeof)#### `jsonb_strip_nulls(j: jsonb) -> jsonb` j with all object fields with a value of null removed. Other null values remain [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb_strip_nulls)#### `to_jsonb(v: T) -> jsonb` v as jsonb [(docs)](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#to_jsonb)### Table functionsTable functions evaluate to a collection of rows rather than a single row. You can use the WITH ORDINALITY and ROWS FROM clauses together with table functions. For more details, see Table functions.#### `generate_series(start: int, stop: int) -> Col` Generate all integer values between start and stop, inclusive.#### `generate_series(start: int, stop: int, step: int) -> Col` Generate all integer values between start and stop, inclusive, incrementing by step each time.#### `generate_series(start: timestamp, stop: timestamp, step: interval) -> Col` Generate all timestamp values between start and stop, inclusive, incrementing by step each time.#### `generate_subscripts(a: anyarray, dim: int) -> Col` Generates a series comprising the valid subscripts of the dim‘th dimension of the given array a.#### `regexp_extract(regex: str, haystack: str) -> Col` Values of the capture groups of regex as matched in haystack. Outputs each capture group in a separate column. At least one capture group is needed. (The capture groups are the parts of the regular expression between parentheses.)#### `regexp_split_to_table(text: str, pattern: str [, flags: str]]) -> Col` 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.#### `unnest(m: anymap)` Expands the map m in a set of rows with the columns key and value.### Array functions#### `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.#### `array_length(a: arrayany, dim: bigint) -> int` Returns the length of the specified dimension of the array.#### `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` > **Warning:** This function has the potential to produce very large strings and > may cause queries to run out of memory or crash. Use with caution.

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.### Hash functions#### `crc32(data: bytea) -> uint32` Computes the 32-bit cyclic redundancy check of the given bytea data using the IEEE 802.3 polynomial.#### `crc32(data: text) -> uint32` Computes the 32-bit cyclic redundancy check of the given text data using the IEEE 802.3 polynomial.#### `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.#### `kafka_murmur2(data: bytea) -> integer` Computes the Murmur2 hash of the given bytea data using the seed used by Kafka’s default partitioner and with the high bit cleared.#### `kafka_murmur2(data: text) -> integer` Computes the Murmur2 hash of the given text data using the seed used by Kafka’s default partitioner and with the high bit cleared.#### `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.#### `seahash(data: bytea) -> u64` Computes the SeaHash hash of the given bytea data.#### `seahash(data: text) -> u64` Computes the SeaHash hash of the given text data.#### `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 functions> **Tip:** For some window function query patterns, rewriting your query to not use > window functions can yield better performance. See [Idiomatic Materialize SQL](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/index.md) for details.

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 supports only the following frame modes:

  • the ROWS frame mode.

  • the default frame, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

> **Note:** For indexed views and materialized views that contain [window > functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/index.md#window-functions) (including aggregate functions used > with an `OVER` clause), when an input record in a partition is > added/removed/changed, Materialize **recomputes the results from scratch** for > that partition (instead of using incremental computation). > The `PARTITION BY` clause of your window function determines your partitions. If > `PARTITION BY` is omitted, all records belong to a single partition (i.e., any > record change results in a recomputation from scratch over the whole input). > To avoid performance issues that may arise as the number of records grows, > consider rewriting your indexed views and materialized views to use idiomatic > Materialize SQL instead of window functions. If your view definitions cannot be > rewritten without the window functions and the performance of window functions > is insufficient for your use case, please [contact our team](https://materialize.com/support/). > See [Idiomatic Materialize SQL](https://preview.materialize.com/materialize/40269/markdown-docs/transform-data/idiomatic-materialize-sql/index.md) > for examples of rewriting window functions.

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

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

See also Idiomatic Materialize SQL: First value.

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

See also Idiomatic Materialize SQL: Lag over.

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

See also Idiomatic Materialize SQL: Last value.

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

See also Idiomatic Materialize SQL: Lead over.

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

See also Idiomatic Materialize SQL: Top-K.

### System information functionsFunctions that return information about the system.#### `mz_environment_id() -> text` Returns a string containing a uuid uniquely identifying the Materialize environment.#### `mz_uptime() -> interval` Returns the length of time that the materialized process has been running. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `mz_version() -> text` Returns the server’s version information as a human-readable string. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `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](#unmaterializable-functions).#### `mz_session_role_memberships() -> text[]` Returns the names of the roles the current role is a member of, directly or through other roles, including the current role itself. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `current_database() -> text` Returns the name of the current database. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `current_catalog() -> text` Alias for current_database. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `current_user() -> text` Returns the name of the user who executed the containing query. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `current_role() -> text` Alias for current_user. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `user() -> text` Alias for current_user. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `session_user() -> text` Returns the name of the user who initiated the database connection. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `mz_row_size(expr: Record) -> int` Returns the number of bytes used to store a row.### PostgreSQL compatibility functionsFunctions whose primary purpose is to facilitate compatibility with PostgreSQL tools. These functions may have suboptimal performance characteristics.#### `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](#unmaterializable-functions).#### `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](#unmaterializable-functions).#### `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](#unmaterializable-functions).#### `obj_description(oid: oid, catalog: text) -> text` Returns the comment for a database object specified by its oid and the name of the containing system catalog.#### `col_description(oid: oid, column: int) -> text` Returns the comment for a table column, which is specified by the oid of its table and its column number.#### `pg_backend_pid() -> int` Returns the internal connection ID. **Note:** This function is [unmaterializable](#unmaterializable-functions).#### `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](#side-effecting-functions).#### `pg_column_size(expr: any) -> int` Returns the number of bytes used to store any individual data value.#### `pg_size_pretty(expr: numeric) -> text` Converts a size in bytes into a human-readable format.#### `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](#unmaterializable-functions).#### `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](#unmaterializable-functions).### Access privilege inquiry functionsFunctions that allow querying object access privileges. None of the following functions consider whether the provided role is a superuser or not.#### `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. **Note:** This function is [unmaterializable](#unmaterializable-functions). ## Operators ### Generic operators Operator | Computes ---------|--------- `val::type` | Cast of `val` as `type` ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/cast/index.md)) ### 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](https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE) `a ILIKE match_expr [ ESCAPE escape_char ]` | `a` matches `match_expr`, using case-insensitive [SQL LIKE matching](https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE) ### Numbers operators Operator | Computes ---------|--------- `+` | Addition `-` | Subtraction `*` | Multiplication `/` | Division `%` | Modulo `&` | Bitwise AND | | Bitwise OR `#` | Bitwise XOR `~` | Bitwise NOT `<<`| Bitwise left shift `>>`| Bitwise right shift ### String operators Operator | Computes ---------|--------- || | Concatenation `~~` | Matches LIKE pattern case sensitively, see [SQL LIKE matching](https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE) `~~*` | Matches LIKE pattern case insensitively (ILIKE), see [SQL LIKE matching](https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE) `!~~` | Matches NOT LIKE pattern (case sensitive), see [SQL LIKE matching](https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE) `!~~*` | Matches NOT ILIKE pattern (case insensitive), see [SQL LIKE matching](https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE) `~` | 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](https://docs.rs/regex/*/#syntax). The maximum length of a regular expression is 1 MiB in its raw form, and 10 MiB after compiling it. A regular expression may also contain at most 2000 character classes, counting each Unicode, Perl, or POSIX class such as `\p{L}`, `\d`, or `[[:alpha:]]`, and each range such as `a-z`. > **Warning:** Materialize regular expressions are similar to, but not identical to, PostgreSQL > regular expressions. ### Time-like operators Operation | Computes ----------|------------ [`date`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md) `+` [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) [`date`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md) `-` [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) [`date`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md) `+` [`time`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md) | [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) [`date`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md) `-` [`date`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md) | [`integer`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/integer/index.md) [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) `+` [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) `-` [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) `-` [`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) | [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) [`time`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md) `+` [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | `time` [`time`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md) `-` [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | `time` [`time`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md) `-` [`time`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md) | [`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) ### JSON operators Operator | RHS Type | Description ---------|----------|------------- `->` | `text`, `int`| Access field by name or index position, and return `jsonb` ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#field-access-as-jsonb--)) `->>` | `text`, `int`| Access field by name or index position, and return `text` ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#field-access-as-text--)) `#>` | `text[]` | Access field by path, and return `jsonb` ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#path-access-as-jsonb-)) `#>>` | `text[]` | Access field by path, and return `text` ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#path-access-as-text-)) || | `jsonb` | Concatenate LHS and RHS ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#jsonb-concat-)) `-` | `text` | Delete all values with key of RHS ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#remove-key--)) `@>` | `jsonb` | Does element contain RHS? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#lhs-contains-rhs-)) <@ | `jsonb` | Does RHS contain element? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#rhs-contains-lhs-)) `?` | `text` | Is RHS a top-level key? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md#search-top-level-keys-)) ### Map operators Operator | RHS Type | Description ---------|----------|------------- `->` | `string` | Access field by name, and return target field ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md#retrieve-value-with-key--)) `@>` | `map` | Does element contain RHS? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md#lhs-contains-rhs-)) <@ | `map` | Does RHS contain element? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md#rhs-contains-lhs-)) `?` | `string` | Is RHS a top-level key? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md#search-top-level-keys-)) `?&` | `string[]` | Does LHS contain all RHS top-level keys? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md#search-for-all-top-level-keys-)) ?| | `string[]` | Does LHS contain any RHS top-level keys? ([docs](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md#search-for-any-top-level-keys-)) ### List operators List operators are [polymorphic](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/list/index.md#polymorphism). Operator | Description ---------|------------- listany || listany | Concatenate the two lists. listany || listelementany | Append the element to the list. listelementany || listany | Prepend the element to the list. listany @> listany | Check if the first list contains all elements of the second list. listany <@ listany | Check if all elements of the first list are contained in the second list. listany < listany, and likewise <=, >, >=, =, <> | Compare the two lists element by element. The first pair of elements that differ decides. A _NULL_ element is greater than any non-_NULL_ element, whatever `NULLS FIRST` or `NULLS LAST` says, since those only place values that are _NULL_ as a whole. A list is greater than any shorter list that it starts with. `ORDER BY`, `min`, and `max` order lists the same way. ## Pages in this section - [Aggregate function filters](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/filters/index.md): Use FILTER to specify which rows are sent to an aggregate function - [array_agg function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/array_agg/index.md): Aggregate values (including nulls) as an array. - [CAST and TRY_CAST](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/cast/index.md): Returns the value converted to the specified type - [COALESCE function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/coalesce/index.md): Returns the first non-NULL element provided. - [csv_extract function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/csv_extract/index.md): Returns separated values from a column containing a CSV file formatted as a string. - [date_bin function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-bin/index.md): Bins a timestamp into a specified interval - [date_part function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-part/index.md): Returns a specified time component from a time-based value - [date_trunc function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/date-trunc/index.md): Truncates a timestamp at the specified time component - [datediff function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/datediff/index.md): Returns the difference between two date, time or timestamp expressions based on the specified date or time part. - [encode and decode functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/encode/index.md): Converts binary data to and from textual representations - [EXTRACT function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/extract/index.md): Returns a specified time component from a time-based value - [jsonb_agg function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/jsonb_agg/index.md): Aggregates values (including nulls) as a jsonb array. - [jsonb_object_agg function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/jsonb_object_agg/index.md): Aggregate keys and values (including nulls) into a jsonb object - [justify_days function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/justify-days/index.md): Adjust interval so 30-day time periods are represented as months - [justify_hours function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/justify-hours/index.md): Adjust interval so 24-hour time periods are represented as days - [justify_interval function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/justify-interval/index.md): Adjust interval using justify_days and justify_hours, with additional sign adjustments - [LENGTH function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/length/index.md): Returns the number of characters in a string. - [list_agg function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/list_agg/index.md): Concatenates input values (including nulls) into a string. - [map_agg function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/map_agg/index.md): Aggregate keys and values (excluding null keys) into a map - [normalize function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/normalize/index.md): Returns a string normalized to the specified Unicode normalization form. - [now and mz_now functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/now_and_mz_now/index.md): Details the differences between the `now()` and `mz_now()` functions. - [Pushdown functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/pushdown/index.md): Functions for use with the filter pushdown feature - [string_agg function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/string_agg/index.md): Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it's not null). - [SUBSTRING function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/substring/index.md): Returns substring at specified positions - [Table functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/table-functions/index.md): Functions that return multiple rows - [TIMEZONE and AT TIME ZONE functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/timezone-and-at-time-zone/index.md): Converts timestamp to a different time zone. - [to_char function](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/to_char/index.md): Converts a timestamp into a string using the specified format.