Functions and Operators

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

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

Special syntax forms

The following syntax forms are recognized for compatibility with theSQL standard and PostgreSQL, but are equivalent to regular built-infunctions:

Special formEquivalent to
CURRENT_CATALOGcurrent_catalog()
CURRENT_DATEcurrent_date()
CURRENT_ROLEcurrent_user()
CURRENT_SCHEMAcurrent_schema()
CURRENT_TIMESTAMPcurrent_timestamp()
CURRENT_TIMEcurrent_time()
CURRENT_USERcurrent_user()
EXTRACT(<part> FROM <value>)extract("<part>", <value>)
EXTRACT_DURATION(<part> FROM <value>)extract_duration("<part>", <value>)
OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>)overlay(<text1>, <text2>, <int1>, <int2>)
OVERLAY(<text1> PLACING <text2> FROM <int>)overlay(<text1>, <text2>, <int>)
POSITION(<text1> IN <text2>)strpos(<text2>, <text1>)
SESSION_USERcurrent_user()
SUBSTRING(<text> FOR <int1> FROM <int2>)substring(<text>, <int2>, <int1>)
SUBSTRING(<text> FOR <int>)substring(<text>, 1, <int>)
SUBSTRING(<text> FROM <int1> FOR <int2>)substring(<text>, <int1>, <int2>)
SUBSTRING(<text> FROM <int>)substring(<text>, <int>)
TRIM(<text1> FROM <text2>)btrim(<text2>, <text1>)
TRIM(<text1>, <text2>)btrim(<text1>, <text2>)
TRIM(FROM <text>)btrim(<text>)
TRIM(LEADING <text1> FROM <text2>)ltrim(<text2>, <text1>)
TRIM(LEADING FROM <text>)ltrim(<text>)
TRIM(TRAILING <text1> FROM <text2>)rtrim(<text2>, <text1>)
TRIM(TRAILING FROM <text>)rtrim(<text>)
USERcurrent_user()

Conditional and function-like operators

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

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

Built-in functions

Array functions

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

BOOL functions

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

Comparison functions

Function → ReturnsDescription
greatest(anyelement…) → anyelement
Returns the element with the greatest value.
least(anyelement…) → anyelement
Returns the element with the lowest value.

Date and time functions

Function → ReturnsDescription
age(end: timestamptz, begin: timestamptz) → interval
Calculates the interval between begin and end.
age(val: timestamptz) → interval
Calculates the interval between val and the current time.
clock_timestamp() → timestamp
Returns the current system time on one of the cluster nodes.
clock_timestamp() → timestamptz
Returns the current system time on one of the cluster nodes.
current_date() → date
Returns the date of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.
current_timestamp() → timestamp
Returns the time of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.
current_timestamp() → timestamptz
Returns the time of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.
date_trunc(element: string, input: date) → timestamptz
Truncates input to precision element. Sets all fields that are lesssignificant than element to zero (or one, for day and month)

Compatible elements: year, quarter, month, week, hour, minute, second,millisecond, microsecond.
date_trunc(element: string, input: time) → interval
Truncates input to precision element. Sets all fields that are lesssignificant than element to zero.

Compatible elements: hour, minute, second, millisecond, microsecond.
date_trunc(element: string, input: timestamp) → timestamp
Truncates input to precision element. Sets all fields that are lesssignificant than element to zero (or one, for day and month)

Compatible elements: year, quarter, month, week, hour, minute, second,millisecond, microsecond.
date_trunc(element: string, input: timestamptz) → timestamptz
Truncates input to precision element. Sets all fields that are lesssignificant than element to zero (or one, for day and month)

Compatible elements: year, quarter, month, week, hour, minute, second,millisecond, microsecond.
experimental_follower_read_timestamp() → timestamptz
Returns a timestamp which is very likely to be safe to performagainst a follower replica.

This function is intended to be used with an AS OF SYSTEM TIME clause to performhistorical reads against a time which is recent but sufficiently old for readsto be performed against the closest replica as opposed to the currentlyleaseholder for a given range.

