Built-in Functions

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.
crdb_internal.force_retry(val: interval) → int This function is used only by CockroachDB’s developers for testing purposes.
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_strptime(format: string, input: 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 are:
• year
• quarter
• month
• week
• dayofweek
• dayofyear
• hour
• minute
• second
• millisecond
• microsecond
• epoch
extract(element: string, input: timestamp) → int Extracts element from input. Compatible elements are:
• 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 are:
• 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.
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.
crdb_internal.force_retry(val: interval, txnID: string) → int This function is used only by CockroachDB’s developers for testing purposes.
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.
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.
round(input: float, decimal_accuracy: int) → float Keeps decimal_accuracy number of figures to the right of the zero position in input.
round(val: decimal) → decimal Rounds val to the nearest integer.
round(val: float) → float Rounds val to the nearest integer.
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.
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).
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 valto 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(val: string) → string Calculates the MD5 hash value of val.
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:

c: Case-sensitive matching

g: Global matching (match each substring instead of only the first).

i: Case-insensitive matching

m or n: Newline-sensitive . and negated brackets ([^...]) do not match newline characters (preventing matching: matches from crossing newlines unless explicitly defined to); ^ and $ match the space before and after newline characters respectively (so characters between newline characters are treated as if they’re on a separate line).

p: Partial newline-sensitive matching: . and negated brackets ([^...]) do not match newline characters (preventing matches from crossing newlines unless explicitly defined to), but ^ and $ still only match the beginning and end of val.

s: Newline-insensitive matching (default).

w: Inverse partial newline-sensitive matching:. and negated brackets ([^...]) do match newline characters, but ^ and $ match the space before and after newline characters respectively (so characters between newline characters are treated as if they’re on a separate line).
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(val: string) → string Calculates the SHA1 hash value of val.
sha256(val: string) → string Calculates the SHA256 hash value of val.
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 \.
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 \.
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_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 valto their to their upper-case equivalents.

System Info Functions

Function → Returns Description
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_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.
cluster_logical_timestamp() → decimal This function is used only by CockroachDB’s developers for testing purposes.
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(unused: bool) → string[] Returns the current search path for unqualified names.
version() → string Returns the node’s version of CockroachDB.

Compatibility Functions

Function → Returns Description
array_in(string: string, element_oid: int, element_typmod: int) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
col_description(table_oid: int, column_number: int) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
format_type(type_oid: int, 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.
obj_description(object_oid: int) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
obj_description(object_oid: int, catalog_name: string) → string Not usable; exposed only for ORM compatibility.
pg_advisory_unlock(int: int) → bool Not usable; exposed only for ORM compatibility.
pg_backend_pid() → int Not usable; exposed only for ORM compatibility with PostgreSQL.
pg_catalog.generate_series(start: int, end: int) → setof tuple{int} Produces a virtual table containing the integer values from start to end, inclusive.
pg_catalog.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.
pg_get_expr(pg_node_tree: string, relation_oid: int) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
pg_get_expr(pg_node_tree: string, relation_oid: int, pretty_bool: bool) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
pg_get_indexdef(index_oid: int) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
pg_get_userbyid(role_oid: int) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
pg_try_advisory_lock(int: int) → bool Not usable; exposed only for ORM compatibility.
pg_typeof(val: anyelement) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
shobj_description(object_oid: int, catalog_name: string) → string Not usable; exposed only for ORM compatibility with PostgreSQL.
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 ANDing all selected values.
bool_or(arg: bool) → bool Calculates the boolean value of ORing 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.
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.
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.
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.

Operators

% 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 * 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 / 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
<< 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
= 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
>> 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
string IN tuple bool
timestamp IN tuple bool
timestamptz IN tuple bool
tuple IN tuple bool
LIKE Return
string LIKE string bool
SIMILAR TO Return
string SIMILAR TO string bool
^ Return
int ^ int int
| Return
int | int int
|| Return
bytes || bytes bytes
string || string string
~ Return
~int int
string ~ string bool
~* Return
string ~* string bool


Yes No