Functions and Operators

On this page

Warning:

As of May 12, 2021, CockroachDB v19.2 is no longer supported. For more details, refer to the Release Support Policy.

CockroachDB supports the following SQL functions and operators for use in scalar expressions.

Tip:

In the built-in SQL shell, use \hf [function] to get inline help about a specific function.

Special syntax forms

The following syntax forms are recognized for compatibility with the SQL standard and PostgreSQL, but are equivalent to regular built-in functions:

Special form	Equivalent to
`AT TIME ZONE`	`timezone()`
`CURRENT_CATALOG`	`current_catalog()`
`COLLATION FOR`	`pg_collation_for()`
`CURRENT_DATE`	`current_date()`
`CURRENT_ROLE`	`current_user()`
`CURRENT_SCHEMA`	`current_schema()`
`CURRENT_TIMESTAMP`	`current_timestamp()`
`CURRENT_TIME`	`current_time()`
`CURRENT_USER`	`current_user()`
`EXTRACT(<part> FROM <value>)`	`extract("<part>", <value>)`
`EXTRACT_DURATION(<part> FROM <value>)`	`extract_duration("<part>", <value>)`
`OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>)`	`overlay(<text1>, <text2>, <int1>, <int2>)`
`OVERLAY(<text1> PLACING <text2> FROM <int>)`	`overlay(<text1>, <text2>, <int>)`
`POSITION(<text1> IN <text2>)`	`strpos(<text2>, <text1>)`
`SESSION_USER`	`current_user()`
`SUBSTRING(<text> FOR <int1> FROM <int2>)`	`substring(<text>, <int2>, <int1>)`
`SUBSTRING(<text> FOR <int>)`	`substring(<text>, 1, <int>)`
`SUBSTRING(<text> FROM <int1> FOR <int2>)`	`substring(<text>, <int1>, <int2>)`
`SUBSTRING(<text> FROM <int>)`	`substring(<text>, <int>)`
`TRIM(<text1> FROM <text2>)`	`btrim(<text2>, <text1>)`
`TRIM(<text1>, <text2>)`	`btrim(<text1>, <text2>)`
`TRIM(FROM <text>)`	`btrim(<text>)`
`TRIM(LEADING <text1> FROM <text2>)`	`ltrim(<text2>, <text1>)`
`TRIM(LEADING FROM <text>)`	`ltrim(<text>)`
`TRIM(TRAILING <text1> FROM <text2>)`	`rtrim(<text2>, <text1>)`
`TRIM(TRAILING FROM <text>)`	`rtrim(<text>)`
`USER`	`current_user()`

Conditional and function-like operators

The following table lists the operators that look like built-in functions but have special evaluation rules:

Operator	Description
`ANNOTATE_TYPE(...)`	Explicitly Typed Expression
`ARRAY(...)`	Conversion of Subquery Results to An Array
`ARRAY[...]`	Conversion of Scalar Expressions to An Array
`CAST(...)`	Type Cast
`COALESCE(...)`	First non-NULL expression with Short Circuit
`EXISTS(...)`	Existence Test on the Result of Subqueries
`IF(...)`	Conditional Evaluation
`IFNULL(...)`	Alias for `COALESCE` restricted to two operands
`NULLIF(...)`	Return `NULL` conditionally
`ROW(...)`	Tuple Constructor

Built-in functions

Array functions

Function → Returns	Description
`array_append(array: bool[], elem: bool) → bool[]`	Appends `elem` to `array`, returning the result.
`array_append(array: bytes[], elem: bytes) → bytes[]`	Appends `elem` to `array`, returning the result.
`array_append(array: date[], elem: date) → date[]`	Appends `elem` to `array`, returning the result.
`array_append(array: decimal[], elem: decimal) → decimal[]`	Appends `elem` to `array`, returning the result.
`array_append(array: float[], elem: float) → float[]`	Appends `elem` to `array`, returning the result.
`array_append(array: inet[], elem: inet) → inet[]`	Appends `elem` to `array`, returning the result.
`array_append(array: int[], elem: int) → int[]`	Appends `elem` to `array`, returning the result.
`array_append(array: interval[], elem: interval) → interval[]`	Appends `elem` to `array`, returning the result.
`array_append(array: string[], elem: string) → string[]`	Appends `elem` to `array`, returning the result.
`array_append(array: time[], elem: time) → time[]`	Appends `elem` to `array`, returning the result.
`array_append(array: timestamp[], elem: timestamp) → timestamp[]`	Appends `elem` to `array`, returning the result.
`array_append(array: timestamptz[], elem: timestamptz) → timestamptz[]`	Appends `elem` to `array`, returning the result.
`array_append(array: uuid[], elem: uuid) → uuid[]`	Appends `elem` to `array`, returning the result.
`array_append(array: oid[], elem: oid) → oid[]`	Appends `elem` to `array`, returning the result.
`array_append(array: varbit[], elem: varbit) → varbit[]`	Appends `elem` to `array`, returning the result.
`array_cat(left: bool[], right: bool[]) → bool[]`	Appends two arrays.
`array_cat(left: bytes[], right: bytes[]) → bytes[]`	Appends two arrays.
`array_cat(left: date[], right: date[]) → date[]`	Appends two arrays.
`array_cat(left: decimal[], right: decimal[]) → decimal[]`	Appends two arrays.
`array_cat(left: float[], right: float[]) → float[]`	Appends two arrays.
`array_cat(left: inet[], right: inet[]) → inet[]`	Appends two arrays.
`array_cat(left: int[], right: int[]) → int[]`	Appends two arrays.
`array_cat(left: interval[], right: interval[]) → interval[]`	Appends two arrays.
`array_cat(left: string[], right: string[]) → string[]`	Appends two arrays.
`array_cat(left: time[], right: time[]) → time[]`	Appends two arrays.
`array_cat(left: timestamp[], right: timestamp[]) → timestamp[]`	Appends two arrays.
`array_cat(left: timestamptz[], right: timestamptz[]) → timestamptz[]`	Appends two arrays.
`array_cat(left: uuid[], right: uuid[]) → uuid[]`	Appends two arrays.
`array_cat(left: oid[], right: oid[]) → oid[]`	Appends two arrays.
`array_cat(left: varbit[], right: varbit[]) → varbit[]`	Appends two arrays.
`array_length(input: anyelement[], array_dimension: int) → int`	Calculates the length of `input` on the provided `array_dimension`. However, because CockroachDB doesn’t yet support multi-dimensional arrays, the only supported `array_dimension` is 1.
`array_lower(input: anyelement[], array_dimension: int) → int`	Calculates the minimum value of `input` on the provided `array_dimension`. However, because CockroachDB doesn’t yet support multi-dimensional arrays, the only supported `array_dimension` is 1.
`array_position(array: bool[], elem: bool) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: bytes[], elem: bytes) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: date[], elem: date) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: decimal[], elem: decimal) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: float[], elem: float) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: inet[], elem: inet) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: int[], elem: int) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: interval[], elem: interval) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: string[], elem: string) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: time[], elem: time) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: timestamp[], elem: timestamp) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: timestamptz[], elem: timestamptz) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: uuid[], elem: uuid) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: oid[], elem: oid) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_position(array: varbit[], elem: varbit) → int`	Return the index of the first occurrence of `elem` in `array`.
`array_positions(array: bool[], elem: bool) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: bytes[], elem: bytes) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: date[], elem: date) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: decimal[], elem: decimal) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: float[], elem: float) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: inet[], elem: inet) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: int[], elem: int) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: interval[], elem: interval) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: string[], elem: string) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: time[], elem: time) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: timestamp[], elem: timestamp) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: timestamptz[], elem: timestamptz) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: uuid[], elem: uuid) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: oid[], elem: oid) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: varbit[], elem: varbit) → int[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_prepend(elem: bool, array: bool[]) → bool[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: bytes, array: bytes[]) → bytes[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: date, array: date[]) → date[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: decimal, array: decimal[]) → decimal[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: float, array: float[]) → float[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: inet, array: inet[]) → inet[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: int, array: int[]) → int[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: interval, array: interval[]) → interval[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: string, array: string[]) → string[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: time, array: time[]) → time[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: timestamp, array: timestamp[]) → timestamp[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: timestamptz, array: timestamptz[]) → timestamptz[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: uuid, array: uuid[]) → uuid[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: oid, array: oid[]) → oid[]`	Prepends `elem` to `array`, returning the result.
`array_prepend(elem: varbit, array: varbit[]) → varbit[]`	Prepends `elem` to `array`, returning the result.
`array_remove(array: bool[], elem: bool) → bool[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: bytes[], elem: bytes) → bytes[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: date[], elem: date) → date[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: decimal[], elem: decimal) → decimal[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: float[], elem: float) → float[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: inet[], elem: inet) → inet[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: int[], elem: int) → int[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: interval[], elem: interval) → interval[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: string[], elem: string) → string[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: time[], elem: time) → time[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: timestamp[], elem: timestamp) → timestamp[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: timestamptz[], elem: timestamptz) → timestamptz[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: uuid[], elem: uuid) → uuid[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: oid[], elem: oid) → oid[]`	Remove from `array` all elements equal to `elem`.
`array_remove(array: varbit[], elem: varbit) → varbit[]`	Remove from `array` all elements equal to `elem`.
`array_replace(array: bool[], toreplace: bool, replacewith: bool) → bool[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: bytes[], toreplace: bytes, replacewith: bytes) → bytes[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: date[], toreplace: date, replacewith: date) → date[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) → decimal[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: float[], toreplace: float, replacewith: float) → float[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: inet[], toreplace: inet, replacewith: inet) → inet[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: int[], toreplace: int, replacewith: int) → int[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: interval[], toreplace: interval, replacewith: interval) → interval[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: string[], toreplace: string, replacewith: string) → string[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: time[], toreplace: time, replacewith: time) → time[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp) → timestamp[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) → timestamptz[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) → uuid[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: oid[], toreplace: oid, replacewith: oid) → oid[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_replace(array: varbit[], toreplace: varbit, replacewith: varbit) → varbit[]`	Replace all occurrences of `toreplace` in `array` with `replacewith`.
`array_to_string(input: anyelement[], delim: string) → string`	Join an array into a string with a delimiter.
`array_to_string(input: anyelement[], delimiter: string, null: string) → string`	Join an array into a string with a delimiter, replacing NULLs with a null string.
`array_upper(input: anyelement[], array_dimension: int) → int`	Calculates the maximum value of `input` on the provided `array_dimension`. However, because CockroachDB doesn’t yet support multi-dimensional arrays, the only supported `array_dimension` is 1.
`string_to_array(str: string, delimiter: string) → string[]`	Split a string into components on a delimiter.
`string_to_array(str: string, delimiter: string, null: string) → string[]`	Split a string into components on a delimiter with a specified string to consider NULL.