Note that this function requires an enterprise license on a CCL distribution toreturn without an error.
experimental_strftime(input: date, extract_format: string) → string
From input, extracts and formats the time as identified in extract_format using standard strftime notation (though not all formatting is supported).
experimental_strftime(input: timestamp, extract_format: string) → string
From input, extracts and formats the time as identified in extract_format using standard strftime notation (though not all formatting is supported).
experimental_strftime(input: timestamptz, extract_format: string) → string
From input, extracts and formats the time as identified in extract_format using standard strftime notation (though not all formatting is supported).
experimental_strptime(input: string, format: string) → timestamptz
Returns input as a timestamptz using format (which uses standard strptime formatting).
extract(element: string, input: date) → int
Extracts element from input.

Compatible elements: year, quarter, month, week, dayofweek, dayofyear,hour, minute, second, millisecond, microsecond, epoch
extract(element: string, input: time) → int
Extracts element from input.

Compatible elements: hour, minute, second, millisecond, microsecond, epoch
extract(element: string, input: timestamp) → int
Extracts element from input.

Compatible elements: year, quarter, month, week, dayofweek, dayofyear,hour, minute, second, millisecond, microsecond, epoch
extract(element: string, input: timestamptz) → int
Extracts element from input.

Compatible elements: year, quarter, month, week, dayofweek, dayofyear,hour, minute, second, millisecond, microsecond, epoch
extract_duration(element: string, input: interval) → int
Extracts element from input.Compatible elements: hour, minute, second, millisecond, microsecond.
now() → timestamp
Returns the time of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.
now() → timestamptz
Returns the time of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.
statement_timestamp() → timestamp
Returns the start time of the current statement.
statement_timestamp() → timestamptz
Returns the start time of the current statement.
transaction_timestamp() → timestamp
Returns the time of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.
transaction_timestamp() → timestamptz
Returns the time of the current transaction.

The value is based on a timestamp picked when the transaction startsand which stays constant throughout the transaction. This timestamphas no relationship with the commit order of concurrent transactions.

ID generation functions

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

INET functions

Function → ReturnsDescription
abbrev(val: inet) → string
Converts the combined IP address and prefix length to an abbreviated display format as text.For INET types, this will omit the prefix length if it’s not the default (32 or IPv4, 128 for IPv6)

For example, abbrev('192.168.1.2/24') returns '192.168.1.2/24'
broadcast(val: inet) → inet
Gets the broadcast address for the network address represented by the value.

For example, broadcast('192.168.1.2/24') returns '192.168.1.255/24'
family(val: inet) → int
Extracts the IP family of the value; 4 for IPv4, 6 for IPv6.

For example, family('::1') returns 6
host(val: inet) → string
Extracts the address part of the combined address/prefixlen value as text.

For example, host('192.168.1.2/16') returns '192.168.1.2'
hostmask(val: inet) → inet
Creates an IP host mask corresponding to the prefix length in the value.

For example, hostmask('192.168.1.2/16') returns '0.0.255.255'
masklen(val: inet) → int
Retrieves the prefix length stored in the value.

For example, masklen('192.168.1.2/16') returns 16
netmask(val: inet) → inet
Creates an IP network mask corresponding to the prefix length in the value.

For example, netmask('192.168.1.2/16') returns '255.255.0.0'
set_masklen(val: inet, prefixlen: int) → inet
Sets the prefix length of val to prefixlen.

For example, set_masklen('192.168.1.2', 16) returns '192.168.1.2/16'.
text(val: inet) → string
Converts the IP address and prefix length to text.

JSONB functions

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

Math and numeric functions

