Functions and Operators

On this page Carat arrow pointing down

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
CURRENT_CATALOG current_catalog()
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

ANYELEMENT Functions

Function → ReturnsDescription
to_json(val: anyelement) → jsonb

Returns the value as JSON or JSONB.

to_jsonb(val: anyelement) → jsonb

Returns the value as JSON or JSONB.

Array Functions

Function → ReturnsDescription
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_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_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_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_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_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_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_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 → ReturnsDescription
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_contained_by(val: inet, val: inet) → bool

Test for subnet inclusion, 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.

Comparison Functions

Function → ReturnsDescription
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 → ReturnsDescription
age(begin: timestamptz, end: timestamptz) → interval

Calculates the interval between begin and end.

age(val: timestamptz) → interval

Calculates the interval between the current time and val.

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() → 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: year, quarter, month, week, 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: year, quarter, month, week, 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: year, quarter, month, week, hour, minute, second, millisecond, microsecond.

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: year, quarter, month, week, dayofweek, dayofyear, 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: year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch

extract(element: string, input: timestamptz) → int

Extracts element from input.

Compatible elements: year, quarter, month, week, dayofweek, dayofyear, 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() → 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.

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 → ReturnsDescription
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.

uuid_v4() → bytes

Returns a UUID.

INET Functions

Function → ReturnsDescription
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 → ReturnsDescription
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_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_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_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.

Math and Numeric Functions

Function → ReturnsDescription
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.

to_hex(val: int) → string

Converts val to its hexadecimal representation.

trunc(val: decimal) → decimal

Truncates the decimal values of val.

trunc(val: float) → float

Truncates the decimal values of val.

STRING[] Functions

Function → ReturnsDescription
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.

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.

Sequence Functions

Function → ReturnsDescription
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.

String and Byte Functions

Function → ReturnsDescription
ascii(val: string) → int

Calculates the ASCII value for the first character in 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.

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.

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

Decodes data as the format specified by format (only “hex” and “escape” are supported).

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

Encodes data in the text format specified by format (only “hex” and “escape” are supported).

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.

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.

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
i Global matching (match each substring instead of only the first).
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.

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, end_pos: int) → string

Returns a substring of input between start_pos and end_pos (count starts at 1).

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

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

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, end_pos: int) → string

Returns a substring of input between start_pos and end_pos (count starts at 1).

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

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

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_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 → ReturnsDescription
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.cluster_id() → uuid

Returns the cluster ID.

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.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.set_vmodule(vmodule_string: string) → int

This function is used for internal debugging purposes. Incorrect use can severely impact 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.

Compatibility Functions

Function → ReturnsDescription
crdb_internal.unary_table() → setof tuple{}

Produces a virtual table containing a single row with no values.

This function is used only by CockroachDB’s developers for testing purposes.

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.

generate_series(start: int, end: int) → setof tuple{int}

Produces a virtual table containing the integer values from start to end, inclusive.

generate_series(start: int, end: int, step: int) → setof tuple{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) → setof tuple{timestamp}

Produces a virtual table containing the timestamp values from start to end, inclusive, by increment of step.

has_any_column_privilege(table: string, privilege: string) → bool

does current user have privilege for any column of table

has_any_column_privilege(table: oid, privilege: string) → bool

does current user have privilege for any column of table

has_any_column_privilege(user: string, table: string, privilege: string) → bool

does user have privilege for any column of table

has_any_column_privilege(user: string, table: oid, privilege: string) → bool

does user have privilege for any column of table

has_any_column_privilege(user: oid, table: string, privilege: string) → bool

does user have privilege for any column of table

has_any_column_privilege(user: oid, table: oid, privilege: string) → bool

does user have privilege for any column of table

has_column_privilege(table: string, column: int, privilege: string) → bool

does current user have privilege for column

has_column_privilege(table: string, column: string, privilege: string) → bool

does current user have privilege for column

has_column_privilege(table: oid, column: int, privilege: string) → bool

does current user have privilege for column

has_column_privilege(table: oid, column: string, privilege: string) → bool

does current user have privilege for column