BOOL functions

Function → Returns	Description
`ilike_escape(unescaped: string, pattern: string, escape: string) → bool`	Matches case insensetively `unescaped` with `pattern` using ‘escape’ as an escape token.
`inet_contained_by_or_equals(val: inet, container: inet) → bool`	Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored.
`inet_contains_or_equals(container: inet, val: inet) → bool`	Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored.
`inet_same_family(val: inet, val: inet) → bool`	Checks if two IP addresses are of the same IP family.
`like_escape(unescaped: string, pattern: string, escape: string) → bool`	Matches `unescaped` with `pattern` using ‘escape’ as an escape token.
`not_ilike_escape(unescaped: string, pattern: string, escape: string) → bool`	Checks whether `unescaped` not matches case insensetively with `pattern` using ‘escape’ as an escape token.
`not_like_escape(unescaped: string, pattern: string, escape: string) → bool`	Checks whether `unescaped` not matches with `pattern` using ‘escape’ as an escape token.
`not_similar_to_escape(unescaped: string, pattern: string, escape: string) → bool`	Checks whether `unescaped` not matches with `pattern` using ‘escape’ as an escape token.
`similar_to_escape(unescaped: string, pattern: string, escape: string) → bool`	Matches `unescaped` with `pattern` using ‘escape’ as an escape token.

Comparison functions

Function → Returns	Description
`greatest(anyelement...) → anyelement`	Returns the element with the greatest value.
`least(anyelement...) → anyelement`	Returns the element with the lowest value.

Date and time functions

Function → Returns	Description
`age(end: timestamptz, begin: timestamptz) → interval`	Calculates the interval between `begin` and `end`.
`age(val: timestamptz) → interval`	Calculates the interval between `val` and the current time.
`clock_timestamp() → timestamp`	Returns the current system time on one of the cluster nodes.
`clock_timestamp() → timestamptz`	Returns the current system time on one of the cluster nodes.
`current_date() → date`	Returns the date of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`current_timestamp() → date`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`current_timestamp() → timestamp`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`current_timestamp() → timestamptz`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`date_trunc(element: string, input: date) → timestamptz`	Truncates `input` to precision `element`. Sets all fields that are less significant than `element` to zero (or one, for day and month) Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond.
`date_trunc(element: string, input: time) → interval`	Truncates `input` to precision `element`. Sets all fields that are less significant than `element` to zero. Compatible elements: hour, minute, second, millisecond, microsecond.
`date_trunc(element: string, input: timestamp) → timestamp`	Truncates `input` to precision `element`. Sets all fields that are less significant than `element` to zero (or one, for day and month) Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond.
`date_trunc(element: string, input: timestamptz) → timestamptz`	Truncates `input` to precision `element`. Sets all fields that are less significant than `element` to zero (or one, for day and month) Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond.
`experimental_follower_read_timestamp() → timestamptz`	Returns a timestamp which is very likely to be safe to perform against a follower replica. This function is intended to be used with an AS OF SYSTEM TIME clause to perform historical reads against a time which is recent but sufficiently old for reads to be performed against the closest replica as opposed to the currently leaseholder for a given range. Note that this function requires an enterprise license on a CCL distribution to return without an error.
`experimental_strftime(input: date, extract_format: string) → string`	From `input`, extracts and formats the time as identified in `extract_format` using standard `strftime` notation (though not all formatting is supported).
`experimental_strftime(input: timestamp, extract_format: string) → string`	From `input`, extracts and formats the time as identified in `extract_format` using standard `strftime` notation (though not all formatting is supported).
`experimental_strftime(input: timestamptz, extract_format: string) → string`	From `input`, extracts and formats the time as identified in `extract_format` using standard `strftime` notation (though not all formatting is supported).
`experimental_strptime(input: string, format: string) → timestamptz`	Returns `input` as a timestamptz using `format` (which uses standard `strptime` formatting).
`extract(element: string, input: date) → int`	Extracts `element` from `input`. Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch
`extract(element: string, input: time) → int`	Extracts `element` from `input`. Compatible elements: hour, minute, second, millisecond, microsecond, epoch
`extract(element: string, input: timestamp) → int`	Extracts `element` from `input`. Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch
`extract(element: string, input: timestamptz) → int`	Extracts `element` from `input`. Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch
`extract_duration(element: string, input: interval) → int`	Extracts `element` from `input`. Compatible elements: hour, minute, second, millisecond, microsecond.
`now() → date`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`now() → timestamp`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`now() → timestamptz`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`statement_timestamp() → timestamp`	Returns the start time of the current statement.
`statement_timestamp() → timestamptz`	Returns the start time of the current statement.
`timezone(timestamp: timestamp, timezone: string) → timestamptz`	Treat given time stamp without time zone as located in the specified time zone
`timezone(timestamptz: timestamptz, timezone: string) → timestamp`	Convert given time stamp with time zone to the new time zone, with no time zone designation
`transaction_timestamp() → date`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`transaction_timestamp() → timestamp`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.
`transaction_timestamp() → timestamptz`	Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions.

ID generation functions

Function → Returns	Description
`experimental_uuid_v4() → bytes`	Returns a UUID.
`gen_random_uuid() → uuid`	Generates a random UUID and returns it as a value of UUID type.
`unique_rowid() → int`	Returns a unique ID used by CockroachDB to generate unique row IDs if a Primary Key isn’t defined for the table. The value is a combination of the insert timestamp and the ID of the node executing the statement, which guarantees this combination is globally unique. However, there can be gaps and the order is not completely guaranteed.
`uuid_v4() → bytes`	Returns a UUID.

INET functions

Function → Returns	Description
`abbrev(val: inet) → string`	Converts the combined IP address and prefix length to an abbreviated display format as text.For INET types, this will omit the prefix length if it’s not the default (32 or IPv4, 128 for IPv6) For example, `abbrev('192.168.1.2/24')` returns `'192.168.1.2/24'`
`broadcast(val: inet) → inet`	Gets the broadcast address for the network address represented by the value. For example, `broadcast('192.168.1.2/24')` returns `'192.168.1.255/24'`
`family(val: inet) → int`	Extracts the IP family of the value; 4 for IPv4, 6 for IPv6. For example, `family('::1')` returns `6`
`host(val: inet) → string`	Extracts the address part of the combined address/prefixlen value as text. For example, `host('192.168.1.2/16')` returns `'192.168.1.2'`
`hostmask(val: inet) → inet`	Creates an IP host mask corresponding to the prefix length in the value. For example, `hostmask('192.168.1.2/16')` returns `'0.0.255.255'`
`masklen(val: inet) → int`	Retrieves the prefix length stored in the value. For example, `masklen('192.168.1.2/16')` returns `16`
`netmask(val: inet) → inet`	Creates an IP network mask corresponding to the prefix length in the value. For example, `netmask('192.168.1.2/16')` returns `'255.255.0.0'`
`set_masklen(val: inet, prefixlen: int) → inet`	Sets the prefix length of `val` to `prefixlen`. For example, `set_masklen('192.168.1.2', 16)` returns `'192.168.1.2/16'`.
`text(val: inet) → string`	Converts the IP address and prefix length to text.

JSONB functions

Function → Returns	Description
`array_to_json(array: anyelement[]) → jsonb`	Returns the array as JSON or JSONB.
`array_to_json(array: anyelement[], pretty_bool: bool) → jsonb`	Returns the array as JSON or JSONB.
`json_array_length(json: jsonb) → int`	Returns the number of elements in the outermost JSON or JSONB array.
`json_build_array(anyelement...) → jsonb`	Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list.
`json_build_object(anyelement...) → jsonb`	Builds a JSON object out of a variadic argument list.
`json_extract_path(jsonb, string...) → jsonb`	Returns the JSON value pointed to by the variadic arguments.
`json_object(keys: string[], values: string[]) → jsonb`	This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.
`json_object(texts: string[]) → jsonb`	Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs.
`json_remove_path(val: jsonb, path: string[]) → jsonb`	Remove the specified path from the JSON object.
`json_set(val: jsonb, path: string[], to: jsonb) → jsonb`	Returns the JSON value pointed to by the variadic arguments.
`json_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb`	Returns the JSON value pointed to by the variadic arguments. If `create_missing` is false, new keys will not be inserted to objects and values will not be prepended or appended to arrays.
`json_strip_nulls(from_json: jsonb) → jsonb`	Returns from_json with all object fields that have null values omitted. Other null values are untouched.
`json_typeof(val: jsonb) → string`	Returns the type of the outermost JSON value as a text string.
`jsonb_array_length(json: jsonb) → int`	Returns the number of elements in the outermost JSON or JSONB array.
`jsonb_build_array(anyelement...) → jsonb`	Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list.
`jsonb_build_object(anyelement...) → jsonb`	Builds a JSON object out of a variadic argument list.
`jsonb_extract_path(jsonb, string...) → jsonb`	Returns the JSON value pointed to by the variadic arguments.
`jsonb_insert(target: jsonb, path: string[], new_val: jsonb) → jsonb`	Returns the JSON value pointed to by the variadic arguments. `new_val` will be inserted before path target.
`jsonb_insert(target: jsonb, path: string[], new_val: jsonb, insert_after: bool) → jsonb`	Returns the JSON value pointed to by the variadic arguments. If `insert_after` is true (default is false), `new_val` will be inserted after path target.
`jsonb_object(keys: string[], values: string[]) → jsonb`	This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.
`jsonb_object(texts: string[]) → jsonb`	Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs.
`jsonb_pretty(val: jsonb) → string`	Returns the given JSON value as a STRING indented and with newlines.
`jsonb_set(val: jsonb, path: string[], to: jsonb) → jsonb`	Returns the JSON value pointed to by the variadic arguments.
`jsonb_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb`	Returns the JSON value pointed to by the variadic arguments. If `create_missing` is false, new keys will not be inserted to objects and values will not be prepended or appended to arrays.
`jsonb_strip_nulls(from_json: jsonb) → jsonb`	Returns from_json with all object fields that have null values omitted. Other null values are untouched.
`jsonb_typeof(val: jsonb) → string`	Returns the type of the outermost JSON value as a text string.
`to_json(val: anyelement) → jsonb`	Returns the value as JSON or JSONB.
`to_jsonb(val: anyelement) → jsonb`	Returns the value as JSON or JSONB.