Function → ReturnsDescription
abs(val: decimal) → decimal
Calculates the absolute value of val.
abs(val: float) → float
Calculates the absolute value of val.
abs(val: int) → int
Calculates the absolute value of val.
acos(val: float) → float
Calculates the inverse cosine of val.
asin(val: float) → float
Calculates the inverse sine of val.
atan(val: float) → float
Calculates the inverse tangent of val.
atan2(x: float, y: float) → float
Calculates the inverse tangent of x/y.
cbrt(val: decimal) → decimal
Calculates the cube root (∛) of val.
cbrt(val: float) → float
Calculates the cube root (∛) of val.
ceil(val: decimal) → decimal
Calculates the smallest integer greater than val.
ceil(val: float) → float
Calculates the smallest integer greater than val.
ceiling(val: decimal) → decimal
Calculates the smallest integer greater than val.
ceiling(val: float) → float
Calculates the smallest integer greater than val.
cos(val: float) → float
Calculates the cosine of val.
cot(val: float) → float
Calculates the cotangent of val.
crc32c(bytes…) → int
Calculates the CRC-32 hash using the Castagnoli polynomial.
crc32c(string…) → int
Calculates the CRC-32 hash using the Castagnoli polynomial.
crc32ieee(bytes…) → int
Calculates the CRC-32 hash using the IEEE polynomial.
crc32ieee(string…) → int
Calculates the CRC-32 hash using the IEEE polynomial.
degrees(val: float) → float
Converts val as a radian value to a degree value.
div(x: decimal, y: decimal) → decimal
Calculates the integer quotient of x/y.
div(x: float, y: float) → float
Calculates the integer quotient of x/y.
div(x: int, y: int) → int
Calculates the integer quotient of x/y.
exp(val: decimal) → decimal
Calculates e ^ val.
exp(val: float) → float
Calculates e ^ val.
floor(val: decimal) → decimal
Calculates the largest integer not greater than val.
floor(val: float) → float
Calculates the largest integer not greater than val.
fnv32(bytes…) → int
Calculates the 32-bit FNV-1 hash value of a set of values.
fnv32(string…) → int
Calculates the 32-bit FNV-1 hash value of a set of values.
fnv32a(bytes…) → int
Calculates the 32-bit FNV-1a hash value of a set of values.
fnv32a(string…) → int
Calculates the 32-bit FNV-1a hash value of a set of values.
fnv64(bytes…) → int
Calculates the 64-bit FNV-1 hash value of a set of values.
fnv64(string…) → int
Calculates the 64-bit FNV-1 hash value of a set of values.
fnv64a(bytes…) → int
Calculates the 64-bit FNV-1a hash value of a set of values.
fnv64a(string…) → int
Calculates the 64-bit FNV-1a hash value of a set of values.
isnan(val: decimal) → bool
Returns true if val is NaN, false otherwise.
isnan(val: float) → bool
Returns true if val is NaN, false otherwise.
ln(val: decimal) → decimal
Calculates the natural log of val.
ln(val: float) → float
Calculates the natural log of val.
log(val: decimal) → decimal
Calculates the base 10 log of val.
log(val: float) → float
Calculates the base 10 log of val.
mod(x: decimal, y: decimal) → decimal
Calculates x%y.
mod(x: float, y: float) → float
Calculates x%y.
mod(x: int, y: int) → int
Calculates x%y.
pi() → float
Returns the value for pi (3.141592653589793).
pow(x: decimal, y: decimal) → decimal
Calculates x^y.
pow(x: float, y: float) → float
Calculates x^y.
pow(x: int, y: int) → int
Calculates x^y.
power(x: decimal, y: decimal) → decimal
Calculates x^y.
power(x: float, y: float) → float
Calculates x^y.
power(x: int, y: int) → int
Calculates x^y.
radians(val: float) → float
Converts val as a degree value to a radians value.
random() → float
Returns a random float between 0 and 1.
round(input: decimal, decimal_accuracy: int) → decimal
Keeps decimal_accuracy number of figures to the right of the zero position in input using half away from zero rounding. If decimal_accuracy is not in the range -2^31…(2^31-1), the results are undefined.
round(input: float, decimal_accuracy: int) → float
Keeps decimal_accuracy number of figures to the right of the zero position in input using half to even (banker’s) rounding.
round(val: decimal) → decimal
Rounds val to the nearest integer, half away from zero: round(+/-2.4) = +/-2, round(+/-2.5) = +/-3.
round(val: float) → float
Rounds val to the nearest integer using half to even (banker’s) rounding.
sign(val: decimal) → decimal
Determines the sign of val: 1 for positive; 0 for 0 values; -1 for negative.
sign(val: float) → float
Determines the sign of val: 1 for positive; 0 for 0 values; -1 for negative.
sign(val: int) → int
Determines the sign of val: 1 for positive; 0 for 0 values; -1 for negative.
sin(val: float) → float
Calculates the sine of val.
sqrt(val: decimal) → decimal
Calculates the square root of val.
sqrt(val: float) → float
Calculates the square root of val.
tan(val: float) → float
Calculates the tangent of val.
trunc(val: decimal) → decimal
Truncates the decimal values of val.
trunc(val: float) → float
Truncates the decimal values of val.