has_column_privilege(user: string, table: string, column: int, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: string, table: string, column: string, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: string, table: oid, column: int, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: string, table: oid, column: string, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: oid, table: string, column: int, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: oid, table: string, column: string, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: oid, table: oid, column: int, privilege: string) → bool

does user have privilege for column

has_column_privilege(user: oid, table: oid, column: string, privilege: string) → bool

does user have privilege for column

has_database_privilege(database: string, privilege: string) → bool

does current user have privilege for database

has_database_privilege(database: oid, privilege: string) → bool

does current user have privilege for database

has_database_privilege(user: string, database: string, privilege: string) → bool

does user have privilege for database

has_database_privilege(user: string, database: oid, privilege: string) → bool

does user have privilege for database

has_database_privilege(user: oid, database: string, privilege: string) → bool

does user have privilege for database

has_database_privilege(user: oid, database: oid, privilege: string) → bool

does user have privilege for database

has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool

does current user have privilege for foreign-data wrapper

has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool

does current user have privilege for foreign-data wrapper

has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool

does user have privilege for foreign-data wrapper

has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool

does user have privilege for foreign-data wrapper

has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool

does user have privilege for foreign-data wrapper

has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool

does user have privilege for foreign-data wrapper

has_function_privilege(function: string, privilege: string) → bool

does current user have privilege for function

has_function_privilege(function: oid, privilege: string) → bool

does current user have privilege for function

has_function_privilege(user: string, function: string, privilege: string) → bool

does user have privilege for function

has_function_privilege(user: string, function: oid, privilege: string) → bool

does user have privilege for function

has_function_privilege(user: oid, function: string, privilege: string) → bool

does user have privilege for function

has_function_privilege(user: oid, function: oid, privilege: string) → bool

does user have privilege for function

has_language_privilege(language: string, privilege: string) → bool

does current user have privilege for language

has_language_privilege(language: oid, privilege: string) → bool

does current user have privilege for language

has_language_privilege(user: string, language: string, privilege: string) → bool

does user have privilege for language

has_language_privilege(user: string, language: oid, privilege: string) → bool

does user have privilege for language

has_language_privilege(user: oid, language: string, privilege: string) → bool

does user have privilege for language

has_language_privilege(user: oid, language: oid, privilege: string) → bool

does user have privilege for language

has_schema_privilege(schema: string, privilege: string) → bool

does current user have privilege for schema

has_schema_privilege(schema: oid, privilege: string) → bool

does current user have privilege for schema

has_schema_privilege(user: string, schema: string, privilege: string) → bool

does user have privilege for schema

has_schema_privilege(user: string, schema: oid, privilege: string) → bool

does user have privilege for schema

has_schema_privilege(user: oid, schema: string, privilege: string) → bool

does user have privilege for schema

has_schema_privilege(user: oid, schema: oid, privilege: string) → bool

does user have privilege for schema

has_sequence_privilege(sequence: string, privilege: string) → bool

does current user have privilege for sequence

has_sequence_privilege(sequence: oid, privilege: string) → bool

does current user have privilege for sequence

has_sequence_privilege(user: string, sequence: string, privilege: string) → bool

does user have privilege for sequence

has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool

does user have privilege for sequence

has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool

does user have privilege for sequence

has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool

does user have privilege for sequence

has_server_privilege(server: string, privilege: string) → bool

does current user have privilege for foreign server

has_server_privilege(server: oid, privilege: string) → bool

does current user have privilege for foreign server

has_server_privilege(user: string, server: string, privilege: string) → bool

does user have privilege for foreign server

has_server_privilege(user: string, server: oid, privilege: string) → bool

does user have privilege for foreign server

has_server_privilege(user: oid, server: string, privilege: string) → bool

does user have privilege for foreign server

has_server_privilege(user: oid, server: oid, privilege: string) → bool

does user have privilege for foreign server

has_table_privilege(table: string, privilege: string) → bool

does current user have privilege for table

has_table_privilege(table: oid, privilege: string) → bool

does current user have privilege for table

has_table_privilege(user: string, table: string, privilege: string) → bool

does user have privilege for table

has_table_privilege(user: string, table: oid, privilege: string) → bool