Math and numeric functions

Function → Returns	Description
`abs(val: decimal) → decimal`	Calculates the absolute value of `val`.
`abs(val: float) → float`	Calculates the absolute value of `val`.
`abs(val: int) → int`	Calculates the absolute value of `val`.
`acos(val: float) → float`	Calculates the inverse cosine of `val`.
`asin(val: float) → float`	Calculates the inverse sine of `val`.
`atan(val: float) → float`	Calculates the inverse tangent of `val`.
`atan2(x: float, y: float) → float`	Calculates the inverse tangent of `x`/`y`.
`cbrt(val: decimal) → decimal`	Calculates the cube root (∛) of `val`.
`cbrt(val: float) → float`	Calculates the cube root (∛) of `val`.
`ceil(val: decimal) → decimal`	Calculates the smallest integer greater than `val`.
`ceil(val: float) → float`	Calculates the smallest integer greater than `val`.
`ceiling(val: decimal) → decimal`	Calculates the smallest integer greater than `val`.
`ceiling(val: float) → float`	Calculates the smallest integer greater than `val`.
`cos(val: float) → float`	Calculates the cosine of `val`.
`cot(val: float) → float`	Calculates the cotangent of `val`.
`crc32c(bytes...) → int`	Calculates the CRC-32 hash using the Castagnoli polynomial.
`crc32c(string...) → int`	Calculates the CRC-32 hash using the Castagnoli polynomial.
`crc32ieee(bytes...) → int`	Calculates the CRC-32 hash using the IEEE polynomial.
`crc32ieee(string...) → int`	Calculates the CRC-32 hash using the IEEE polynomial.
`degrees(val: float) → float`	Converts `val` as a radian value to a degree value.
`div(x: decimal, y: decimal) → decimal`	Calculates the integer quotient of `x`/`y`.
`div(x: float, y: float) → float`	Calculates the integer quotient of `x`/`y`.
`div(x: int, y: int) → int`	Calculates the integer quotient of `x`/`y`.
`exp(val: decimal) → decimal`	Calculates e ^ `val`.
`exp(val: float) → float`	Calculates e ^ `val`.
`floor(val: decimal) → decimal`	Calculates the largest integer not greater than `val`.
`floor(val: float) → float`	Calculates the largest integer not greater than `val`.
`fnv32(bytes...) → int`	Calculates the 32-bit FNV-1 hash value of a set of values.
`fnv32(string...) → int`	Calculates the 32-bit FNV-1 hash value of a set of values.
`fnv32a(bytes...) → int`	Calculates the 32-bit FNV-1a hash value of a set of values.
`fnv32a(string...) → int`	Calculates the 32-bit FNV-1a hash value of a set of values.
`fnv64(bytes...) → int`	Calculates the 64-bit FNV-1 hash value of a set of values.
`fnv64(string...) → int`	Calculates the 64-bit FNV-1 hash value of a set of values.
`fnv64a(bytes...) → int`	Calculates the 64-bit FNV-1a hash value of a set of values.
`fnv64a(string...) → int`	Calculates the 64-bit FNV-1a hash value of a set of values.
`isnan(val: decimal) → bool`	Returns true if `val` is NaN, false otherwise.
`isnan(val: float) → bool`	Returns true if `val` is NaN, false otherwise.
`ln(val: decimal) → decimal`	Calculates the natural log of `val`.
`ln(val: float) → float`	Calculates the natural log of `val`.
`log(val: decimal) → decimal`	Calculates the base 10 log of `val`.
`log(val: float) → float`	Calculates the base 10 log of `val`.
`mod(x: decimal, y: decimal) → decimal`	Calculates `x`%`y`.
`mod(x: float, y: float) → float`	Calculates `x`%`y`.
`mod(x: int, y: int) → int`	Calculates `x`%`y`.
`pi() → float`	Returns the value for pi (3.141592653589793).
`pow(x: decimal, y: decimal) → decimal`	Calculates `x`^`y`.
`pow(x: float, y: float) → float`	Calculates `x`^`y`.
`pow(x: int, y: int) → int`	Calculates `x`^`y`.
`power(x: decimal, y: decimal) → decimal`	Calculates `x`^`y`.
`power(x: float, y: float) → float`	Calculates `x`^`y`.
`power(x: int, y: int) → int`	Calculates `x`^`y`.
`radians(val: float) → float`	Converts `val` as a degree value to a radians value.
`random() → float`	Returns a random float between 0 and 1.
`round(input: decimal, decimal_accuracy: int) → decimal`	Keeps `decimal_accuracy` number of figures to the right of the zero position in `input` using half away from zero rounding. If `decimal_accuracy` is not in the range -2^31…(2^31-1), the results are undefined.
`round(input: float, decimal_accuracy: int) → float`	Keeps `decimal_accuracy` number of figures to the right of the zero position in `input` using half to even (banker’s) rounding.
`round(val: decimal) → decimal`	Rounds `val` to the nearest integer, half away from zero: round(+/-2.4) = +/-2, round(+/-2.5) = +/-3.
`round(val: float) → float`	Rounds `val` to the nearest integer using half to even (banker’s) rounding.
`sign(val: decimal) → decimal`	Determines the sign of `val`: 1 for positive; 0 for 0 values; -1 for negative.
`sign(val: float) → float`	Determines the sign of `val`: 1 for positive; 0 for 0 values; -1 for negative.
`sign(val: int) → int`	Determines the sign of `val`: 1 for positive; 0 for 0 values; -1 for negative.
`sin(val: float) → float`	Calculates the sine of `val`.
`sqrt(val: decimal) → decimal`	Calculates the square root of `val`.
`sqrt(val: float) → float`	Calculates the square root of `val`.
`tan(val: float) → float`	Calculates the tangent of `val`.
`trunc(val: decimal) → decimal`	Truncates the decimal values of `val`.
`trunc(val: float) → float`	Truncates the decimal values of `val`.
`width_bucket(operand: decimal, b1: decimal, b2: decimal, count: int) → int`	return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2.
`width_bucket(operand: int, b1: int, b2: int, count: int) → int`	return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2.
`width_bucket(operand: anyelement, thresholds: anyelement[]) → int`	return the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained

Sequence functions

Function → Returns	Description
`currval(sequence_name: string) → int`	Returns the latest value obtained with nextval for this sequence in this session.
`lastval() → int`	Return value most recently obtained with nextval in this session.
`nextval(sequence_name: string) → int`	Advances the given sequence and returns its new value.
`setval(sequence_name: string, value: int) → int`	Set the given sequence’s current value. The next call to nextval will return `value + Increment`
`setval(sequence_name: string, value: int, is_called: bool) → int`	Set the given sequence’s current value. If is_called is false, the next call to nextval will return `value`; otherwise `value + Increment`.

Set-returning functions

Function → Returns	Description
`aclexplode(aclitems: string[]) → tuple{oid AS grantor, oid AS grantee, string AS privilege_type, bool AS is_grantable}`	Produces a virtual table containing aclitem stuff (returns no rows as this feature is unsupported in CockroachDB)
`crdb_internal.unary_table() → tuple`	Produces a virtual table containing a single row with no values. This function is used only by CockroachDB’s developers for testing purposes.
`generate_series(start: int, end: int) → int`	Produces a virtual table containing the integer values from `start` to `end`, inclusive.
`generate_series(start: int, end: int, step: int) → int`	Produces a virtual table containing the integer values from `start` to `end`, inclusive, by increment of `step`.
`generate_series(start: timestamp, end: timestamp, step: interval) → timestamp`	Produces a virtual table containing the timestamp values from `start` to `end`, inclusive, by increment of `step`.
`generate_subscripts(array: anyelement[]) → int`	Returns a series comprising the given array’s subscripts.
`generate_subscripts(array: anyelement[], dim: int) → int`	Returns a series comprising the given array’s subscripts.
`generate_subscripts(array: anyelement[], dim: int, reverse: bool) → int`	Returns a series comprising the given array’s subscripts. When reverse is true, the series is returned in reverse order.
`information_schema._pg_expandarray(input: anyelement[]) → anyelement`	Returns the input array as a set of rows with an index
`json_array_elements(input: jsonb) → jsonb`	Expands a JSON array to a set of JSON values.
`json_array_elements_text(input: jsonb) → string`	Expands a JSON array to a set of text values.
`json_each(input: jsonb) → tuple{string AS key, jsonb AS value}`	Expands the outermost JSON or JSONB object into a set of key/value pairs.
`json_each_text(input: jsonb) → tuple{string AS key, string AS value}`	Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.
`json_object_keys(input: jsonb) → string`	Returns sorted set of keys in the outermost JSON object.
`jsonb_array_elements(input: jsonb) → jsonb`	Expands a JSON array to a set of JSON values.
`jsonb_array_elements_text(input: jsonb) → string`	Expands a JSON array to a set of text values.
`jsonb_each(input: jsonb) → tuple{string AS key, jsonb AS value}`	Expands the outermost JSON or JSONB object into a set of key/value pairs.
`jsonb_each_text(input: jsonb) → tuple{string AS key, string AS value}`	Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.
`jsonb_object_keys(input: jsonb) → string`	Returns sorted set of keys in the outermost JSON object.
`pg_get_keywords() → tuple{string AS word, string AS catcode, string AS catdesc}`	Produces a virtual table containing the keywords known to the SQL parser.
`unnest(input: anyelement[]) → anyelement`	Returns the input array as a set of rows