Sequence functions

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

Set-returning functions

Function → ReturnsDescription
crdb_internal.unary_table() → tuple
Produces a virtual table containing a single row with no values.

This function is used only by CockroachDB’s developers for testing purposes.
generate_series(start: int, end: int) → int
Produces a virtual table containing the integer values from start to end, inclusive.
generate_series(start: int, end: int, step: int) → int
Produces a virtual table containing the integer values from start to end, inclusive, by increment of step.
generate_series(start: timestamp, end: timestamp, step: interval) → timestamp
Produces a virtual table containing the timestamp values from start to end, inclusive, by increment of step.
generate_subscripts(array: anyelement[]) → int
Returns a series comprising the given array’s subscripts.
generate_subscripts(array: anyelement[], dim: int) → int
Returns a series comprising the given array’s subscripts.
generate_subscripts(array: anyelement[], dim: int, reverse: bool) → int
Returns a series comprising the given array’s subscripts.

When reverse is true, the series is returned in reverse order.
information_schema._pg_expandarray(input: anyelement[]) → anyelement
Returns the input array as a set of rows with an index
json_array_elements(input: jsonb) → jsonb
Expands a JSON array to a set of JSON values.
json_array_elements_text(input: jsonb) → string
Expands a JSON array to a set of text values.
json_each(input: jsonb) → tuple{string AS key, jsonb AS value}
Expands the outermost JSON or JSONB object into a set of key/value pairs.
json_each_text(input: jsonb) → tuple{string AS key, string AS value}
Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.
json_object_keys(input: jsonb) → string
Returns sorted set of keys in the outermost JSON object.
jsonb_array_elements(input: jsonb) → jsonb
Expands a JSON array to a set of JSON values.
jsonb_array_elements_text(input: jsonb) → string
Expands a JSON array to a set of text values.
jsonb_each(input: jsonb) → tuple{string AS key, jsonb AS value}
Expands the outermost JSON or JSONB object into a set of key/value pairs.
jsonb_each_text(input: jsonb) → tuple{string AS key, string AS value}
Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.
jsonb_object_keys(input: jsonb) → string
Returns sorted set of keys in the outermost JSON object.
pg_get_keywords() → tuple{string AS word, string AS catcode, string AS catdesc}
Produces a virtual table containing the keywords known to the SQL parser.
unnest(input: anyelement[]) → anyelement
Returns the input array as a set of rows

String and byte functions

Function → ReturnsDescription
ascii(val: string) → int
Returns the character code of the first character in val. Despite the name, the function supports Unicode too.
bit_length(val: bytes) → int
Calculates the number of bits in val.
bit_length(val: string) → int
Calculates the number of bits used to represent val.
btrim(input: string, trim_chars: string) → string
Removes any characters included in trim_chars from the beginning or end of input (applies recursively).

For example, btrim('doggie', 'eod') returns ggi.
btrim(val: string) → string
Removes all spaces from the beginning and end of val.
char_length(val: bytes) → int
Calculates the number of bytes in val.
char_length(val: string) → int
Calculates the number of characters in val.
character_length(val: bytes) → int
Calculates the number of bytes in val.
character_length(val: string) → int
Calculates the number of characters in val.
chr(val: int) → string
Returns the character with the code given in val. Inverse function of ascii().
concat(string…) → string
Concatenates a comma-separated list of strings.
concat_ws(string…) → string
Uses the first argument as a separator between the concatenation of the subsequent arguments.