does user have privilege for table

has_table_privilege(user: oid, table: string, privilege: string) → bool

does user have privilege for table

has_table_privilege(user: oid, table: oid, privilege: string) → bool

does user have privilege for table

has_tablespace_privilege(tablespace: string, privilege: string) → bool

does current user have privilege for tablespace

has_tablespace_privilege(tablespace: oid, privilege: string) → bool

does current user have privilege for tablespace

has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool

does user have privilege for tablespace

has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool

does user have privilege for tablespace

has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool

does user have privilege for tablespace

has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool

does user have privilege for tablespace

has_type_privilege(type: string, privilege: string) → bool

does current user have privilege for type

has_type_privilege(type: oid, privilege: string) → bool

does current user have privilege for type

has_type_privilege(user: string, type: string, privilege: string) → bool

does user have privilege for type

has_type_privilege(user: string, type: oid, privilege: string) → bool

does user have privilege for type

has_type_privilege(user: oid, type: string, privilege: string) → bool

does user have privilege for type

has_type_privilege(user: oid, type: oid, privilege: string) → bool

does user have privilege for type

json_array_elements(input: jsonb) → setof tuple{jsonb}

Expands a JSON array to a set of JSON values.

json_array_elements_text(input: jsonb) → setof tuple{string}

Expands a JSON array to a set of text values.

json_each(input: jsonb) → setof tuple{string, jsonb}

Expands the outermost JSON or JSONB object into a set of key/value pairs.

json_each_text(input: jsonb) → setof tuple{string, string}

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) → setof tuple{string}

Returns sorted set of keys in the outermost JSON object.

jsonb_array_elements(input: jsonb) → setof tuple{jsonb}

Expands a JSON array to a set of JSON values.

jsonb_array_elements_text(input: jsonb) → setof tuple{string}

Expands a JSON array to a set of text values.

jsonb_each(input: jsonb) → setof tuple{string, jsonb}

Expands the outermost JSON or JSONB object into a set of key/value pairs.

jsonb_each_text(input: jsonb) → setof tuple{string, string}

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) → setof tuple{string}

Returns sorted set of keys in the outermost JSON object.

oid(int: int) → oid

Converts an integer to an OID.

pg_get_keywords() → setof tuple{string, string, string}

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

Aggregate Functions

Function → ReturnsDescription
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.

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.

bool_and(arg1: bool) → bool

Calculates the boolean value of ANDing all selected values.

bool_or(arg1: bool) → bool