String and byte functions

Function → Returns Description

ascii(val: string) → int

Returns the character code of the first character in val. Despite the name, the function supports Unicode too.

bit_length(val: bytes) → int

Calculates the number of bits in val.

bit_length(val: string) → int

Calculates the number of bits used to represent val.

btrim(input: string, trim_chars: string) → string

Removes any characters included in trim_chars from the beginning or end of input (applies recursively).

For example, btrim('doggie', 'eod') returns ggi.

btrim(val: string) → string

Removes all spaces from the beginning and end of val.

char_length(val: bytes) → int

Calculates the number of bytes in val.

char_length(val: string) → int

Calculates the number of characters in val.

character_length(val: bytes) → int

Calculates the number of bytes in val.

character_length(val: string) → int

Calculates the number of characters in val.

chr(val: int) → string

Returns the character with the code given in val. Inverse function of ascii().

concat(string...) → string

Concatenates a comma-separated list of strings.

concat_ws(string...) → string

Uses the first argument as a separator between the concatenation of the subsequent arguments.

For example concat_ws('!','wow','great') returns wow!great.

convert_from(str: bytes, enc: string) → string

Decode the bytes in str into a string using encoding enc. Supports encodings ‘UTF8’ and ‘LATIN1’.

convert_to(str: string, enc: string) → bytes

Encode the string str as a byte array using encoding enc. Supports encodings ‘UTF8’ and ‘LATIN1’.

decode(text: string, format: string) → bytes

Decodes data using format (hex / escape / base64).

encode(data: bytes, format: string) → string

Encodes data using format (hex / escape / base64).

from_ip(val: bytes) → string

Converts the byte string representation of an IP to its character string representation.

from_uuid(val: bytes) → string

Converts the byte string representation of a UUID to its character string representation.

initcap(val: string) → string

Capitalizes the first letter of val.

left(input: bytes, return_set: int) → bytes

Returns the first return_set bytes from input.

left(input: string, return_set: int) → string

Returns the first return_set characters from input.

length(val: bytes) → int

Calculates the number of bytes in val.

length(val: string) → int

Calculates the number of characters in val.

lower(val: string) → string

Converts all characters in val to their lower-case equivalents.

lpad(string: string, length: int) → string

Pads string to length by adding ’ ’ to the left of string.If string is longer than length it is truncated.

lpad(string: string, length: int, fill: string) → string

Pads string by adding fill to the left of string to make it length. If string is longer than length it is truncated.

ltrim(input: string, trim_chars: string) → string

Removes any characters included in trim_chars from the beginning (left-hand side) of input (applies recursively).

For example, ltrim('doggie', 'od') returns ggie.

ltrim(val: string) → string

Removes all spaces from the beginning (left-hand side) of val.

md5(bytes...) → string

Calculates the MD5 hash value of a set of values.

md5(string...) → string

Calculates the MD5 hash value of a set of values.

octet_length(val: bytes) → int

Calculates the number of bytes in val.

octet_length(val: string) → int

Calculates the number of bytes used to represent val.

overlay(input: string, overlay_val: string, start_pos: int) → string

Replaces characters in input with overlay_val starting at start_pos (begins at 1).

For example, overlay('doggie', 'CAT', 2) returns dCATie.

overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string

Deletes the characters in input between start_pos and end_pos (count starts at 1), and then insert overlay_val at start_pos.

pg_collation_for(str: anyelement) → string

Returns the collation of the argument

quote_ident(val: string) → string

Return val suitably quoted to serve as identifier in a SQL statement.

quote_literal(val: string) → string

Return val suitably quoted to serve as string literal in a SQL statement.

quote_literal(val: anyelement) → string

Coerce val to a string and then quote it as a literal.

quote_nullable(val: string) → string

Coerce val to a string and then quote it as a literal. If val is NULL, returns ‘NULL’.

quote_nullable(val: anyelement) → string

Coerce val to a string and then quote it as a literal. If val is NULL, returns ‘NULL’.

regexp_extract(input: string, regex: string) → string

Returns the first match for the Regular Expression regex in input.

regexp_replace(input: string, regex: string, replace: string) → string

Replaces matches for the Regular Expression regex in input with the Regular Expression replace.

regexp_replace(input: string, regex: string, replace: string, flags: string) → string

Replaces matches for the regular expression regex in input with the regular expression replace using flags.

CockroachDB supports the following flags:

Flag	Description
c	Case-sensitive matching
g	Global matching (match each substring instead of only the first)
i	Case-insensitive matching
m or n	Newline-sensitive (see below)
p	Partial newline-sensitive matching (see below)
s	Newline-insensitive (default)
w	Inverse partial newline-sensitive matching (see below)

Mode	`.` and `[^...]` match newlines	`^` and `$` match line boundaries
s	yes	no
w	yes	yes
p	no	no
m/n	no	yes

repeat(input: string, repeat_counter: int) → string

Concatenates input repeat_counter number of times.

For example, repeat('dog', 2) returns dogdog.

replace(input: string, find: string, replace: string) → string

Replaces all occurrences of find with replace in input

reverse(val: string) → string

Reverses the order of the string’s characters.

right(input: bytes, return_set: int) → bytes

Returns the last return_set bytes from input.

right(input: string, return_set: int) → string

Returns the last return_set characters from input.

rpad(string: string, length: int) → string

Pads string to length by adding ’ ’ to the right of string. If string is longer than length it is truncated.

rpad(string: string, length: int, fill: string) → string

Pads string to length by adding fill to the right of string. If string is longer than length it is truncated.

rtrim(input: string, trim_chars: string) → string

Removes any characters included in trim_chars from the end (right-hand side) of input (applies recursively).

For example, rtrim('doggie', 'ei') returns dogg.

rtrim(val: string) → string

Removes all spaces from the end (right-hand side) of val.

sha1(bytes...) → string

Calculates the SHA1 hash value of a set of values.

sha1(string...) → string

Calculates the SHA1 hash value of a set of values.

sha256(bytes...) → string

Calculates the SHA256 hash value of a set of values.

sha256(string...) → string

Calculates the SHA256 hash value of a set of values.

sha512(bytes...) → string

Calculates the SHA512 hash value of a set of values.

sha512(string...) → string

Calculates the SHA512 hash value of a set of values.

split_part(input: string, delimiter: string, return_index_pos: int) → string

Splits input on delimiter and return the value in the return_index_pos position (starting at 1).

For example, split_part('123.456.789.0','.',3)returns 789.

strpos(input: string, find: string) → int

Calculates the position where the string find begins in input.

For example, strpos('doggie', 'gie') returns 4.

substr(input: string, regex: string) → string

Returns a substring of input that matches the regular expression regex.

substr(input: string, regex: string, escape_char: string) → string

Returns a substring of input that matches the regular expression regex using escape_char as your escape character instead of </code>.

substr(input: string, start_pos: int) → string

Returns a substring of input starting at start_pos (count starts at 1).

substr(input: string, start_pos: int, length: int) → string

Returns a substring of input starting at start_pos (count starts at 1) and including up to length characters.

substring(input: string, regex: string) → string

Returns a substring of input that matches the regular expression regex.

substring(input: string, regex: string, escape_char: string) → string

Returns a substring of input that matches the regular expression regex using escape_char as your escape character instead of </code>.

substring(input: string, start_pos: int) → string

Returns a substring of input starting at start_pos (count starts at 1).

substring(input: string, start_pos: int, length: int) → string

Returns a substring of input starting at start_pos (count starts at 1) and including up to length characters.

to_english(val: int) → string

This function enunciates the value of its argument using English cardinals.

to_hex(val: bytes) → string

Converts val to its hexadecimal representation.

to_hex(val: int) → string

Converts val to its hexadecimal representation.

to_ip(val: string) → bytes

Converts the character string representation of an IP to its byte string representation.

to_uuid(val: string) → bytes

Converts the character string representation of a UUID to its byte string representation.

translate(input: string, find: string, replace: string) → string

In input, replaces the first character from find with the first character in replace; repeat for each character in find.

For example, translate('doggie', 'dog', '123'); returns 1233ie.

upper(val: string) → string

Converts all characters in val to their to their upper-case equivalents.

System info functions