For example concat_ws('!','wow','great') returns wow!great.
convert_from(str: bytes, enc: string) → string
Decode the bytes in str into a string using encoding enc. Supports encodings ‘UTF8’ and ‘LATIN1’.
convert_to(str: string, enc: string) → bytes
Encode the string str as a byte array using encoding enc. Supports encodings ‘UTF8’ and ‘LATIN1’.
decode(text: string, format: string) → bytes
Decodes data using format (hex / escape / base64).
encode(data: bytes, format: string) → string
Encodes data using format (hex / escape / base64).
from_ip(val: bytes) → string
Converts the byte string representation of an IP to its character string representation.
from_uuid(val: bytes) → string
Converts the byte string representation of a UUID to its character string representation.
initcap(val: string) → string
Capitalizes the first letter of val.
left(input: bytes, return_set: int) → bytes
Returns the first return_set bytes from input.
left(input: string, return_set: int) → string
Returns the first return_set characters from input.
length(val: bytes) → int
Calculates the number of bytes in val.
length(val: string) → int
Calculates the number of characters in val.
lower(val: string) → string
Converts all characters in val to their lower-case equivalents.
lpad(string: string, length: int) → string
Pads string to length by adding ’ ’ to the left of string.If string is longer than length it is truncated.
lpad(string: string, length: int, fill: string) → string
Pads string by adding fill to the left of string to make it length. If string is longer than length it is truncated.
ltrim(input: string, trim_chars: string) → string
Removes any characters included in trim_chars from the beginning (left-hand side) of input (applies recursively).

For example, ltrim('doggie', 'od') returns ggie.
ltrim(val: string) → string
Removes all spaces from the beginning (left-hand side) of val.
md5(bytes…) → string
Calculates the MD5 hash value of a set of values.
md5(string…) → string
Calculates the MD5 hash value of a set of values.
octet_length(val: bytes) → int
Calculates the number of bytes in val.
octet_length(val: string) → int
Calculates the number of bytes used to represent val.
overlay(input: string, overlay_val: string, start_pos: int) → string
Replaces characters in input with overlay_val starting at start_pos (begins at 1).

For example, overlay('doggie', 'CAT', 2) returns dCATie.
overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string
Deletes the characters in input between start_pos and end_pos (count starts at 1), and then insert overlay_val at start_pos.
quote_ident(val: string) → string
Return val suitably quoted to serve as identifier in a SQL statement.
quote_literal(val: string) → string
Return val suitably quoted to serve as string literal in a SQL statement.
quote_literal(val: anyelement) → string
Coerce val to a string and then quote it as a literal.
quote_nullable(val: string) → string
Coerce val to a string and then quote it as a literal. If val is NULL, returns ‘NULL’.
quote_nullable(val: anyelement) → string
Coerce val to a string and then quote it as a literal. If val is NULL, returns ‘NULL’.
regexp_extract(input: string, regex: string) → string
Returns the first match for the Regular Expression regex in input.
regexp_replace(input: string, regex: string, replace: string) → string
Replaces matches for the Regular Expression regex in input with the Regular Expression replace.
regexp_replace(input: string, regex: string, replace: string, flags: string) → string
Replaces matches for the regular expression regex in input with the regular expression replace using flags.

CockroachDB supports the following flags:

FlagDescription
cCase-sensitive matching
gGlobal matching (match each substring instead of only the first)
iCase-insensitive matching
m or nNewline-sensitive (see below)
pPartial newline-sensitive matching (see below)
sNewline-insensitive (default)
wInverse partial newline-sensitive matching (see below)
Mode. and [^…] match newlines^ and $ match line boundaries
syesno
wyesyes
pnono
m/nnoyes

repeat(input: string, repeat_counter: int) → string
Concatenates input repeat_counter number of times.

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