Calculates the boolean value of ORing 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.

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.

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.

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.

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 # intint
#>Return
jsonb #> string[]jsonb
#>>Return
jsonb #>> string[]string
%Return
decimal % decimaldecimal
decimal % intdecimal
float % floatfloat
int % decimaldecimal
int % intint
&Return
inet & inetinet
int & intint
*Return
decimal * decimaldecimal
decimal * intdecimal
decimal * intervalinterval
float * floatfloat
float * intervalinterval
int * decimaldecimal
int * intint
int * intervalinterval
interval * decimalinterval
interval * floatinterval
interval * intinterval
+Return
+decimaldecimal
+floatfloat
+intint
date + intdate
date + intervaltimestamptz
date + timetimestamp
decimal + decimaldecimal
decimal + intdecimal
float + floatfloat
inet + intinet
int + datedate
int + decimaldecimal
int + inetinet
int + intint
interval + datetimestamptz
interval + intervalinterval
interval + timetime
interval + timestamptimestamp
interval + timestamptztimestamptz
time + datetimestamp
time + intervaltime
timestamp + intervaltimestamp
timestamptz + intervaltimestamptz
-Return
-decimaldecimal
-floatfloat
-intint
-intervalinterval
date - dateint
date - intdate
date - intervaltimestamptz
date - timetimestamp
decimal - decimaldecimal
decimal - intdecimal
float - floatfloat
inet - inetint
inet - intinet
int - decimaldecimal
int - intint
interval - intervalinterval
jsonb - intjsonb
jsonb - stringjsonb
time - intervaltime
time - timeinterval
timestamp - intervaltimestamp
timestamp - timestampinterval
timestamp - timestamptzinterval
timestamptz - intervaltimestamptz
timestamptz - timestampinterval
timestamptz - timestamptzinterval
->Return
jsonb -> intjsonb
jsonb -> stringjsonb
->>Return
jsonb ->> intstring
jsonb ->> stringstring
/Return
decimal / decimaldecimal
decimal / intdecimal
float / floatfloat
int / decimaldecimal
int / intdecimal
interval / floatinterval
interval / intinterval
//Return
decimal // decimaldecimal
decimal // intdecimal
float // floatfloat
int // decimaldecimal
int // intint
<Return
bool < boolbool
bytes < bytesbool
collatedstring < collatedstringbool
date < datebool
date < timestampbool
date < timestamptzbool
decimal < decimalbool
decimal < floatbool
decimal < intbool
float < decimalbool
float < floatbool
float < intbool
inet < inetbool
int < decimalbool
int < floatbool
int < intbool
interval < intervalbool
oid < oidbool
string < stringbool
time < timebool
timestamp < datebool
timestamp < timestampbool
timestamp < timestamptzbool
timestamptz < datebool
timestamptz < timestampbool
timestamptz < timestamptzbool
tuple < tuplebool
uuid < uuidbool
<<Return
inet << inetbool
int << intint
<=Return
bool <= boolbool
bytes <= bytesbool
collatedstring <= collatedstringbool
date <= datebool
date <= timestampbool
date <= timestamptzbool
decimal <= decimalbool
decimal <= floatbool
decimal <= intbool
float <= decimalbool
float <= floatbool
float <= intbool
inet <= inetbool
int <= decimalbool
int <= floatbool
int <= intbool
interval <= intervalbool
oid <= oidbool
string <= stringbool
time <= timebool
timestamp <= datebool
timestamp <= timestampbool
timestamp <= timestamptzbool
timestamptz <= datebool
timestamptz <= timestampbool
timestamptz <= timestamptzbool
tuple <= tuplebool
uuid <= uuidbool
<@Return
jsonb <@ jsonbbool
=Return
bool = boolbool
bool[] = bool[]bool
bytes = bytesbool
bytes[] = bytes[]bool
collatedstring = collatedstringbool
date = datebool
date = timestampbool
date = timestamptzbool
date[] = date[]bool
decimal = decimalbool
decimal = floatbool
decimal = intbool
decimal[] = decimal[]bool
float = decimalbool
float = floatbool
float = intbool
float[] = float[]bool
inet = inetbool
inet[] = inet[]bool
int = decimalbool
int = floatbool
int = intbool
int[] = int[]bool
interval = intervalbool
interval[] = interval[]bool
jsonb = jsonbbool
oid = oidbool
string = stringbool
string[] = string[]bool
time = timebool
time[] = time[]bool
timestamp = datebool
timestamp = timestampbool
timestamp = timestamptzbool
timestamp[] = timestamp[]bool
timestamptz = datebool
timestamptz = timestampbool
timestamptz = timestamptzbool
timestamptz = timestamptzbool
tuple = tuplebool
uuid = uuidbool
uuid[] = uuid[]bool
>>Return
inet >> inetbool
int >> intint
?Return
jsonb ? stringbool
?&Return
jsonb ?& string[]bool
?|Return
jsonb ?| string[]bool
@>Return
jsonb @> jsonbbool
ILIKEReturn
string ILIKE stringbool
INReturn
bool IN tuplebool
bytes IN tuplebool
collatedstring IN tuplebool
date IN tuplebool
decimal IN tuplebool
float IN tuplebool
inet IN tuplebool
int IN tuplebool
interval IN tuplebool
jsonb IN tuplebool
oid IN tuplebool
string IN tuplebool
time IN tuplebool
timestamp IN tuplebool
timestamptz IN tuplebool
tuple IN tuplebool
uuid IN tuplebool
IS NOT DISTINCT FROMReturn
bool IS NOT DISTINCT FROM boolbool
bool[] IS NOT DISTINCT FROM bool[]bool
bytes IS NOT DISTINCT FROM bytesbool
bytes[] IS NOT DISTINCT FROM bytes[]bool
collatedstring IS NOT DISTINCT FROM collatedstringbool
date IS NOT DISTINCT FROM datebool
date IS NOT DISTINCT FROM timestampbool
date IS NOT DISTINCT FROM timestamptzbool
date[] IS NOT DISTINCT FROM date[]bool
decimal IS NOT DISTINCT FROM decimalbool
decimal IS NOT DISTINCT FROM floatbool
decimal IS NOT DISTINCT FROM intbool
decimal[] IS NOT DISTINCT FROM decimal[]bool
float IS NOT DISTINCT FROM decimalbool
float IS NOT DISTINCT FROM floatbool
float IS NOT DISTINCT FROM intbool
float[] IS NOT DISTINCT FROM float[]bool
inet IS NOT DISTINCT FROM inetbool
inet[] IS NOT DISTINCT FROM inet[]bool
int IS NOT DISTINCT FROM decimalbool
int IS NOT DISTINCT FROM floatbool
int IS NOT DISTINCT FROM intbool
int[] IS NOT DISTINCT FROM int[]bool
interval IS NOT DISTINCT FROM intervalbool
interval[] IS NOT DISTINCT FROM interval[]bool
jsonb IS NOT DISTINCT FROM jsonbbool
oid IS NOT DISTINCT FROM oidbool
string IS NOT DISTINCT FROM stringbool
string[] IS NOT DISTINCT FROM string[]bool
time IS NOT DISTINCT FROM timebool
time[] IS NOT DISTINCT FROM time[]bool
timestamp IS NOT DISTINCT FROM datebool
timestamp IS NOT DISTINCT FROM timestampbool
timestamp IS NOT DISTINCT FROM timestamptzbool
timestamp[] IS NOT DISTINCT FROM timestamp[]bool
timestamptz IS NOT DISTINCT FROM datebool
timestamptz IS NOT DISTINCT FROM timestampbool
timestamptz IS NOT DISTINCT FROM timestamptzbool
timestamptz IS NOT DISTINCT FROM timestamptzbool
tuple IS NOT DISTINCT FROM tuplebool
unknown IS NOT DISTINCT FROM unknownbool
uuid IS NOT DISTINCT FROM uuidbool
uuid[] IS NOT DISTINCT FROM uuid[]bool
LIKEReturn
string LIKE stringbool
SIMILAR TOReturn
string SIMILAR TO stringbool
^Return
decimal ^ decimaldecimal
decimal ^ intdecimal
float ^ floatfloat
int ^ decimaldecimal
int ^ intint
|Return
inet | inetinet
int | intint
||Return
bool || bool[]bool[]
bool[] || boolbool[]
bool[] || bool[]bool[]
bytes || bytesbytes
bytes || bytes[]bytes[]
bytes[] || bytesbytes[]
bytes[] || bytes[]bytes[]
date || date[]date[]
date[] || datedate[]
date[] || date[]date[]
decimal || decimal[]decimal[]
decimal[] || decimaldecimal[]
decimal[] || decimal[]decimal[]
float || float[]float[]
float[] || floatfloat[]
float[] || float[]float[]
inet || inet[]inet[]
inet[] || inetinet[]
inet[] || inet[]inet[]
int || int[]int[]
int[] || intint[]
int[] || int[]int[]
interval || interval[]interval[]
interval[] || intervalinterval[]
interval[] || interval[]interval[]
jsonb || jsonbjsonb
oid || oidoid
string || stringstring
string || string[]string[]
string[] || stringstring[]
string[] || string[]string[]
time || time[]time[]
time[] || timetime[]
time[] || time[]time[]
timestamp || timestamp[]timestamp[]
timestamp[] || timestamptimestamp[]
timestamp[] || timestamp[]timestamp[]
timestamptz || timestamptztimestamptz
timestamptz || timestamptztimestamptz
timestamptz || timestamptztimestamptz
uuid || uuid[]uuid[]
uuid[] || uuiduuid[]
uuid[] || uuid[]uuid[]
~Return
~inetinet
~intint
string ~ stringbool
~*Return
string ~* stringbool

Yes No
On this page

Yes No