Function → Returns	Description
`cluster_logical_timestamp() → decimal`	Returns the logical time of the current transaction. This function is reserved for testing purposes by CockroachDB developers and its definition may change without prior notice. Note that uses of this function disable server-side optimizations and may increase either contention or retry errors, or both.
`crdb_internal.check_consistency(stats_only: bool, start_key: bytes, end_key: bytes) → tuple{int AS range_id, bytes AS start_key, string AS start_key_pretty, string AS status, string AS detail}`	Runs a consistency check on ranges touching the specified key range. an empty start or end key is treated as the minimum and maximum possible, respectively. stats_only should only be set to false when targeting a small number of ranges to avoid overloading the cluster. Each returned row contains the range ID, the status (a roachpb.CheckConsistencyResponse_Status), and verbose detail. Example usage: SELECT * FROM crdb_internal.check_consistency(true, ‘\x02’, ‘\x04’)
`crdb_internal.cluster_id() → uuid`	Returns the cluster ID.
`crdb_internal.cluster_name() → string`	Returns the cluster name.
`crdb_internal.encode_key(table_id: int, index_id: int, row_tuple: tuple) → bytes`	Generate the key for a row on a particular table and index.
`crdb_internal.force_assertion_error(msg: string) → int`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.force_error(errorCode: string, msg: string) → int`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.force_log_fatal(msg: string) → int`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.force_panic(msg: string) → int`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.force_retry(val: interval) → int`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.get_namespace_id(parent_id: int, name: string) → int`
`crdb_internal.get_zone_config(namespace_id: int) → bytes`
`crdb_internal.is_admin() → bool`	Retrieves the current user’s admin status.
`crdb_internal.json_num_index_entries(val: jsonb) → int`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.lease_holder(key: bytes) → int`	This function is used to fetch the leaseholder corresponding to a request key
`crdb_internal.locality_value(key: string) → string`	Returns the value of the specified locality key.
`crdb_internal.no_constant_folding(input: anyelement) → anyelement`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.node_executable_version() → string`	Returns the version of CockroachDB this node is running.
`crdb_internal.pretty_key(raw_key: bytes, skip_fields: int) → string`	This function is used only by CockroachDB’s developers for testing purposes.
`crdb_internal.range_stats(key: bytes) → jsonb`	This function is used to retrieve range statistics information as a JSON object.
`crdb_internal.round_decimal_values(val: decimal, scale: int) → decimal`	This function is used internally to round decimal values during mutations.
`crdb_internal.round_decimal_values(val: decimal[], scale: int) → decimal[]`	This function is used internally to round decimal array values during mutations.
`crdb_internal.set_vmodule(vmodule_string: string) → int`	Set the equivalent of the `--vmodule` flag on the gateway node processing this request; it affords control over the logging verbosity of different files. Example syntax: `crdb_internal.set_vmodule('recordio=2,file=1,gfs*=3')`. Reset with: `crdb_internal.set_vmodule('')`. Raising the verbosity can severely affect performance.
`current_database() → string`	Returns the current database.
`current_schema() → string`	Returns the current schema.
`current_schemas(include_pg_catalog: bool) → string[]`	Returns the valid schemas in the search path.
`current_user() → string`	Returns the current user. This function is provided for compatibility with PostgreSQL.
`version() → string`	Returns the node’s version of CockroachDB.

TUPLE functions

Function → Returns	Description
`row_to_json(row: tuple) → jsonb`	Returns the row as a JSON object.

Compatibility functions

Function → Returns	Description
`format_type(type_oid: oid, typemod: int) → string`	Returns the SQL name of a data type that is identified by its type OID and possibly a type modifier. Currently, the type modifier is ignored.
`has_any_column_privilege(table: string, privilege: string) → bool`	Returns whether or not the current user has privileges for any column of table.
`has_any_column_privilege(table: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for any column of table.
`has_any_column_privilege(user: string, table: string, privilege: string) → bool`	Returns whether or not the user has privileges for any column of table.
`has_any_column_privilege(user: string, table: oid, privilege: string) → bool`	Returns whether or not the user has privileges for any column of table.
`has_any_column_privilege(user: oid, table: string, privilege: string) → bool`	Returns whether or not the user has privileges for any column of table.
`has_any_column_privilege(user: oid, table: oid, privilege: string) → bool`	Returns whether or not the user has privileges for any column of table.
`has_column_privilege(table: string, column: int, privilege: string) → bool`	Returns whether or not the current user has privileges for column.
`has_column_privilege(table: string, column: string, privilege: string) → bool`	Returns whether or not the current user has privileges for column.
`has_column_privilege(table: oid, column: int, privilege: string) → bool`	Returns whether or not the current user has privileges for column.
`has_column_privilege(table: oid, column: string, privilege: string) → bool`	Returns whether or not the current user has privileges for column.
`has_column_privilege(user: string, table: string, column: int, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: string, table: string, column: string, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: string, table: oid, column: int, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: string, table: oid, column: string, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: oid, table: string, column: int, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: oid, table: string, column: string, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: oid, table: oid, column: int, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_column_privilege(user: oid, table: oid, column: string, privilege: string) → bool`	Returns whether or not the user has privileges for column.
`has_database_privilege(database: string, privilege: string) → bool`	Returns whether or not the current user has privileges for database.
`has_database_privilege(database: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for database.
`has_database_privilege(user: string, database: string, privilege: string) → bool`	Returns whether or not the user has privileges for database.
`has_database_privilege(user: string, database: oid, privilege: string) → bool`	Returns whether or not the user has privileges for database.
`has_database_privilege(user: oid, database: string, privilege: string) → bool`	Returns whether or not the user has privileges for database.
`has_database_privilege(user: oid, database: oid, privilege: string) → bool`	Returns whether or not the user has privileges for database.
`has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool`	Returns whether or not the current user has privileges for foreign-data wrapper.
`has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for foreign-data wrapper.
`has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool`	Returns whether or not the user has privileges for foreign-data wrapper.
`has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool`	Returns whether or not the user has privileges for foreign-data wrapper.
`has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool`	Returns whether or not the user has privileges for foreign-data wrapper.
`has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool`	Returns whether or not the user has privileges for foreign-data wrapper.
`has_function_privilege(function: string, privilege: string) → bool`	Returns whether or not the current user has privileges for function.
`has_function_privilege(function: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for function.
`has_function_privilege(user: string, function: string, privilege: string) → bool`	Returns whether or not the user has privileges for function.
`has_function_privilege(user: string, function: oid, privilege: string) → bool`	Returns whether or not the user has privileges for function.
`has_function_privilege(user: oid, function: string, privilege: string) → bool`	Returns whether or not the user has privileges for function.
`has_function_privilege(user: oid, function: oid, privilege: string) → bool`	Returns whether or not the user has privileges for function.
`has_language_privilege(language: string, privilege: string) → bool`	Returns whether or not the current user has privileges for language.
`has_language_privilege(language: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for language.
`has_language_privilege(user: string, language: string, privilege: string) → bool`	Returns whether or not the user has privileges for language.
`has_language_privilege(user: string, language: oid, privilege: string) → bool`	Returns whether or not the user has privileges for language.
`has_language_privilege(user: oid, language: string, privilege: string) → bool`	Returns whether or not the user has privileges for language.
`has_language_privilege(user: oid, language: oid, privilege: string) → bool`	Returns whether or not the user has privileges for language.
`has_schema_privilege(schema: string, privilege: string) → bool`	Returns whether or not the current user has privileges for schema.
`has_schema_privilege(schema: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for schema.
`has_schema_privilege(user: string, schema: string, privilege: string) → bool`	Returns whether or not the user has privileges for schema.
`has_schema_privilege(user: string, schema: oid, privilege: string) → bool`	Returns whether or not the user has privileges for schema.
`has_schema_privilege(user: oid, schema: string, privilege: string) → bool`	Returns whether or not the user has privileges for schema.
`has_schema_privilege(user: oid, schema: oid, privilege: string) → bool`	Returns whether or not the user has privileges for schema.
`has_sequence_privilege(sequence: string, privilege: string) → bool`	Returns whether or not the current user has privileges for sequence.
`has_sequence_privilege(sequence: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for sequence.
`has_sequence_privilege(user: string, sequence: string, privilege: string) → bool`	Returns whether or not the user has privileges for sequence.
`has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool`	Returns whether or not the user has privileges for sequence.
`has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool`	Returns whether or not the user has privileges for sequence.
`has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool`	Returns whether or not the user has privileges for sequence.
`has_server_privilege(server: string, privilege: string) → bool`	Returns whether or not the current user has privileges for foreign server.
`has_server_privilege(server: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for foreign server.
`has_server_privilege(user: string, server: string, privilege: string) → bool`	Returns whether or not the user has privileges for foreign server.
`has_server_privilege(user: string, server: oid, privilege: string) → bool`	Returns whether or not the user has privileges for foreign server.
`has_server_privilege(user: oid, server: string, privilege: string) → bool`	Returns whether or not the user has privileges for foreign server.
`has_server_privilege(user: oid, server: oid, privilege: string) → bool`	Returns whether or not the user has privileges for foreign server.
`has_table_privilege(table: string, privilege: string) → bool`	Returns whether or not the current user has privileges for table.
`has_table_privilege(table: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for table.
`has_table_privilege(user: string, table: string, privilege: string) → bool`	Returns whether or not the user has privileges for table.
`has_table_privilege(user: string, table: oid, privilege: string) → bool`	Returns whether or not the user has privileges for table.
`has_table_privilege(user: oid, table: string, privilege: string) → bool`	Returns whether or not the user has privileges for table.
`has_table_privilege(user: oid, table: oid, privilege: string) → bool`	Returns whether or not the user has privileges for table.
`has_tablespace_privilege(tablespace: string, privilege: string) → bool`	Returns whether or not the current user has privileges for tablespace.
`has_tablespace_privilege(tablespace: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for tablespace.
`has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool`	Returns whether or not the user has privileges for tablespace.
`has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool`	Returns whether or not the user has privileges for tablespace.
`has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool`	Returns whether or not the user has privileges for tablespace.
`has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool`	Returns whether or not the user has privileges for tablespace.
`has_type_privilege(type: string, privilege: string) → bool`	Returns whether or not the current user has privileges for type.
`has_type_privilege(type: oid, privilege: string) → bool`	Returns whether or not the current user has privileges for type.
`has_type_privilege(user: string, type: string, privilege: string) → bool`	Returns whether or not the user has privileges for type.
`has_type_privilege(user: string, type: oid, privilege: string) → bool`	Returns whether or not the user has privileges for type.
`has_type_privilege(user: oid, type: string, privilege: string) → bool`	Returns whether or not the user has privileges for type.
`has_type_privilege(user: oid, type: oid, privilege: string) → bool`	Returns whether or not the user has privileges for type.
`oid(int: int) → oid`	Converts an integer to an OID.
`pg_sleep(seconds: float) → bool`	pg_sleep makes the current session’s process sleep until seconds seconds have elapsed. seconds is a value of type double precision, so fractional-second delays can be specified.

Aggregate functions

For examples showing how to use aggregate functions, see the SELECT clause documentation.

Note:

New in v19.2: Non-commutative aggregate functions are sensitive to the order in which the rows are processed in the surrounding SELECT clause. To specify the order in which input rows are processed, you can add an ORDER BY clause within the function argument list. For examples, see the SELECT clause documentation.

Function → Returns	Description
`array_agg(arg1: bool) → bool[]`	Aggregates the selected values into an array.
`array_agg(arg1: bytes) → bytes[]`	Aggregates the selected values into an array.
`array_agg(arg1: date) → date[]`	Aggregates the selected values into an array.
`array_agg(arg1: decimal) → decimal[]`	Aggregates the selected values into an array.
`array_agg(arg1: float) → float[]`	Aggregates the selected values into an array.
`array_agg(arg1: inet) → inet[]`	Aggregates the selected values into an array.
`array_agg(arg1: int) → int[]`	Aggregates the selected values into an array.
`array_agg(arg1: interval) → interval[]`	Aggregates the selected values into an array.
`array_agg(arg1: string) → string[]`	Aggregates the selected values into an array.
`array_agg(arg1: time) → time[]`	Aggregates the selected values into an array.
`array_agg(arg1: timestamp) → timestamp[]`	Aggregates the selected values into an array.
`array_agg(arg1: timestamptz) → timestamptz[]`	Aggregates the selected values into an array.
`array_agg(arg1: uuid) → uuid[]`	Aggregates the selected values into an array.
`array_agg(arg1: oid) → oid[]`	Aggregates the selected values into an array.
`array_agg(arg1: varbit) → varbit[]`	Aggregates the selected values into an array.
`avg(arg1: decimal) → decimal`	Calculates the average of the selected values.
`avg(arg1: float) → float`	Calculates the average of the selected values.
`avg(arg1: int) → decimal`	Calculates the average of the selected values.
`avg(arg1: interval) → interval`	Calculates the average of the selected values.
`bool_and(arg1: bool) → bool`	Calculates the boolean value of `AND`ing all selected values.
`bool_or(arg1: bool) → bool`	Calculates the boolean value of `OR`ing all selected values.
`concat_agg(arg1: bytes) → bytes`	Concatenates all selected values.
`concat_agg(arg1: string) → string`	Concatenates all selected values.
`count(arg1: anyelement) → int`	Calculates the number of selected elements.
`count_rows() → int`	Calculates the number of rows.
`json_agg(arg1: anyelement) → jsonb`	Aggregates values as a JSON or JSONB array.
`jsonb_agg(arg1: anyelement) → jsonb`	Aggregates values as a JSON or JSONB array.
`max(arg1: bool) → bool`	Identifies the maximum selected value.
`max(arg1: bytes) → bytes`	Identifies the maximum selected value.
`max(arg1: date) → date`	Identifies the maximum selected value.
`max(arg1: decimal) → decimal`	Identifies the maximum selected value.
`max(arg1: float) → float`	Identifies the maximum selected value.
`max(arg1: inet) → inet`	Identifies the maximum selected value.
`max(arg1: int) → int`	Identifies the maximum selected value.
`max(arg1: interval) → interval`	Identifies the maximum selected value.
`max(arg1: string) → string`	Identifies the maximum selected value.
`max(arg1: time) → time`	Identifies the maximum selected value.
`max(arg1: timestamp) → timestamp`	Identifies the maximum selected value.
`max(arg1: timestamptz) → timestamptz`	Identifies the maximum selected value.
`max(arg1: uuid) → uuid`	Identifies the maximum selected value.
`max(arg1: jsonb) → jsonb`	Identifies the maximum selected value.
`max(arg1: oid) → oid`	Identifies the maximum selected value.
`max(arg1: varbit) → varbit`	Identifies the maximum selected value.
`min(arg1: bool) → bool`	Identifies the minimum selected value.
`min(arg1: bytes) → bytes`	Identifies the minimum selected value.
`min(arg1: date) → date`	Identifies the minimum selected value.
`min(arg1: decimal) → decimal`	Identifies the minimum selected value.
`min(arg1: float) → float`	Identifies the minimum selected value.
`min(arg1: inet) → inet`	Identifies the minimum selected value.
`min(arg1: int) → int`	Identifies the minimum selected value.
`min(arg1: interval) → interval`	Identifies the minimum selected value.
`min(arg1: string) → string`	Identifies the minimum selected value.
`min(arg1: time) → time`	Identifies the minimum selected value.
`min(arg1: timestamp) → timestamp`	Identifies the minimum selected value.
`min(arg1: timestamptz) → timestamptz`	Identifies the minimum selected value.
`min(arg1: uuid) → uuid`	Identifies the minimum selected value.
`min(arg1: jsonb) → jsonb`	Identifies the minimum selected value.
`min(arg1: oid) → oid`	Identifies the minimum selected value.
`min(arg1: varbit) → varbit`	Identifies the minimum selected value.
`sqrdiff(arg1: decimal) → decimal`	Calculates the sum of squared differences from the mean of the selected values.
`sqrdiff(arg1: float) → float`	Calculates the sum of squared differences from the mean of the selected values.
`sqrdiff(arg1: int) → decimal`	Calculates the sum of squared differences from the mean of the selected values.
`stddev(arg1: decimal) → decimal`	Calculates the standard deviation of the selected values.
`stddev(arg1: float) → float`	Calculates the standard deviation of the selected values.
`stddev(arg1: int) → decimal`	Calculates the standard deviation of the selected values.
`string_agg(arg1: bytes, arg2: bytes) → bytes`	Concatenates all selected values using the provided delimiter.
`string_agg(arg1: string, arg2: string) → string`	Concatenates all selected values using the provided delimiter.
`sum(arg1: decimal) → decimal`	Calculates the sum of the selected values.
`sum(arg1: float) → float`	Calculates the sum of the selected values.
`sum(arg1: int) → decimal`	Calculates the sum of the selected values.
`sum(arg1: interval) → interval`	Calculates the sum of the selected values.
`sum_int(arg1: int) → int`	Calculates the sum of the selected values.
`variance(arg1: decimal) → decimal`	Calculates the variance of the selected values.
`variance(arg1: float) → float`	Calculates the variance of the selected values.
`variance(arg1: int) → decimal`	Calculates the variance of the selected values.
`xor_agg(arg1: bytes) → bytes`	Calculates the bitwise XOR of the selected values.
`xor_agg(arg1: int) → int`	Calculates the bitwise XOR of the selected values.

Window functions

Function → Returns	Description
`cume_dist() → float`	Calculates the relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).
`dense_rank() → int`	Calculates the rank of the current row without gaps; this function counts peer groups.
`first_value(val: bool) → bool`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: bytes) → bytes`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: date) → date`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: decimal) → decimal`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: float) → float`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: inet) → inet`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: int) → int`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: interval) → interval`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: string) → string`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: time) → time`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: timestamp) → timestamp`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: timestamptz) → timestamptz`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: uuid) → uuid`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: jsonb) → jsonb`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: oid) → oid`	Returns `val` evaluated at the row that is the first row of the window frame.
`first_value(val: varbit) → varbit`	Returns `val` evaluated at the row that is the first row of the window frame.
`lag(val: bool) → bool`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: bool, n: int) → bool`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: bool, n: int, default: bool) → bool`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: bytes) → bytes`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: bytes, n: int) → bytes`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: bytes, n: int, default: bytes) → bytes`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: date) → date`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: date, n: int) → date`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: date, n: int, default: date) → date`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: decimal) → decimal`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: decimal, n: int) → decimal`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: decimal, n: int, default: decimal) → decimal`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: float) → float`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: float, n: int) → float`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: float, n: int, default: float) → float`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: inet) → inet`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: inet, n: int) → inet`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: inet, n: int, default: inet) → inet`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: int) → int`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: int, n: int) → int`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: int, n: int, default: int) → int`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: interval) → interval`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: interval, n: int) → interval`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: interval, n: int, default: interval) → interval`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: string) → string`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: string, n: int) → string`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: string, n: int, default: string) → string`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: time) → time`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: time, n: int) → time`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: time, n: int, default: time) → time`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: timestamp) → timestamp`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: timestamp, n: int) → timestamp`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: timestamp, n: int, default: timestamp) → timestamp`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: timestamptz) → timestamptz`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: timestamptz, n: int) → timestamptz`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: timestamptz, n: int, default: timestamptz) → timestamptz`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: uuid) → uuid`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: uuid, n: int) → uuid`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: uuid, n: int, default: uuid) → uuid`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: jsonb) → jsonb`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: jsonb, n: int) → jsonb`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: jsonb, n: int, default: jsonb) → jsonb`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: oid) → oid`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: oid, n: int) → oid`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: oid, n: int, default: oid) → oid`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lag(val: varbit) → varbit`	Returns `val` evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.
`lag(val: varbit, n: int) → varbit`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lag(val: varbit, n: int, default: varbit) → varbit`	Returns `val` evaluated at the row that is `n` rows before the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`last_value(val: bool) → bool`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: bytes) → bytes`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: date) → date`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: decimal) → decimal`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: float) → float`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: inet) → inet`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: int) → int`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: interval) → interval`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: string) → string`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: time) → time`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: timestamp) → timestamp`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: timestamptz) → timestamptz`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: uuid) → uuid`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: jsonb) → jsonb`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: oid) → oid`	Returns `val` evaluated at the row that is the last row of the window frame.
`last_value(val: varbit) → varbit`	Returns `val` evaluated at the row that is the last row of the window frame.
`lead(val: bool) → bool`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: bool, n: int) → bool`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: bool, n: int, default: bool) → bool`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: bytes) → bytes`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: bytes, n: int) → bytes`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: bytes, n: int, default: bytes) → bytes`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: date) → date`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: date, n: int) → date`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: date, n: int, default: date) → date`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: decimal) → decimal`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: decimal, n: int) → decimal`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: decimal, n: int, default: decimal) → decimal`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: float) → float`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: float, n: int) → float`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: float, n: int, default: float) → float`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: inet) → inet`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: inet, n: int) → inet`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: inet, n: int, default: inet) → inet`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: int) → int`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: int, n: int) → int`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: int, n: int, default: int) → int`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: interval) → interval`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: interval, n: int) → interval`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: interval, n: int, default: interval) → interval`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: string) → string`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: string, n: int) → string`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: string, n: int, default: string) → string`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: time) → time`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: time, n: int) → time`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: time, n: int, default: time) → time`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: timestamp) → timestamp`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: timestamp, n: int) → timestamp`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: timestamp, n: int, default: timestamp) → timestamp`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: timestamptz) → timestamptz`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: timestamptz, n: int) → timestamptz`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: timestamptz, n: int, default: timestamptz) → timestamptz`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: uuid) → uuid`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: uuid, n: int) → uuid`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: uuid, n: int, default: uuid) → uuid`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: jsonb) → jsonb`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: jsonb, n: int) → jsonb`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: jsonb, n: int, default: jsonb) → jsonb`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: oid) → oid`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: oid, n: int) → oid`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: oid, n: int, default: oid) → oid`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`lead(val: varbit) → varbit`	Returns `val` evaluated at the following row within current row’s partition; if there is no such row, instead returns null.
`lead(val: varbit, n: int) → varbit`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such row, instead returns null. `n` is evaluated with respect to the current row.
`lead(val: varbit, n: int, default: varbit) → varbit`	Returns `val` evaluated at the row that is `n` rows after the current row within its partition; if there is no such, row, instead returns `default` (which must be of the same type as `val`). Both `n` and `default` are evaluated with respect to the current row.
`nth_value(val: bool, n: int) → bool`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: bytes, n: int) → bytes`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: date, n: int) → date`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: decimal, n: int) → decimal`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: float, n: int) → float`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: inet, n: int) → inet`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: int, n: int) → int`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: interval, n: int) → interval`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: string, n: int) → string`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: time, n: int) → time`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: timestamp, n: int) → timestamp`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: timestamptz, n: int) → timestamptz`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: uuid, n: int) → uuid`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: jsonb, n: int) → jsonb`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: oid, n: int) → oid`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`nth_value(val: varbit, n: int) → varbit`	Returns `val` evaluated at the row that is the `n`th row of the window frame (counting from 1); null if no such row.
`ntile(n: int) → int`	Calculates an integer ranging from 1 to `n`, dividing the partition as equally as possible.
`percent_rank() → float`	Calculates the relative rank of the current row: (rank - 1) / (total rows - 1).
`rank() → int`	Calculates the rank of the current row with gaps; same as row_number of its first peer.
`row_number() → int`	Calculates the number of the current row within its partition, counting from 1.

Operators

The following table lists all CockroachDB operators from highest to lowest precedence, i.e., the order in which they will be evaluated within a statement. Operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right.

Order of Precedence	Operator	Name	Operator Arity
1	`.`	Member field access operator	binary
2	`::`	Type cast	binary
3	`-`	Unary minus	unary (prefix)
	`~`	Bitwise not	unary (prefix)
4	`^`	Exponentiation	binary
5	`*`	Multiplication	binary
	`/`	Division	binary
	`//`	Floor division	binary
	`%`	Modulo	binary