replace(input: string, find: string, replace: string) → string
Replaces all occurrences of find with replace in input
reverse(val: string) → string
Reverses the order of the string’s characters.
right(input: bytes, return_set: int) → bytes
Returns the last return_set bytes from input.
right(input: string, return_set: int) → string
Returns the last return_set characters from input.
rpad(string: string, length: int) → string
Pads string to length by adding ’ ’ to the right of string. If string is longer than length it is truncated.
rpad(string: string, length: int, fill: string) → string
Pads string to length by adding fill to the right of string. If string is longer than length it is truncated.
rtrim(input: string, trim_chars: string) → string
Removes any characters included in trim_chars from the end (right-hand side) of input (applies recursively).

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

rtrim(val: string) → string
Removes all spaces from the end (right-hand side) of val.
sha1(bytes…) → string
Calculates the SHA1 hash value of a set of values.
sha1(string…) → string
Calculates the SHA1 hash value of a set of values.
sha256(bytes…) → string
Calculates the SHA256 hash value of a set of values.
sha256(string…) → string
Calculates the SHA256 hash value of a set of values.
sha512(bytes…) → string
Calculates the SHA512 hash value of a set of values.
sha512(string…) → string
Calculates the SHA512 hash value of a set of values.
split_part(input: string, delimiter: string, return_index_pos: int) → string
Splits input on delimiter and return the value in the return_index_pos position (starting at 1).

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

strpos(input: string, find: string) → int
Calculates the position where the string find begins in input.

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

substr(input: string, regex: string) → string
Returns a substring of input that matches the regular expression regex.
substr(input: string, regex: string, escape_char: string) → string
Returns a substring of input that matches the regular expression regex using escape_char as your escape character instead of </code>.

