Functions and Operators

Warning:

This version of CockroachDB is no longer supported. For more details, see the Release Support Policy.

CockroachDB supports the following SQL functions and operators.

Tip:

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

Built-in Functions

Array Functions

Function → Returns	Description
`array_append(array: bool[], elem: bool) → bool[]`	Appends `elem` to `array`, returning the result.
`array_append(array: bytes[], elem: bytes) → bytes[]`	Appends `elem` to `array`, returning the result.
`array_append(array: date[], elem: date) → date[]`	Appends `elem` to `array`, returning the result.
`array_append(array: decimal[], elem: decimal) → decimal[]`	Appends `elem` to `array`, returning the result.
`array_append(array: float[], elem: float) → float[]`	Appends `elem` to `array`, returning the result.
`array_append(array: 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: 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: 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 `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: 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: 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) → bool[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: bytes[], elem: bytes) → bytes[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: date[], elem: date) → date[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: decimal[], elem: decimal) → decimal[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: float[], elem: float) → float[]`	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) → interval[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: string[], elem: string) → string[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: timestamp[], elem: timestamp) → timestamp[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: timestamptz[], elem: timestamptz) → timestamptz[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: uuid[], elem: uuid) → uuid[]`	Returns and array of indexes of all occurrences of `elem` in `array`.
`array_positions(array: oid[], elem: oid) → oid[]`	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: 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: 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: 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: 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: 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: 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.

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 `begin` and `end`.
`age(val: timestamptz) → interval`	Calculates the interval between `val` and the current time.
`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 `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: timestamp) → 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 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 `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 and Byte Functions

Function → Returns Description

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.

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 → 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 `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`.
`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 `AND`ing all selected values.
`bool_or(arg: bool) → bool`	Calculates the boolean value of `OR`ing all selected values.
`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
`+`float	float
`+`int	int
date `+` int	date
date `+` interval	timestamptz
decimal `+` decimal	decimal
decimal `+` int	decimal
float `+` float	float
int `+` date	date
int `+` decimal	decimal
int `+` int	int
interval `+` date	timestamptz
interval `+` interval	interval
interval `+` timestamp	timestamp
interval `+` timestamptz	timestamptz
timestamp `+` interval	timestamp
timestamptz `+` interval	timestamptz

`-`	Return
`-`decimal	decimal
`-`float	float
`-`int	int
`-`interval	interval
date `-` date	int
date `-` int	date
date `-` interval	timestamptz
decimal `-` decimal	decimal
decimal `-` int	decimal
float `-` float	float
int `-` decimal	decimal
int `-` int	int
interval `-` interval	interval
timestamp `-` interval	timestamp
timestamp `-` timestamp	interval
timestamp `-` timestamptz	interval
timestamptz `-` interval	timestamptz
timestamptz `-` timestamp	interval
timestamptz `-` timestamptz	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

Was this page helpful?

Yes

The most
highly evolved
database
on the planet

CockroachDB

CockroachCloud

CockroachDB

Built-in Functions

Array Functions

Comparison Functions

Date and Time Functions

ID Generation Functions

Math and Numeric Functions

String and Byte Functions

System Info Functions

Compatibility Functions

Aggregate Functions

Operators

Supported Operations

The most highly evolved database on the planet

CockroachDB

CockroachCloud

CockroachDB

Functions and Operators

Built-in Functions

Array Functions

Comparison Functions

Date and Time Functions

ID Generation Functions

Math and Numeric Functions

String and Byte Functions

System Info Functions

Compatibility Functions

Aggregate Functions

Operators

Supported Operations

The most
highly evolved
database
on the planet