6	`+`	Addition	binary
	`-`	Subtraction	binary
7	`<<`	Bitwise left-shift	binary
	`>>`	Bitwise right-shift	binary
8	`&`	Bitwise AND	binary
9	`#`	Bitwise XOR	binary
10	`\|`	Bitwise OR	binary
11	`\|\|`	Concatenation	binary
	`< ANY`, `SOME`, `ALL`	Multi-valued "less than" comparison	binary
	`> ANY`, `SOME`, `ALL`	Multi-valued "greater than" comparison	binary
	`= ANY`, `SOME`, `ALL`	Multi-valued "equal" comparison	binary
	`<= ANY`, `SOME`, `ALL`	Multi-valued "less than or equal" comparison	binary
	`>= ANY`, `SOME`, `ALL`	Multi-valued "greater than or equal" comparison	binary
	`<> ANY` / `!= ANY`, `<> SOME` / `!= SOME`, `<> ALL` / `!= ALL`	Multi-valued "not equal" comparison	binary
	`[NOT] LIKE ANY`, `[NOT] LIKE SOME`, `[NOT] LIKE ALL`	Multi-valued `LIKE` comparison	binary
	`[NOT] ILIKE ANY`, `[NOT] ILIKE SOME`, `[NOT] ILIKE ALL`	Multi-valued `ILIKE` comparison	binary
