CockroachDB supports the following SQL functions and operators.
Built-in Functions
Array Functions
Function → Returns | Description |
---|---|
array_append(array: bool[], elem: bool) → bool[] | Appends |
array_append(array: bytes[], elem: bytes) → bytes[] | Appends |
array_append(array: date[], elem: date) → date[] | Appends |
array_append(array: decimal[], elem: decimal) → decimal[] | Appends |
array_append(array: float[], elem: float) → float[] | Appends |
array_append(array: int[], elem: int) → int[] | Appends |
array_append(array: interval[], elem: interval) → interval[] | Appends |
array_append(array: string[], elem: string) → string[] | Appends |
array_append(array: timestamp[], elem: timestamp) → timestamp[] | Appends |
array_append(array: timestamptz[], elem: timestamptz) → timestamptz[] | Appends |
array_append(array: uuid[], elem: uuid) → uuid[] | Appends |
array_append(array: oid[], elem: oid) → oid[] | Appends |
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: 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: 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 |
array_lower(input: anyelement[], array_dimension: int) → int | Calculates the minimum value of |
array_position(array: bool[], elem: bool) → int | Return the index of the first occurrence of |
array_position(array: bytes[], elem: bytes) → int | Return the index of the first occurrence of |
array_position(array: date[], elem: date) → int | Return the index of the first occurrence of |
array_position(array: decimal[], elem: decimal) → int | Return the index of the first occurrence of |
array_position(array: float[], elem: float) → int | Return the index of the first occurrence of |
array_position(array: int[], elem: int) → int | Return the index of the first occurrence of |
array_position(array: interval[], elem: interval) → int | Return the index of the first occurrence of |
array_position(array: string[], elem: string) → int | Return the index of the first occurrence of |
array_position(array: timestamp[], elem: timestamp) → int | Return the index of the first occurrence of |
array_position(array: timestamptz[], elem: timestamptz) → int | Return the index of the first occurrence of |
array_position(array: uuid[], elem: uuid) → int | Return the index of the first occurrence of |
array_position(array: oid[], elem: oid) → int | Return the index of the first occurrence of |
array_positions(array: bool[], elem: bool) → bool[] | Returns and array of indexes of all occurrences of |
array_positions(array: bytes[], elem: bytes) → bytes[] | Returns and array of indexes of all occurrences of |
array_positions(array: date[], elem: date) → date[] | Returns and array of indexes of all occurrences of |
array_positions(array: decimal[], elem: decimal) → decimal[] | Returns and array of indexes of all occurrences of |
array_positions(array: float[], elem: float) → float[] | Returns and array of indexes of all occurrences of |
array_positions(array: int[], elem: int) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: interval[], elem: interval) → interval[] | Returns and array of indexes of all occurrences of |
array_positions(array: string[], elem: string) → string[] | Returns and array of indexes of all occurrences of |
array_positions(array: timestamp[], elem: timestamp) → timestamp[] | Returns and array of indexes of all occurrences of |
array_positions(array: timestamptz[], elem: timestamptz) → timestamptz[] | Returns and array of indexes of all occurrences of |
array_positions(array: uuid[], elem: uuid) → uuid[] | Returns and array of indexes of all occurrences of |
array_positions(array: oid[], elem: oid) → oid[] | Returns and array of indexes of all occurrences of |
array_prepend(elem: bool, array: bool[]) → bool[] | Prepends |
array_prepend(elem: bytes, array: bytes[]) → bytes[] | Prepends |
array_prepend(elem: date, array: date[]) → date[] | Prepends |
array_prepend(elem: decimal, array: decimal[]) → decimal[] | Prepends |
array_prepend(elem: float, array: float[]) → float[] | Prepends |
array_prepend(elem: int, array: int[]) → int[] | Prepends |
array_prepend(elem: interval, array: interval[]) → interval[] | Prepends |
array_prepend(elem: string, array: string[]) → string[] | Prepends |
array_prepend(elem: timestamp, array: timestamp[]) → timestamp[] | Prepends |
array_prepend(elem: timestamptz, array: timestamptz[]) → timestamptz[] | Prepends |
array_prepend(elem: uuid, array: uuid[]) → uuid[] | Prepends |
array_prepend(elem: oid, array: oid[]) → oid[] | Prepends |
array_remove(array: bool[], elem: bool) → bool[] | Remove from |
array_remove(array: bytes[], elem: bytes) → bytes[] | Remove from |
array_remove(array: date[], elem: date) → date[] | Remove from |
array_remove(array: decimal[], elem: decimal) → decimal[] | Remove from |
array_remove(array: float[], elem: float) → float[] | Remove from |
array_remove(array: int[], elem: int) → int[] | Remove from |
array_remove(array: interval[], elem: interval) → interval[] | Remove from |
array_remove(array: string[], elem: string) → string[] | Remove from |
array_remove(array: timestamp[], elem: timestamp) → timestamp[] | Remove from |
array_remove(array: timestamptz[], elem: timestamptz) → timestamptz[] | Remove from |
array_remove(array: uuid[], elem: uuid) → uuid[] | Remove from |
array_remove(array: oid[], elem: oid) → oid[] | Remove from |
array_replace(array: bool[], toreplace: bool, replacewith: bool) → bool[] | Replace all occurrences of |
array_replace(array: bytes[], toreplace: bytes, replacewith: bytes) → bytes[] | Replace all occurrences of |
array_replace(array: date[], toreplace: date, replacewith: date) → date[] | Replace all occurrences of |
array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) → decimal[] | Replace all occurrences of |
array_replace(array: float[], toreplace: float, replacewith: float) → float[] | Replace all occurrences of |
array_replace(array: int[], toreplace: int, replacewith: int) → int[] | Replace all occurrences of |
array_replace(array: interval[], toreplace: interval, replacewith: interval) → interval[] | Replace all occurrences of |
array_replace(array: string[], toreplace: string, replacewith: string) → string[] | Replace all occurrences of |
array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp) → timestamp[] | Replace all occurrences of |
array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) → timestamptz[] | Replace all occurrences of |
array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) → uuid[] | Replace all occurrences of |
array_replace(array: oid[], toreplace: oid, replacewith: oid) → oid[] | Replace all occurrences of |
array_upper(input: anyelement[], array_dimension: int) → int | Calculates the maximum value of |
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(begin: timestamptz, end: timestamptz) → interval | Calculates the interval between |
age(val: timestamptz) → interval | Calculates the interval between |
clock_timestamp() → timestamp | Returns the current wallclock time. |
clock_timestamp() → timestamptz | Returns the current wallclock time. |
current_date() → date | Returns the current date. |
current_timestamp() → timestamp | Returns the current transaction’s timestamp. |
current_timestamp() → timestamptz | Returns the current transaction’s timestamp. |
experimental_strftime(input: date, extract_format: string) → string | From |
experimental_strftime(input: timestamp, extract_format: string) → string | From |
experimental_strftime(input: timestamptz, extract_format: string) → string | From |
experimental_strptime(input: string, format: string) → timestamptz | Returns |
extract(element: string, input: date) → int | Extracts Compatible elements: year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch |
extract(element: string, input: timestamp) → int | Extracts Compatible elements: year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch |
extract_duration(element: string, input: interval) → int | Extracts |
now() → timestamp | Returns the current transaction’s timestamp. |
now() → timestamptz | Returns the current transaction’s timestamp. |
statement_timestamp() → timestamp | Returns the current statement’s timestamp. |
statement_timestamp() → timestamptz | Returns the current statement’s timestamp. |
transaction_timestamp() → timestamp | Returns the current transaction’s timestamp. |
transaction_timestamp() → timestamptz | Returns the current transaction’s timestamp. |
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. |
uuid_v4() → bytes |
Returns a UUID. |
Math and Numeric Functions
Function → Returns | Description |
---|---|
abs(val: decimal) → decimal | Calculates the absolute value of |
abs(val: float) → float | Calculates the absolute value of |
abs(val: int) → int | Calculates the absolute value of |
acos(val: float) → float | Calculates the inverse cosine of |
asin(val: float) → float | Calculates the inverse sine of |
atan(val: float) → float | Calculates the inverse tangent of |
atan2(x: float, y: float) → float | Calculates the inverse tangent of |
cbrt(val: decimal) → decimal | Calculates the cube root (∛) of |
cbrt(val: float) → float | Calculates the cube root (∛) of |
ceil(val: decimal) → decimal | Calculates the smallest integer greater than |
ceil(val: float) → float | Calculates the smallest integer greater than |
ceiling(val: decimal) → decimal | Calculates the smallest integer greater than |
ceiling(val: float) → float | Calculates the smallest integer greater than |
cos(val: float) → float | Calculates the cosine of |
cot(val: float) → float | Calculates the cotangent of |
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 |
div(x: decimal, y: decimal) → decimal | Calculates the integer quotient of |
div(x: float, y: float) → float | Calculates the integer quotient of |
div(x: int, y: int) → int | Calculates the integer quotient of |
exp(val: decimal) → decimal | Calculates e ^ |
exp(val: float) → float | Calculates e ^ |
floor(val: decimal) → decimal | Calculates the largest integer not greater than |
floor(val: float) → float | Calculates the largest integer not greater than |
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 |
isnan(val: float) → bool | Returns true if |
ln(val: decimal) → decimal | Calculates the natural log of |
ln(val: float) → float | Calculates the natural log of |
log(val: decimal) → decimal | Calculates the base 10 log of |
log(val: float) → float | Calculates the base 10 log of |
mod(x: decimal, y: decimal) → decimal | Calculates |
mod(x: float, y: float) → float | Calculates |
mod(x: int, y: int) → int | Calculates |
pi() → float | Returns the value for pi (3.141592653589793). |
pow(x: decimal, y: decimal) → decimal | Calculates |
pow(x: float, y: float) → float | Calculates |
pow(x: int, y: int) → int | Calculates |
power(x: decimal, y: decimal) → decimal | Calculates |
power(x: float, y: float) → float | Calculates |
power(x: int, y: int) → int | Calculates |
radians(val: float) → float | Converts |
random() → float | Returns a random float between 0 and 1. |
round(input: decimal, decimal_accuracy: int) → decimal | Keeps |
round(input: float, decimal_accuracy: int) → float | Keeps |
round(val: decimal) → decimal | Rounds |
round(val: float) → float | Rounds |
sign(val: decimal) → decimal | Determines the sign of |
sign(val: float) → float | Determines the sign of |
sign(val: int) → int | Determines the sign of |
sin(val: float) → float | Calculates the sine of |
sqrt(val: decimal) → decimal | Calculates the square root of |
sqrt(val: float) → float | Calculates the square root of |
tan(val: float) → float | Calculates the tangent of |
to_hex(val: int) → string | Converts |
trunc(val: decimal) → decimal | Truncates the decimal values of |
trunc(val: float) → float | Truncates the decimal values of |
String and Byte Functions
Function → Returns | Description | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ascii(val: string) → int | Calculates the ASCII value for the first character in | |||||||||||||||||||||||||||||
btrim(input: string, trim_chars: string) → string | Removes any characters included in For example, | |||||||||||||||||||||||||||||
btrim(val: string) → string | Removes all spaces from the beginning and end of | |||||||||||||||||||||||||||||
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 | |||||||||||||||||||||||||||||
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 | |||||||||||||||||||||||||||||
left(input: bytes, return_set: int) → bytes | Returns the first | |||||||||||||||||||||||||||||
left(input: string, return_set: int) → string | Returns the first | |||||||||||||||||||||||||||||
length(val: bytes) → int | Calculates the number of bytes in | |||||||||||||||||||||||||||||
length(val: string) → int | Calculates the number of characters in | |||||||||||||||||||||||||||||
lower(val: string) → string | Converts all characters in | |||||||||||||||||||||||||||||
ltrim(input: string, trim_chars: string) → string | Removes any characters included in For example, | |||||||||||||||||||||||||||||
ltrim(val: string) → string | Removes all spaces from the beginning (left-hand side) of | |||||||||||||||||||||||||||||
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 | |||||||||||||||||||||||||||||
octet_length(val: string) → int | Calculates the number of bytes used to represent | |||||||||||||||||||||||||||||
overlay(input: string, overlay_val: string, start_pos: int) → string | Replaces characters in For example, | |||||||||||||||||||||||||||||
overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string | Deletes the characters in | |||||||||||||||||||||||||||||
regexp_extract(input: string, regex: string) → string | Returns the first match for the Regular Expression | |||||||||||||||||||||||||||||
regexp_replace(input: string, regex: string, replace: string) → string | Replaces matches for the Regular Expression | |||||||||||||||||||||||||||||
regexp_replace(input: string, regex: string, replace: string, flags: string) → string | Replaces matches for the regular expression CockroachDB supports the following flags:
| |||||||||||||||||||||||||||||
repeat(input: string, repeat_counter: int) → string | Concatenates For example, | |||||||||||||||||||||||||||||
replace(input: string, find: string, replace: string) → string | Replaces all occurrences of | |||||||||||||||||||||||||||||
reverse(val: string) → string | Reverses the order of the string’s characters. | |||||||||||||||||||||||||||||
right(input: bytes, return_set: int) → bytes | Returns the last | |||||||||||||||||||||||||||||
right(input: string, return_set: int) → string | Returns the last | |||||||||||||||||||||||||||||
rtrim(input: string, trim_chars: string) → string | Removes any characters included in For example, | |||||||||||||||||||||||||||||
rtrim(val: string) → string | Removes all spaces from the end (right-hand side) of | |||||||||||||||||||||||||||||
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 For example, | |||||||||||||||||||||||||||||
strpos(input: string, find: string) → int | Calculates the position where the string For example, | |||||||||||||||||||||||||||||
substr(input: string, regex: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substr(input: string, regex: string, escape_char: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substr(input: string, start_pos: int, end_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
substr(input: string, substr_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, regex: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, regex: string, escape_char: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, start_pos: int, end_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, substr_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
to_english(val: int) → string | This function enunciates the value of its argument using English cardinals. | |||||||||||||||||||||||||||||
to_hex(val: bytes) → string | Converts | |||||||||||||||||||||||||||||
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 For example, | |||||||||||||||||||||||||||||
upper(val: string) → string | Converts all characters in |
System Info Functions
Function → Returns | Description |
---|---|
cluster_logical_timestamp() → decimal | This function is used only by CockroachDB’s developers for testing purposes. |
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.force_retry(val: interval, txnID: string) → 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.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. This function is provided for compatibility with PostgreSQL. For a new CockroachDB application, consider using current_database() instead. |
current_schemas(include_pg_catalog: bool) → string[] | Returns the current search path for unqualified names. |
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 → 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. |
generate_series(start: int, end: int) → setof tuple{int} | Produces a virtual table containing the integer values from |
generate_series(start: int, end: int, step: int) → setof tuple{int} | Produces a virtual table containing the integer values from |
oid(int: int) → oid | Converts an integer to an OID. |
unnest(input: anyelement[]) → anyelement | Returns the input array as a set of rows |
Aggregate Functions
Function → Returns | Description |
---|---|
array_agg(arg: anyelement) → anyelement | Aggregates the selected values into an array. |
avg(arg: decimal) → decimal | Calculates the average of the selected values. |
avg(arg: float) → float | Calculates the average of the selected values. |
avg(arg: int) → decimal | Calculates the average of the selected values. |
bool_and(arg: bool) → bool | Calculates the boolean value of |
bool_or(arg: bool) → bool | Calculates the boolean value of |
concat_agg(arg: bytes) → bytes | Concatenates all selected values. |
concat_agg(arg: string) → string | Concatenates all selected values. |
count(arg: anyelement) → int | Calculates the number of selected elements. |
count_rows() → int | Calculates the number of rows. |
max(arg: bool) → bool | Identifies the maximum selected value. |
max(arg: bytes) → bytes | Identifies the maximum selected value. |
max(arg: date) → date | Identifies the maximum selected value. |
max(arg: decimal) → decimal | Identifies the maximum selected value. |
max(arg: float) → float | Identifies the maximum selected value. |
max(arg: int) → int | Identifies the maximum selected value. |
max(arg: interval) → interval | Identifies the maximum selected value. |
max(arg: string) → string | Identifies the maximum selected value. |
max(arg: timestamp) → timestamp | Identifies the maximum selected value. |
max(arg: timestamptz) → timestamptz | Identifies the maximum selected value. |
max(arg: uuid) → uuid | Identifies the maximum selected value. |
max(arg: oid) → oid | Identifies the maximum selected value. |
min(arg: bool) → bool | Identifies the minimum selected value. |
min(arg: bytes) → bytes | Identifies the minimum selected value. |
min(arg: date) → date | Identifies the minimum selected value. |
min(arg: decimal) → decimal | Identifies the minimum selected value. |
min(arg: float) → float | Identifies the minimum selected value. |
min(arg: int) → int | Identifies the minimum selected value. |
min(arg: interval) → interval | Identifies the minimum selected value. |
min(arg: string) → string | Identifies the minimum selected value. |
min(arg: timestamp) → timestamp | Identifies the minimum selected value. |
min(arg: timestamptz) → timestamptz | Identifies the minimum selected value. |
min(arg: uuid) → uuid | Identifies the minimum selected value. |
min(arg: oid) → oid | Identifies the minimum selected value. |
stddev(arg: decimal) → decimal | Calculates the standard deviation of the selected values. |
stddev(arg: float) → float | Calculates the standard deviation of the selected values. |
stddev(arg: int) → decimal | Calculates the standard deviation of the selected values. |
sum(arg: decimal) → decimal | Calculates the sum of the selected values. |
sum(arg: float) → float | Calculates the sum of the selected values. |
sum(arg: int) → decimal | Calculates the sum of the selected values. |
sum(arg: interval) → interval | Calculates the sum of the selected values. |
sum_int(arg: int) → int | Calculates the sum of the selected values. |
variance(arg: decimal) → decimal | Calculates the variance of the selected values. |
variance(arg: float) → float | Calculates the variance of the selected values. |
variance(arg: int) → decimal | Calculates the variance of the selected values. |
xor_agg(arg: bytes) → bytes | Calculates the bitwise XOR of the selected values. |
xor_agg(arg: 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 |
~ |
Bitwise not | unary | |
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 |
12 | [NOT] BETWEEN |
Value is [not] within the range specified | 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 |
Value identity | binary |
15 | NOT |
Logical NOT | unary |
16 | AND |
Logical AND | binary |
17 | OR |
Logical OR | binary |
Supported Operations
# | Return |
int # int | int |
% | Return |
decimal % decimal | decimal |
decimal % int | decimal |
float % float | float |
int % decimal | decimal |
int % int | int |
& | Return |
int & int | int |
* | Return |
decimal * decimal | decimal |
decimal * int | decimal |
float * float | float |
int * decimal | decimal |
int * int | int |
int * interval | interval |
interval * float | interval |
interval * int | interval |
/ | 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 |
int < decimal | bool |
int < float | bool |
int < int | bool |
interval < interval | bool |
string < string | 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 |
<< | Return |
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 |
int <= decimal | bool |
int <= float | bool |
int <= int | bool |
interval <= interval | bool |
string <= string | 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 |
= | 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 |
int = decimal | bool |
int = float | bool |
int = int | bool |
int[] = int[] | bool |
interval = interval | bool |
interval[] = interval[] | bool |
oid = oid | bool |
oid = oid | bool |
string = string | bool |
string[] = string[] | 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 |
>> | Return |
int >> int | int |
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 |
int IN tuple | bool |
interval IN tuple | bool |
oid IN tuple | bool |
string IN tuple | bool |
timestamp IN tuple | bool |
timestamptz IN tuple | bool |
tuple IN tuple | bool |
uuid IN tuple | 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 |
int | int | int |
|| | 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[] |
int || int[] | int[] |
int[] || int | int[] |
int[] || int[] | int[] |
interval || interval[] | interval[] |
interval[] || interval | interval[] |
interval[] || interval[] | interval[] |
oid || oid | oid |
oid || oid | oid |
oid || oid | oid |
string || string | string |
string || string[] | string[] |
string[] || string | string[] |
string[] || string[] | string[] |
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[] |
~ | Return |
~ int | int |
string ~ string | bool |
~* | Return |
string ~* string | bool |