substr(input: string, start_pos: int, 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_hex(val: int) → string
Converts val to its hexadecimal representation.
to_ip(val: string) → bytes
Converts the character string representation of an IP to its byte string representation.
to_uuid(val: string) → bytes
Converts the character string representation of a UUID to its byte string representation.
translate(input: string, find: string, replace: string) → string
In input, replaces the first character from find with the first character in replace; repeat for each character in find.

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

upper(val: string) → string
Converts all characters in val to their to their upper-case equivalents.

System info functions

Function → ReturnsDescription
cluster_logical_timestamp() → decimal
Returns the logical time of the current transaction.

This function is reserved for testing purposes by CockroachDBdevelopers and its definition may change without prior notice.

Note that uses of this function disable server-side optimizations andmay increase either contention or retry errors, or both.
crdb_internal.check_consistency(stats_only: bool, start_key: bytes, end_key: bytes) → tuple{int AS range_id, bytes AS start_key, string AS start_key_pretty, string AS status, string AS detail}[]
Runs a consistency check on ranges touching the specified key range. an empty start or end key is treated as the minimum and maximum possible, respectively. stats_only should only be set to false when targeting a small number of ranges to avoid overloading the cluster. The return value is an array of tuples, with each tuple consisting of the range ID, the status (a roachpb.CheckConsistencyResponse_Status), and verbose detail.

Example usage:

SELECT (t). FROM unnest(crdb_internal.check_consistency(true, '\x02', '\x04')) as t;
crdb_internal.cluster_id() → uuid
Returns the cluster ID.
crdb_internal.force_assertion_error(msg: string) → int
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.force_error(errorCode: string, msg: string) → int
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.force_log_fatal(msg: string) → int
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.force_panic(msg: string) → int
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.force_retry(val: interval) → int
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.json_num_index_entries(val: jsonb) → int
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.lease_holder(key: bytes) → int
This function is used to fetch the leaseholder corresponding to a request key
crdb_internal.no_constant_folding(input: anyelement) → anyelement
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.node_executable_version() → string
Returns the version of CockroachDB this node is running.
crdb_internal.pretty_key(raw_key: bytes, skip_fields: int) → string
This function is used only by CockroachDB’s developers for testing purposes.
crdb_internal.round_decimal_values(val: decimal, scale: int) → decimal
This function is used internally to round decimal values during mutations.
crdb_internal.round_decimal_values(val: decimal[], scale: int) → decimal[]
This function is used internally to round decimal array values during mutations.
crdb_internal.set_vmodule(vmodule_string: string) → int
Set the equivalent of the —vmodule flag on the gateway node processing this request; it affords control over the logging verbosity of different files. Example syntax: crdb_internal.set_vmodule('recordio=2,file=1,gfs=3'). Reset with: crdb_internal.set_vmodule(''). Raising the verbosity can severely affect performance.
current_database() → string
Returns the current database.
current_schema() → string
Returns the current schema.
current_schemas(include_pg_catalog: bool) → string[]
Returns the valid schemas in the search path.
current_user() → string
Returns the current user. This function is provided for compatibility with PostgreSQL.
version() → string
Returns the node’s version of CockroachDB.

Compatibility functions

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

Aggregate functions

Tip:

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

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

Window functions

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

Operators

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

Order of PrecedenceOperatorNameOperator Arity
1.Member field access operatorbinary
2::Type castbinary
3-Unary minusunary (prefix)
~Bitwise notunary (prefix)
4^Exponentiationbinary
5Multiplicationbinary
/Divisionbinary
//Floor divisionbinary
%Modulobinary
6+Additionbinary
-Subtractionbinary
7<<Bitwise left-shiftbinary
>>Bitwise right-shiftbinary
8&Bitwise ANDbinary
9#Bitwise XORbinary
10|Bitwise ORbinary
11||Concatenationbinary
< ANY, SOME, ALLMulti-valued "less than" comparisonbinary
> ANY, SOME, ALLMulti-valued "greater than" comparisonbinary
= ANY, SOME, ALLMulti-valued "equal" comparisonbinary
<= ANY, SOME, ALLMulti-valued "less than or equal" comparisonbinary
>= ANY, SOME, ALLMulti-valued "greater than or equal" comparisonbinary
<> ANY / != ANY, <> SOME / != SOME, <> ALL / != ALLMulti-valued "not equal" comparisonbinary
[NOT] LIKE ANY, [NOT] LIKE SOME, [NOT] LIKE ALLMulti-valued LIKE comparisonbinary
[NOT] ILIKE ANY, [NOT] ILIKE SOME, [NOT] ILIKE ALLMulti-valued ILIKE comparisonbinary
12[NOT] BETWEENValue is [not] within the range specifiedbinary
[NOT] BETWEEN SYMMETRICLike [NOT] BETWEEN, but in non-sorted order. For example, whereas a BETWEEN b AND c means b <= a <= c, a BETWEEN SYMMETRIC b AND c means (b <= a <= c) OR (c <= a <= b).binary
[NOT] INValue is [not] in the set of values specifiedbinary
[NOT] LIKEMatches [or not] LIKE expression, case sensitivebinary
[NOT] ILIKEMatches [or not] LIKE expression, case insensitivebinary
[NOT] SIMILARMatches [or not] SIMILAR TO regular expressionbinary
~Matches regular expression, case sensitivebinary
!~Does not match regular expression, case sensitivebinary
~Matches regular expression, case insensitivebinary
!~*Does not match regular expression, case insensitivebinary
13=Equalbinary
<Less thanbinary
>Greater thanbinary
<=Less than or equal tobinary
>=Greater than or equal tobinary
!=, <>Not equalbinary
14IS [DISTINCT FROM]Equal, considering NULL as valuebinary
IS NOT [DISTINCT FROM]a IS NOT b equivalent to NOT (a IS b)binary
ISNULL, IS UNKNOWN , NOTNULL, IS NOT UNKNOWNEquivalent to IS NULL / IS NOT NULLunary (postfix)
IS NAN, IS NOT NANComparison with the floating-point NaN valueunary (postfix)
IS OF(…)Type predicateunary (postfix)
15NOTLogical NOTunary
16ANDLogical ANDbinary
17ORLogical ORbinary

Supported operations

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

~Return
string ~ stringbool

Was this page helpful?
YesNo