12	`[NOT] BETWEEN`	Value is [not] within the range specified	binary
	`[NOT] BETWEEN SYMMETRIC`	Like `[NOT] BETWEEN`, but in non-sorted order. For example, whereas `a BETWEEN b AND c` means `b <= a <= c`, `a BETWEEN SYMMETRIC b AND c` means `(b <= a <= c) OR (c <= a <= b)`.	binary
	`[NOT] IN`	Value is [not] in the set of values specified	binary
	`[NOT] LIKE`	Matches [or not] LIKE expression, case sensitive	binary
	`[NOT] ILIKE`	Matches [or not] LIKE expression, case insensitive	binary
	`[NOT] SIMILAR`	Matches [or not] SIMILAR TO regular expression	binary
	`~`	Matches regular expression, case sensitive	binary
	`!~`	Does not match regular expression, case sensitive	binary
	`~*`	Matches regular expression, case insensitive	binary
	`!~*`	Does not match regular expression, case insensitive	binary
13	`=`	Equal	binary
	`<`	Less than	binary
	`>`	Greater than	binary
	`<=`	Less than or equal to	binary
	`>=`	Greater than or equal to	binary
	`!=`, `<>`	Not equal	binary
14	`IS [DISTINCT FROM]`	Equal, considering `NULL` as value	binary
	`IS NOT [DISTINCT FROM]`	`a IS NOT b` equivalent to `NOT (a IS b)`	binary
	`ISNULL`, `IS UNKNOWN` , `NOTNULL`, `IS NOT UNKNOWN`	Equivalent to `IS NULL` / `IS NOT NULL`	unary (postfix)
	`IS NAN`, `IS NOT NAN`	Comparison with the floating-point NaN value	unary (postfix)
	`IS OF(...)`	Type predicate	unary (postfix)
15	`NOT`	Logical NOT	unary
16	`AND`	Logical AND	binary
17	`OR`	Logical OR	binary

Supported operations

`#`	Return
int `#` int	int
varbit `#` varbit	varbit

`#>`	Return
jsonb `#>` string[]	jsonb

`#>>`	Return
jsonb `#>>` string[]	string

`%`	Return
decimal `%` decimal	decimal
decimal `%` int	decimal
float `%` float	float
int `%` decimal	decimal
int `%` int	int

`&`	Return
inet `&` inet	inet
int `&` int	int
varbit `&` varbit	varbit

`&&`	Return
anyelement `&&` anyelement	bool
inet `&&` inet	bool

`*`	Return
decimal `*` decimal	decimal
decimal `*` int	decimal
decimal `*` interval	interval
float `*` float	float
float `*` interval	interval
int `*` decimal	decimal
int `*` int	int
int `*` interval	interval
interval `*` decimal	interval
interval `*` float	interval
interval `*` int	interval

`+`	Return
date `+` int	date
date `+` interval	timestamptz
date `+` time	timestamp
decimal `+` decimal	decimal
decimal `+` int	decimal
float `+` float	float
inet `+` int	inet
int `+` date	date
int `+` decimal	decimal
int `+` inet	inet
int `+` int	int
interval `+` date	timestamptz
interval `+` interval	interval
interval `+` time	time
interval `+` timestamp	timestamp
interval `+` timestamptz	timestamptz
time `+` date	timestamp
time `+` interval	time
timestamp `+` interval	timestamp
timestamptz `+` interval	timestamptz

`-`	Return
`-`decimal	decimal
`-`float	float
`-`int	int
`-`interval	interval
date `-` date	int
date `-` int	date
date `-` interval	timestamptz
date `-` time	timestamp
decimal `-` decimal	decimal
decimal `-` int	decimal
float `-` float	float
inet `-` inet	int
inet `-` int	inet
int `-` decimal	decimal
int `-` int	int
interval `-` interval	interval
jsonb `-` int	jsonb
jsonb `-` string	jsonb
jsonb `-` string[]	jsonb
time `-` interval	time
time `-` time	interval
timestamp `-` interval	timestamp
timestamp `-` timestamp	interval
timestamp `-` timestamptz	interval
timestamptz `-` interval	timestamptz
timestamptz `-` timestamp	interval
timestamptz `-` timestamptz	interval

`->`	Return
jsonb `->` int	jsonb
jsonb `->` string	jsonb

`->>`	Return
jsonb `->>` int	string
jsonb `->>` string	string

`/`	Return
decimal `/` decimal	decimal
decimal `/` int	decimal
float `/` float	float
int `/` decimal	decimal
int `/` int	decimal
interval `/` float	interval
interval `/` int	interval

`//`	Return
decimal `//` decimal	decimal
decimal `//` int	decimal
float `//` float	float
int `//` decimal	decimal
int `//` int	int

`<`	Return
bool `<` bool	bool
bytes `<` bytes	bool
collatedstring `<` collatedstring	bool
date `<` date	bool
date `<` timestamp	bool
date `<` timestamptz	bool
decimal `<` decimal	bool
decimal `<` float	bool
decimal `<` int	bool
float `<` decimal	bool
float `<` float	bool
float `<` int	bool
inet `<` inet	bool
int `<` decimal	bool
int `<` float	bool
int `<` int	bool
interval `<` interval	bool
oid `<` oid	bool
string `<` string	bool
time `<` time	bool
timestamp `<` date	bool
timestamp `<` timestamp	bool
timestamp `<` timestamptz	bool
timestamptz `<` date	bool
timestamptz `<` timestamp	bool
timestamptz `<` timestamptz	bool
tuple `<` tuple	bool
uuid `<` uuid	bool
varbit `<` varbit	bool

`<<`	Return
inet `<<` inet	bool
int `<<` int	int
varbit `<<` int	varbit

`<=`	Return
bool `<=` bool	bool
bytes `<=` bytes	bool
collatedstring `<=` collatedstring	bool
date `<=` date	bool
date `<=` timestamp	bool
date `<=` timestamptz	bool
decimal `<=` decimal	bool
decimal `<=` float	bool
decimal `<=` int	bool
float `<=` decimal	bool
float `<=` float	bool
float `<=` int	bool
inet `<=` inet	bool
int `<=` decimal	bool
int `<=` float	bool
int `<=` int	bool
interval `<=` interval	bool
oid `<=` oid	bool
string `<=` string	bool
time `<=` time	bool
timestamp `<=` date	bool
timestamp `<=` timestamp	bool
timestamp `<=` timestamptz	bool
timestamptz `<=` date	bool
timestamptz `<=` timestamp	bool
timestamptz `<=` timestamptz	bool
tuple `<=` tuple	bool
uuid `<=` uuid	bool
varbit `<=` varbit	bool

`<@`	Return
anyelement `<@` anyelement	bool
jsonb `<@` jsonb	bool

`=`	Return
bool `=` bool	bool
bool[] `=` bool[]	bool
bytes `=` bytes	bool
bytes[] `=` bytes[]	bool
collatedstring `=` collatedstring	bool
date `=` date	bool
date `=` timestamp	bool
date `=` timestamptz	bool
date[] `=` date[]	bool
decimal `=` decimal	bool
decimal `=` float	bool
decimal `=` int	bool
decimal[] `=` decimal[]	bool
float `=` decimal	bool
float `=` float	bool
float `=` int	bool
float[] `=` float[]	bool
inet `=` inet	bool
inet[] `=` inet[]	bool
int `=` decimal	bool
int `=` float	bool
int `=` int	bool
int[] `=` int[]	bool
interval `=` interval	bool
interval[] `=` interval[]	bool
jsonb `=` jsonb	bool
oid `=` oid	bool
string `=` string	bool
string[] `=` string[]	bool
time `=` time	bool
time[] `=` time[]	bool
timestamp `=` date	bool
timestamp `=` timestamp	bool
timestamp `=` timestamptz	bool
timestamp[] `=` timestamp[]	bool
timestamptz `=` date	bool
timestamptz `=` timestamp	bool
timestamptz `=` timestamptz	bool
timestamptz `=` timestamptz	bool
tuple `=` tuple	bool
uuid `=` uuid	bool
uuid[] `=` uuid[]	bool
varbit `=` varbit	bool

`>>`	Return
inet `>>` inet	bool
int `>>` int	int
varbit `>>` int	varbit

`?`	Return
jsonb `?` string	bool

`?&`	Return
jsonb `?&` string[]	bool

`?\|`	Return
jsonb `?\|` string[]	bool

`@>`	Return
anyelement `@>` anyelement	bool
jsonb `@>` jsonb	bool

`ILIKE`	Return
string `ILIKE` string	bool

`IN`	Return
bool `IN` tuple	bool
bytes `IN` tuple	bool
collatedstring `IN` tuple	bool
date `IN` tuple	bool
decimal `IN` tuple	bool
float `IN` tuple	bool
inet `IN` tuple	bool
int `IN` tuple	bool
interval `IN` tuple	bool
jsonb `IN` tuple	bool
oid `IN` tuple	bool
string `IN` tuple	bool
time `IN` tuple	bool
timestamp `IN` tuple	bool
timestamptz `IN` tuple	bool
tuple `IN` tuple	bool
uuid `IN` tuple	bool
varbit `IN` tuple	bool

`IS NOT DISTINCT FROM`	Return
bool `IS NOT DISTINCT FROM` bool	bool
bool[] `IS NOT DISTINCT FROM` bool[]	bool
bytes `IS NOT DISTINCT FROM` bytes	bool
bytes[] `IS NOT DISTINCT FROM` bytes[]	bool
collatedstring `IS NOT DISTINCT FROM` collatedstring	bool
date `IS NOT DISTINCT FROM` date	bool
date `IS NOT DISTINCT FROM` timestamp	bool
date `IS NOT DISTINCT FROM` timestamptz	bool
date[] `IS NOT DISTINCT FROM` date[]	bool
decimal `IS NOT DISTINCT FROM` decimal	bool
decimal `IS NOT DISTINCT FROM` float	bool
decimal `IS NOT DISTINCT FROM` int	bool
decimal[] `IS NOT DISTINCT FROM` decimal[]	bool
float `IS NOT DISTINCT FROM` decimal	bool
float `IS NOT DISTINCT FROM` float	bool
float `IS NOT DISTINCT FROM` int	bool
float[] `IS NOT DISTINCT FROM` float[]	bool
inet `IS NOT DISTINCT FROM` inet	bool
inet[] `IS NOT DISTINCT FROM` inet[]	bool
int `IS NOT DISTINCT FROM` decimal	bool
int `IS NOT DISTINCT FROM` float	bool
int `IS NOT DISTINCT FROM` int	bool
int[] `IS NOT DISTINCT FROM` int[]	bool
interval `IS NOT DISTINCT FROM` interval	bool
interval[] `IS NOT DISTINCT FROM` interval[]	bool
jsonb `IS NOT DISTINCT FROM` jsonb	bool
oid `IS NOT DISTINCT FROM` oid	bool
string `IS NOT DISTINCT FROM` string	bool
string[] `IS NOT DISTINCT FROM` string[]	bool
time `IS NOT DISTINCT FROM` time	bool
time[] `IS NOT DISTINCT FROM` time[]	bool
timestamp `IS NOT DISTINCT FROM` date	bool
timestamp `IS NOT DISTINCT FROM` timestamp	bool
timestamp `IS NOT DISTINCT FROM` timestamptz	bool
timestamp[] `IS NOT DISTINCT FROM` timestamp[]	bool
timestamptz `IS NOT DISTINCT FROM` date	bool
timestamptz `IS NOT DISTINCT FROM` timestamp	bool
timestamptz `IS NOT DISTINCT FROM` timestamptz	bool
timestamptz `IS NOT DISTINCT FROM` timestamptz	bool
tuple `IS NOT DISTINCT FROM` tuple	bool
unknown `IS NOT DISTINCT FROM` unknown	bool
uuid `IS NOT DISTINCT FROM` uuid	bool
uuid[] `IS NOT DISTINCT FROM` uuid[]	bool
varbit `IS NOT DISTINCT FROM` varbit	bool

`LIKE`	Return
string `LIKE` string	bool

`SIMILAR TO`	Return
string `SIMILAR TO` string	bool

`^`	Return
decimal `^` decimal	decimal
decimal `^` int	decimal
float `^` float	float
int `^` decimal	decimal
int `^` int	int

`\|`	Return
inet `\|` inet	inet
int `\|` int	int
varbit `\|` varbit	varbit

`\|\|`	Return
bool `\|\|` bool[]	bool[]
bool[] `\|\|` bool	bool[]
bool[] `\|\|` bool[]	bool[]
bytes `\|\|` bytes	bytes
bytes `\|\|` bytes[]	bytes[]
bytes[] `\|\|` bytes	bytes[]
bytes[] `\|\|` bytes[]	bytes[]
date `\|\|` date[]	date[]
date[] `\|\|` date	date[]
date[] `\|\|` date[]	date[]
decimal `\|\|` decimal[]	decimal[]
decimal[] `\|\|` decimal	decimal[]
decimal[] `\|\|` decimal[]	decimal[]
float `\|\|` float[]	float[]
float[] `\|\|` float	float[]
float[] `\|\|` float[]	float[]
inet `\|\|` inet[]	inet[]
inet[] `\|\|` inet	inet[]
inet[] `\|\|` inet[]	inet[]
int `\|\|` int[]	int[]
int[] `\|\|` int	int[]
int[] `\|\|` int[]	int[]
interval `\|\|` interval[]	interval[]
interval[] `\|\|` interval	interval[]
interval[] `\|\|` interval[]	interval[]
jsonb `\|\|` jsonb	jsonb
oid `\|\|` oid	oid
string `\|\|` string	string
string `\|\|` string[]	string[]
string[] `\|\|` string	string[]
string[] `\|\|` string[]	string[]
time `\|\|` time[]	time[]
time[] `\|\|` time	time[]
time[] `\|\|` time[]	time[]
timestamp `\|\|` timestamp[]	timestamp[]
timestamp[] `\|\|` timestamp	timestamp[]
timestamp[] `\|\|` timestamp[]	timestamp[]
timestamptz `\|\|` timestamptz	timestamptz
timestamptz `\|\|` timestamptz	timestamptz
timestamptz `\|\|` timestamptz	timestamptz
uuid `\|\|` uuid[]	uuid[]
uuid[] `\|\|` uuid	uuid[]
uuid[] `\|\|` uuid[]	uuid[]
varbit `\|\|` varbit	varbit

`~`	Return
`~`inet	inet
`~`int	int
`~`varbit	varbit
string `~` string	bool

`~*`	Return
string `~*` string	bool

Was this helpful?

Cockroach
University

Docs Hub

Functions and Operators

Special syntax forms

Conditional and function-like operators

Built-in functions

Array functions

BOOL functions

Comparison functions

Date and time functions

ID generation functions

INET functions

JSONB functions

Math and numeric functions

Sequence functions

Set-returning functions

String and byte functions

System info functions

TUPLE functions

Compatibility functions

Aggregate functions

Window functions

Operators

Supported operations

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Functions and Operators

Special syntax forms

Conditional and function-like operators

Built-in functions

Array functions

BOOL functions

Comparison functions

Date and time functions

ID generation functions

INET functions

JSONB functions

Math and numeric functions

Sequence functions

Set-returning functions

String and byte functions

System info functions

TUPLE functions

Compatibility functions

Aggregate functions

Window functions

Operators

Supported operations

Cockroach
University

Cockroach
University