CockroachDB supports the following SQL functions and operators for use in scalar expressions.
Special Syntax Forms
The following syntax forms are recognized for compatibility with the SQL standard and PostgreSQL, but are equivalent to regular built-in functions:
Special form | Equivalent to |
---|---|
CURRENT_CATALOG |
current_catalog() |
CURRENT_DATE |
current_date() |
CURRENT_ROLE |
current_user() |
CURRENT_SCHEMA |
current_schema() |
CURRENT_TIMESTAMP |
current_timestamp() |
CURRENT_TIME |
current_time() |
CURRENT_USER |
current_user() |
EXTRACT(<part> FROM <value>) |
extract("<part>", <value>) |
EXTRACT_DURATION(<part> FROM <value>) |
extract_duration("<part>", <value>) |
OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>) |
overlay(<text1>, <text2>, <int1>, <int2>) |
OVERLAY(<text1> PLACING <text2> FROM <int>) |
overlay(<text1>, <text2>, <int>) |
POSITION(<text1> IN <text2>) |
strpos(<text2>, <text1>) |
SESSION_USER |
current_user() |
SUBSTRING(<text> FOR <int1> FROM <int2>) |
substring(<text>, <int2>, <int1>) |
SUBSTRING(<text> FOR <int>) |
substring(<text>, 1, <int>) |
SUBSTRING(<text> FROM <int1> FOR <int2>) |
substring(<text>, <int1>, <int2>) |
SUBSTRING(<text> FROM <int>) |
substring(<text>, <int>) |
TRIM(<text1> FROM <text2>) |
btrim(<text2>, <text1>) |
TRIM(<text1>, <text2>) |
btrim(<text1>, <text2>) |
TRIM(FROM <text>) |
btrim(<text>) |
TRIM(LEADING <text1> FROM <text2>) |
ltrim(<text2>, <text1>) |
TRIM(LEADING FROM <text>) |
ltrim(<text>) |
TRIM(TRAILING <text1> FROM <text2>) |
rtrim(<text2>, <text1>) |
TRIM(TRAILING FROM <text>) |
rtrim(<text>) |
USER |
current_user() |
Conditional and Function-Like Operators
The following table lists the operators that look like built-in functions but have special evaluation rules:
Operator | Description |
---|---|
ANNOTATE_TYPE(...) |
Explicitly Typed Expression |
ARRAY(...) |
Conversion of Subquery Results to An Array |
ARRAY[...] |
Conversion of Scalar Expressions to An Array |
CAST(...) |
Type Cast |
COALESCE(...) |
First non-NULL expression with Short Circuit |
EXISTS(...) |
Existence Test on the Result of Subqueries |
IF(...) |
Conditional Evaluation |
IFNULL(...) |
Alias for COALESCE restricted to two operands |
NULLIF(...) |
Return NULL conditionally |
ROW(...) |
Tuple Constructor |
Built-in Functions
ANYELEMENT Functions
Function → Returns | Description |
---|---|
to_json(val: anyelement) → jsonb | Returns the value as JSON or JSONB. |
to_jsonb(val: anyelement) → jsonb | Returns the value as JSON or JSONB. |
Array Functions
Function → Returns | Description |
---|---|
array_append(array: bool[], elem: bool) → bool[] | Appends |
array_append(array: bytes[], elem: bytes) → bytes[] | Appends |
array_append(array: date[], elem: date) → date[] | Appends |
array_append(array: decimal[], elem: decimal) → decimal[] | Appends |
array_append(array: float[], elem: float) → float[] | Appends |
array_append(array: inet[], elem: inet) → inet[] | Appends |
array_append(array: int[], elem: int) → int[] | Appends |
array_append(array: interval[], elem: interval) → interval[] | Appends |
array_append(array: string[], elem: string) → string[] | Appends |
array_append(array: time[], elem: time) → time[] | Appends |
array_append(array: timestamp[], elem: timestamp) → timestamp[] | Appends |
array_append(array: timestamptz[], elem: timestamptz) → timestamptz[] | Appends |
array_append(array: uuid[], elem: uuid) → uuid[] | Appends |
array_append(array: oid[], elem: oid) → oid[] | Appends |
array_cat(left: bool[], right: bool[]) → bool[] | Appends two arrays. |
array_cat(left: bytes[], right: bytes[]) → bytes[] | Appends two arrays. |
array_cat(left: date[], right: date[]) → date[] | Appends two arrays. |
array_cat(left: decimal[], right: decimal[]) → decimal[] | Appends two arrays. |
array_cat(left: float[], right: float[]) → float[] | Appends two arrays. |
array_cat(left: inet[], right: inet[]) → inet[] | Appends two arrays. |
array_cat(left: int[], right: int[]) → int[] | Appends two arrays. |
array_cat(left: interval[], right: interval[]) → interval[] | Appends two arrays. |
array_cat(left: string[], right: string[]) → string[] | Appends two arrays. |
array_cat(left: time[], right: time[]) → time[] | Appends two arrays. |
array_cat(left: timestamp[], right: timestamp[]) → timestamp[] | Appends two arrays. |
array_cat(left: timestamptz[], right: timestamptz[]) → timestamptz[] | Appends two arrays. |
array_cat(left: uuid[], right: uuid[]) → uuid[] | Appends two arrays. |
array_cat(left: oid[], right: oid[]) → oid[] | Appends two arrays. |
array_length(input: anyelement[], array_dimension: int) → int | Calculates the length of |
array_lower(input: anyelement[], array_dimension: int) → int | Calculates the minimum value of |
array_position(array: bool[], elem: bool) → int | Return the index of the first occurrence of |
array_position(array: bytes[], elem: bytes) → int | Return the index of the first occurrence of |
array_position(array: date[], elem: date) → int | Return the index of the first occurrence of |
array_position(array: decimal[], elem: decimal) → int | Return the index of the first occurrence of |
array_position(array: float[], elem: float) → int | Return the index of the first occurrence of |
array_position(array: inet[], elem: inet) → int | Return the index of the first occurrence of |
array_position(array: int[], elem: int) → int | Return the index of the first occurrence of |
array_position(array: interval[], elem: interval) → int | Return the index of the first occurrence of |
array_position(array: string[], elem: string) → int | Return the index of the first occurrence of |
array_position(array: time[], elem: time) → int | Return the index of the first occurrence of |
array_position(array: timestamp[], elem: timestamp) → int | Return the index of the first occurrence of |
array_position(array: timestamptz[], elem: timestamptz) → int | Return the index of the first occurrence of |
array_position(array: uuid[], elem: uuid) → int | Return the index of the first occurrence of |
array_position(array: oid[], elem: oid) → int | Return the index of the first occurrence of |
array_positions(array: bool[], elem: bool) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: bytes[], elem: bytes) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: date[], elem: date) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: decimal[], elem: decimal) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: float[], elem: float) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: inet[], elem: inet) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: int[], elem: int) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: interval[], elem: interval) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: string[], elem: string) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: time[], elem: time) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: timestamp[], elem: timestamp) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: timestamptz[], elem: timestamptz) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: uuid[], elem: uuid) → int[] | Returns and array of indexes of all occurrences of |
array_positions(array: oid[], elem: oid) → int[] | Returns and array of indexes of all occurrences of |
array_prepend(elem: bool, array: bool[]) → bool[] | Prepends |
array_prepend(elem: bytes, array: bytes[]) → bytes[] | Prepends |
array_prepend(elem: date, array: date[]) → date[] | Prepends |
array_prepend(elem: decimal, array: decimal[]) → decimal[] | Prepends |
array_prepend(elem: float, array: float[]) → float[] | Prepends |
array_prepend(elem: inet, array: inet[]) → inet[] | Prepends |
array_prepend(elem: int, array: int[]) → int[] | Prepends |
array_prepend(elem: interval, array: interval[]) → interval[] | Prepends |
array_prepend(elem: string, array: string[]) → string[] | Prepends |
array_prepend(elem: time, array: time[]) → time[] | Prepends |
array_prepend(elem: timestamp, array: timestamp[]) → timestamp[] | Prepends |
array_prepend(elem: timestamptz, array: timestamptz[]) → timestamptz[] | Prepends |
array_prepend(elem: uuid, array: uuid[]) → uuid[] | Prepends |
array_prepend(elem: oid, array: oid[]) → oid[] | Prepends |
array_remove(array: bool[], elem: bool) → bool[] | Remove from |
array_remove(array: bytes[], elem: bytes) → bytes[] | Remove from |
array_remove(array: date[], elem: date) → date[] | Remove from |
array_remove(array: decimal[], elem: decimal) → decimal[] | Remove from |
array_remove(array: float[], elem: float) → float[] | Remove from |
array_remove(array: inet[], elem: inet) → inet[] | Remove from |
array_remove(array: int[], elem: int) → int[] | Remove from |
array_remove(array: interval[], elem: interval) → interval[] | Remove from |
array_remove(array: string[], elem: string) → string[] | Remove from |
array_remove(array: time[], elem: time) → time[] | Remove from |
array_remove(array: timestamp[], elem: timestamp) → timestamp[] | Remove from |
array_remove(array: timestamptz[], elem: timestamptz) → timestamptz[] | Remove from |
array_remove(array: uuid[], elem: uuid) → uuid[] | Remove from |
array_remove(array: oid[], elem: oid) → oid[] | Remove from |
array_replace(array: bool[], toreplace: bool, replacewith: bool) → bool[] | Replace all occurrences of |
array_replace(array: bytes[], toreplace: bytes, replacewith: bytes) → bytes[] | Replace all occurrences of |
array_replace(array: date[], toreplace: date, replacewith: date) → date[] | Replace all occurrences of |
array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) → decimal[] | Replace all occurrences of |
array_replace(array: float[], toreplace: float, replacewith: float) → float[] | Replace all occurrences of |
array_replace(array: inet[], toreplace: inet, replacewith: inet) → inet[] | Replace all occurrences of |
array_replace(array: int[], toreplace: int, replacewith: int) → int[] | Replace all occurrences of |
array_replace(array: interval[], toreplace: interval, replacewith: interval) → interval[] | Replace all occurrences of |
array_replace(array: string[], toreplace: string, replacewith: string) → string[] | Replace all occurrences of |
array_replace(array: time[], toreplace: time, replacewith: time) → time[] | Replace all occurrences of |
array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp) → timestamp[] | Replace all occurrences of |
array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) → timestamptz[] | Replace all occurrences of |
array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) → uuid[] | Replace all occurrences of |
array_replace(array: oid[], toreplace: oid, replacewith: oid) → oid[] | Replace all occurrences of |
array_upper(input: anyelement[], array_dimension: int) → int | Calculates the maximum value of |
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 → Returns | Description |
---|---|
inet_contained_by_or_equals(val: inet, container: inet) → bool | Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored. |
inet_contains_or_contained_by(val: inet, val: inet) → bool | Test for subnet inclusion, using only the network parts of the addresses. The host part of the addresses is ignored. |
inet_contains_or_equals(container: inet, val: inet) → bool | Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored. |
inet_same_family(val: inet, val: inet) → bool | Checks if two IP addresses are of the same IP family. |
Comparison Functions
Function → Returns | Description |
---|---|
greatest(anyelement...) → anyelement | Returns the element with the greatest value. |
least(anyelement...) → anyelement | Returns the element with the lowest value. |
Date and Time Functions
Function → Returns | Description |
---|---|
age(begin: timestamptz, end: timestamptz) → interval | Calculates the interval between |
age(val: timestamptz) → interval | Calculates the interval between the current time and |
clock_timestamp() → timestamp | Returns the current system time on one of the cluster nodes. |
clock_timestamp() → timestamptz | Returns the current system time on one of the cluster nodes. |
current_date() → date | Returns the date of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
current_timestamp() → timestamp | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
current_timestamp() → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
date_trunc(element: string, input: date) → timestamptz | Truncates Compatible elements: year, quarter, month, week, hour, minute, second, millisecond, microsecond. |
date_trunc(element: string, input: time) → interval | Truncates Compatible elements: hour, minute, second, millisecond, microsecond. |
date_trunc(element: string, input: timestamp) → timestamp | Truncates Compatible elements: year, quarter, month, week, hour, minute, second, millisecond, microsecond. |
date_trunc(element: string, input: timestamptz) → timestamptz | Truncates Compatible elements: year, quarter, month, week, hour, minute, second, millisecond, microsecond. |
experimental_strftime(input: date, extract_format: string) → string | From |
experimental_strftime(input: timestamp, extract_format: string) → string | From |
experimental_strftime(input: timestamptz, extract_format: string) → string | From |
experimental_strptime(input: string, format: string) → timestamptz | Returns |
extract(element: string, input: date) → int | Extracts Compatible elements: year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch |
extract(element: string, input: time) → int | Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch |
extract(element: string, input: timestamp) → int | Extracts Compatible elements: year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch |
extract(element: string, input: timestamptz) → int | Extracts Compatible elements: year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch |
extract_duration(element: string, input: interval) → int | Extracts |
now() → timestamp | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
now() → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
statement_timestamp() → timestamp | Returns the start time of the current statement. |
statement_timestamp() → timestamptz | Returns the start time of the current statement. |
transaction_timestamp() → timestamp | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
transaction_timestamp() → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
ID Generation Functions
Function → Returns | Description |
---|---|
experimental_uuid_v4() → bytes | Returns a UUID. |
gen_random_uuid() → uuid | Generates a random UUID and returns it as a value of UUID type. |
unique_rowid() → int | Returns a unique ID used by CockroachDB to generate unique row IDs if a Primary Key isn’t defined for the table. The value is a combination of the insert timestamp and the ID of the node executing the statement, which guarantees this combination is globally unique. |
uuid_v4() → bytes | Returns a UUID. |
INET Functions
Function → Returns | Description |
---|---|
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, |
broadcast(val: inet) → inet | Gets the broadcast address for the network address represented by the value. For example, |
family(val: inet) → int | Extracts the IP family of the value; 4 for IPv4, 6 for IPv6. For example, |
host(val: inet) → string | Extracts the address part of the combined address/prefixlen value as text. For example, |
hostmask(val: inet) → inet | Creates an IP host mask corresponding to the prefix length in the value. For example, |
masklen(val: inet) → int | Retrieves the prefix length stored in the value. For example, |
netmask(val: inet) → inet | Creates an IP network mask corresponding to the prefix length in the value. For example, |
set_masklen(val: inet, prefixlen: int) → inet | Sets the prefix length of For example, |
text(val: inet) → string | Converts the IP address and prefix length to text. |
JSONB Functions
Function → Returns | Description |
---|---|
json_array_length(json: jsonb) → int | Returns the number of elements in the outermost JSON or JSONB array. |
json_build_array(anyelement...) → jsonb | Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list. |
json_build_object(anyelement...) → jsonb | Builds a JSON object out of a variadic argument list. |
json_extract_path(jsonb, string...) → jsonb | Returns the JSON value pointed to by the variadic arguments. |
json_object(keys: string[], values: string[]) → jsonb | This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form. |
json_remove_path(val: jsonb, path: string[]) → jsonb | Remove the specified path from the JSON object. |
json_set(val: jsonb, path: string[], to: jsonb) → jsonb | Returns the JSON value pointed to by the variadic arguments. |
json_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb | Returns the JSON value pointed to by the variadic arguments. If |
json_strip_nulls(from_json: jsonb) → jsonb | Returns from_json with all object fields that have null values omitted. Other null values are untouched. |
json_typeof(val: jsonb) → string | Returns the type of the outermost JSON value as a text string. |
jsonb_array_length(json: jsonb) → int | Returns the number of elements in the outermost JSON or JSONB array. |
jsonb_build_array(anyelement...) → jsonb | Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list. |
jsonb_build_object(anyelement...) → jsonb | Builds a JSON object out of a variadic argument list. |
jsonb_extract_path(jsonb, string...) → jsonb | Returns the JSON value pointed to by the variadic arguments. |
jsonb_object(keys: string[], values: string[]) → jsonb | This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form. |
jsonb_pretty(val: jsonb) → string | Returns the given JSON value as a STRING indented and with newlines. |
jsonb_set(val: jsonb, path: string[], to: jsonb) → jsonb | Returns the JSON value pointed to by the variadic arguments. |
jsonb_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb | Returns the JSON value pointed to by the variadic arguments. If |
jsonb_strip_nulls(from_json: jsonb) → jsonb | Returns from_json with all object fields that have null values omitted. Other null values are untouched. |
jsonb_typeof(val: jsonb) → string | Returns the type of the outermost JSON value as a text string. |
Math and Numeric Functions
Function → Returns | Description |
---|---|
abs(val: decimal) → decimal | Calculates the absolute value of |
abs(val: float) → float | Calculates the absolute value of |
abs(val: int) → int | Calculates the absolute value of |
acos(val: float) → float | Calculates the inverse cosine of |
asin(val: float) → float | Calculates the inverse sine of |
atan(val: float) → float | Calculates the inverse tangent of |
atan2(x: float, y: float) → float | Calculates the inverse tangent of |
cbrt(val: decimal) → decimal | Calculates the cube root (∛) of |
cbrt(val: float) → float | Calculates the cube root (∛) of |
ceil(val: decimal) → decimal | Calculates the smallest integer greater than |
ceil(val: float) → float | Calculates the smallest integer greater than |
ceiling(val: decimal) → decimal | Calculates the smallest integer greater than |
ceiling(val: float) → float | Calculates the smallest integer greater than |
cos(val: float) → float | Calculates the cosine of |
cot(val: float) → float | Calculates the cotangent of |
crc32c(bytes...) → int | Calculates the CRC-32 hash using the Castagnoli polynomial. |
crc32c(string...) → int | Calculates the CRC-32 hash using the Castagnoli polynomial. |
crc32ieee(bytes...) → int | Calculates the CRC-32 hash using the IEEE polynomial. |
crc32ieee(string...) → int | Calculates the CRC-32 hash using the IEEE polynomial. |
degrees(val: float) → float | Converts |
div(x: decimal, y: decimal) → decimal | Calculates the integer quotient of |
div(x: float, y: float) → float | Calculates the integer quotient of |
div(x: int, y: int) → int | Calculates the integer quotient of |
exp(val: decimal) → decimal | Calculates e ^ |
exp(val: float) → float | Calculates e ^ |
floor(val: decimal) → decimal | Calculates the largest integer not greater than |
floor(val: float) → float | Calculates the largest integer not greater than |
fnv32(bytes...) → int | Calculates the 32-bit FNV-1 hash value of a set of values. |
fnv32(string...) → int | Calculates the 32-bit FNV-1 hash value of a set of values. |
fnv32a(bytes...) → int | Calculates the 32-bit FNV-1a hash value of a set of values. |
fnv32a(string...) → int | Calculates the 32-bit FNV-1a hash value of a set of values. |
fnv64(bytes...) → int | Calculates the 64-bit FNV-1 hash value of a set of values. |
fnv64(string...) → int | Calculates the 64-bit FNV-1 hash value of a set of values. |
fnv64a(bytes...) → int | Calculates the 64-bit FNV-1a hash value of a set of values. |
fnv64a(string...) → int | Calculates the 64-bit FNV-1a hash value of a set of values. |
isnan(val: decimal) → bool | Returns true if |
isnan(val: float) → bool | Returns true if |
ln(val: decimal) → decimal | Calculates the natural log of |
ln(val: float) → float | Calculates the natural log of |
log(val: decimal) → decimal | Calculates the base 10 log of |
log(val: float) → float | Calculates the base 10 log of |
mod(x: decimal, y: decimal) → decimal | Calculates |
mod(x: float, y: float) → float | Calculates |
mod(x: int, y: int) → int | Calculates |
pi() → float | Returns the value for pi (3.141592653589793). |
pow(x: decimal, y: decimal) → decimal | Calculates |
pow(x: float, y: float) → float | Calculates |
pow(x: int, y: int) → int | Calculates |
power(x: decimal, y: decimal) → decimal | Calculates |
power(x: float, y: float) → float | Calculates |
power(x: int, y: int) → int | Calculates |
radians(val: float) → float | Converts |
random() → float | Returns a random float between 0 and 1. |
round(input: decimal, decimal_accuracy: int) → decimal | Keeps |
round(input: float, decimal_accuracy: int) → float | Keeps |
round(val: decimal) → decimal | Rounds |
round(val: float) → float | Rounds |
sign(val: decimal) → decimal | Determines the sign of |
sign(val: float) → float | Determines the sign of |
sign(val: int) → int | Determines the sign of |
sin(val: float) → float | Calculates the sine of |
sqrt(val: decimal) → decimal | Calculates the square root of |
sqrt(val: float) → float | Calculates the square root of |
tan(val: float) → float | Calculates the tangent of |
to_hex(val: int) → string | Converts |
trunc(val: decimal) → decimal | Truncates the decimal values of |
trunc(val: float) → float | Truncates the decimal values of |
STRING[] Functions
Function → Returns | Description |
---|---|
json_object(texts: string[]) → jsonb | Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs. |
jsonb_object(texts: string[]) → jsonb | Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs. |
Sequence Functions
Function → Returns | Description |
---|---|
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 |
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 |
String and Byte Functions
Function → Returns | Description | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ascii(val: string) → int | Calculates the ASCII value for the first character in | |||||||||||||||||||||||||||||
btrim(input: string, trim_chars: string) → string | Removes any characters included in For example, | |||||||||||||||||||||||||||||
btrim(val: string) → string | Removes all spaces from the beginning and end of | |||||||||||||||||||||||||||||
concat(string...) → string | Concatenates a comma-separated list of strings. | |||||||||||||||||||||||||||||
concat_ws(string...) → string | Uses the first argument as a separator between the concatenation of the subsequent arguments. For example | |||||||||||||||||||||||||||||
decode(text: string, format: string) → bytes | Decodes | |||||||||||||||||||||||||||||
encode(data: bytes, format: string) → string | Encodes | |||||||||||||||||||||||||||||
from_ip(val: bytes) → string | Converts the byte string representation of an IP to its character string representation. | |||||||||||||||||||||||||||||
from_uuid(val: bytes) → string | Converts the byte string representation of a UUID to its character string representation. | |||||||||||||||||||||||||||||
initcap(val: string) → string | Capitalizes the first letter of | |||||||||||||||||||||||||||||
left(input: bytes, return_set: int) → bytes | Returns the first | |||||||||||||||||||||||||||||
left(input: string, return_set: int) → string | Returns the first | |||||||||||||||||||||||||||||
length(val: bytes) → int | Calculates the number of bytes in | |||||||||||||||||||||||||||||
length(val: string) → int | Calculates the number of characters in | |||||||||||||||||||||||||||||
lower(val: string) → string | Converts all characters in | |||||||||||||||||||||||||||||
ltrim(input: string, trim_chars: string) → string | Removes any characters included in For example, | |||||||||||||||||||||||||||||
ltrim(val: string) → string | Removes all spaces from the beginning (left-hand side) of | |||||||||||||||||||||||||||||
md5(bytes...) → string | Calculates the MD5 hash value of a set of values. | |||||||||||||||||||||||||||||
md5(string...) → string | Calculates the MD5 hash value of a set of values. | |||||||||||||||||||||||||||||
octet_length(val: bytes) → int | Calculates the number of bytes in | |||||||||||||||||||||||||||||
octet_length(val: string) → int | Calculates the number of bytes used to represent | |||||||||||||||||||||||||||||
overlay(input: string, overlay_val: string, start_pos: int) → string | Replaces characters in For example, | |||||||||||||||||||||||||||||
overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string | Deletes the characters in | |||||||||||||||||||||||||||||
regexp_extract(input: string, regex: string) → string | Returns the first match for the Regular Expression | |||||||||||||||||||||||||||||
regexp_replace(input: string, regex: string, replace: string) → string | Replaces matches for the Regular Expression | |||||||||||||||||||||||||||||
regexp_replace(input: string, regex: string, replace: string, flags: string) → string | Replaces matches for the regular expression CockroachDB supports the following flags:
| |||||||||||||||||||||||||||||
repeat(input: string, repeat_counter: int) → string | Concatenates For example, | |||||||||||||||||||||||||||||
replace(input: string, find: string, replace: string) → string | Replaces all occurrences of | |||||||||||||||||||||||||||||
reverse(val: string) → string | Reverses the order of the string’s characters. | |||||||||||||||||||||||||||||
right(input: bytes, return_set: int) → bytes | Returns the last | |||||||||||||||||||||||||||||
right(input: string, return_set: int) → string | Returns the last | |||||||||||||||||||||||||||||
rtrim(input: string, trim_chars: string) → string | Removes any characters included in For example, | |||||||||||||||||||||||||||||
rtrim(val: string) → string | Removes all spaces from the end (right-hand side) of | |||||||||||||||||||||||||||||
sha1(bytes...) → string | Calculates the SHA1 hash value of a set of values. | |||||||||||||||||||||||||||||
sha1(string...) → string | Calculates the SHA1 hash value of a set of values. | |||||||||||||||||||||||||||||
sha256(bytes...) → string | Calculates the SHA256 hash value of a set of values. | |||||||||||||||||||||||||||||
sha256(string...) → string | Calculates the SHA256 hash value of a set of values. | |||||||||||||||||||||||||||||
sha512(bytes...) → string | Calculates the SHA512 hash value of a set of values. | |||||||||||||||||||||||||||||
sha512(string...) → string | Calculates the SHA512 hash value of a set of values. | |||||||||||||||||||||||||||||
split_part(input: string, delimiter: string, return_index_pos: int) → string | Splits For example, | |||||||||||||||||||||||||||||
strpos(input: string, find: string) → int | Calculates the position where the string For example, | |||||||||||||||||||||||||||||
substr(input: string, regex: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substr(input: string, regex: string, escape_char: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substr(input: string, start_pos: int, end_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
substr(input: string, substr_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, regex: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, regex: string, escape_char: string) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, start_pos: int, end_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
substring(input: string, substr_pos: int) → string | Returns a substring of | |||||||||||||||||||||||||||||
to_english(val: int) → string | This function enunciates the value of its argument using English cardinals. | |||||||||||||||||||||||||||||
to_hex(val: bytes) → string | Converts | |||||||||||||||||||||||||||||
to_ip(val: string) → bytes | Converts the character string representation of an IP to its byte string representation. | |||||||||||||||||||||||||||||
to_uuid(val: string) → bytes | Converts the character string representation of a UUID to its byte string representation. | |||||||||||||||||||||||||||||
translate(input: string, find: string, replace: string) → string | In For example, | |||||||||||||||||||||||||||||
upper(val: string) → string | Converts all characters in |
System Info Functions
Function → Returns | Description |
---|---|
cluster_logical_timestamp() → decimal | Returns the logical time of the current transaction. This function is reserved for testing purposes by CockroachDB developers and its definition may change without prior notice. Note that uses of this function disable server-side optimizations and may increase either contention or retry errors, or both. |
crdb_internal.cluster_id() → uuid | Returns the cluster ID. |
crdb_internal.force_error(errorCode: string, msg: string) → int | This function is used only by CockroachDB’s developers for testing purposes. |
crdb_internal.force_log_fatal(msg: string) → int | This function is used only by CockroachDB’s developers for testing purposes. |
crdb_internal.force_panic(msg: string) → int | This function is used only by CockroachDB’s developers for testing purposes. |
crdb_internal.force_retry(val: interval) → int | This function is used only by CockroachDB’s developers for testing purposes. |
crdb_internal.no_constant_folding(input: anyelement) → anyelement | This function is used only by CockroachDB’s developers for testing purposes. |
crdb_internal.node_executable_version() → string | Returns the version of CockroachDB this node is running. |
crdb_internal.set_vmodule(vmodule_string: string) → int | This function is used for internal debugging purposes. Incorrect use can severely impact performance. |
current_database() → string | Returns the current database. |
current_schema() → string | Returns the current schema. |
current_schemas(include_pg_catalog: bool) → string[] | Returns the valid schemas in the search path. |
current_user() → string | Returns the current user. This function is provided for compatibility with PostgreSQL. |
version() → string | Returns the node’s version of CockroachDB. |
Compatibility Functions
Function → Returns | Description |
---|---|
crdb_internal.unary_table() → setof tuple{} | Produces a virtual table containing a single row with no values. This function is used only by CockroachDB’s developers for testing purposes. |
format_type(type_oid: oid, typemod: int) → string | Returns the SQL name of a data type that is identified by its type OID and possibly a type modifier. Currently, the type modifier is ignored. |
generate_series(start: int, end: int) → setof tuple{int} | Produces a virtual table containing the integer values from |
generate_series(start: int, end: int, step: int) → setof tuple{int} | Produces a virtual table containing the integer values from |
generate_series(start: timestamp, end: timestamp, step: interval) → setof tuple{timestamp} | Produces a virtual table containing the timestamp values from |
has_any_column_privilege(table: string, privilege: string) → bool | does current user have privilege for any column of table |
has_any_column_privilege(table: oid, privilege: string) → bool | does current user have privilege for any column of table |
has_any_column_privilege(user: string, table: string, privilege: string) → bool | does user have privilege for any column of table |
has_any_column_privilege(user: string, table: oid, privilege: string) → bool | does user have privilege for any column of table |
has_any_column_privilege(user: oid, table: string, privilege: string) → bool | does user have privilege for any column of table |
has_any_column_privilege(user: oid, table: oid, privilege: string) → bool | does user have privilege for any column of table |
has_column_privilege(table: string, column: int, privilege: string) → bool | does current user have privilege for column |
has_column_privilege(table: string, column: string, privilege: string) → bool | does current user have privilege for column |
has_column_privilege(table: oid, column: int, privilege: string) → bool | does current user have privilege for column |
has_column_privilege(table: oid, column: string, privilege: string) → bool | does current user have privilege for column |
has_column_privilege(user: string, table: string, column: int, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: string, table: string, column: string, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: string, table: oid, column: int, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: string, table: oid, column: string, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: oid, table: string, column: int, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: oid, table: string, column: string, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: oid, table: oid, column: int, privilege: string) → bool | does user have privilege for column |
has_column_privilege(user: oid, table: oid, column: string, privilege: string) → bool | does user have privilege for column |
has_database_privilege(database: string, privilege: string) → bool | does current user have privilege for database |
has_database_privilege(database: oid, privilege: string) → bool | does current user have privilege for database |
has_database_privilege(user: string, database: string, privilege: string) → bool | does user have privilege for database |
has_database_privilege(user: string, database: oid, privilege: string) → bool | does user have privilege for database |
has_database_privilege(user: oid, database: string, privilege: string) → bool | does user have privilege for database |
has_database_privilege(user: oid, database: oid, privilege: string) → bool | does user have privilege for database |
has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool | does current user have privilege for foreign-data wrapper |
has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool | does current user have privilege for foreign-data wrapper |
has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool | does user have privilege for foreign-data wrapper |
has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool | does user have privilege for foreign-data wrapper |
has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool | does user have privilege for foreign-data wrapper |
has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool | does user have privilege for foreign-data wrapper |
has_function_privilege(function: string, privilege: string) → bool | does current user have privilege for function |
has_function_privilege(function: oid, privilege: string) → bool | does current user have privilege for function |
has_function_privilege(user: string, function: string, privilege: string) → bool | does user have privilege for function |
has_function_privilege(user: string, function: oid, privilege: string) → bool | does user have privilege for function |
has_function_privilege(user: oid, function: string, privilege: string) → bool | does user have privilege for function |
has_function_privilege(user: oid, function: oid, privilege: string) → bool | does user have privilege for function |
has_language_privilege(language: string, privilege: string) → bool | does current user have privilege for language |
has_language_privilege(language: oid, privilege: string) → bool | does current user have privilege for language |
has_language_privilege(user: string, language: string, privilege: string) → bool | does user have privilege for language |
has_language_privilege(user: string, language: oid, privilege: string) → bool | does user have privilege for language |
has_language_privilege(user: oid, language: string, privilege: string) → bool | does user have privilege for language |
has_language_privilege(user: oid, language: oid, privilege: string) → bool | does user have privilege for language |
has_schema_privilege(schema: string, privilege: string) → bool | does current user have privilege for schema |
has_schema_privilege(schema: oid, privilege: string) → bool | does current user have privilege for schema |
has_schema_privilege(user: string, schema: string, privilege: string) → bool | does user have privilege for schema |
has_schema_privilege(user: string, schema: oid, privilege: string) → bool | does user have privilege for schema |
has_schema_privilege(user: oid, schema: string, privilege: string) → bool | does user have privilege for schema |
has_schema_privilege(user: oid, schema: oid, privilege: string) → bool | does user have privilege for schema |
has_sequence_privilege(sequence: string, privilege: string) → bool | does current user have privilege for sequence |
has_sequence_privilege(sequence: oid, privilege: string) → bool | does current user have privilege for sequence |
has_sequence_privilege(user: string, sequence: string, privilege: string) → bool | does user have privilege for sequence |
has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool | does user have privilege for sequence |
has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool | does user have privilege for sequence |
has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool | does user have privilege for sequence |
has_server_privilege(server: string, privilege: string) → bool | does current user have privilege for foreign server |
has_server_privilege(server: oid, privilege: string) → bool | does current user have privilege for foreign server |
has_server_privilege(user: string, server: string, privilege: string) → bool | does user have privilege for foreign server |
has_server_privilege(user: string, server: oid, privilege: string) → bool | does user have privilege for foreign server |
has_server_privilege(user: oid, server: string, privilege: string) → bool | does user have privilege for foreign server |
has_server_privilege(user: oid, server: oid, privilege: string) → bool | does user have privilege for foreign server |
has_table_privilege(table: string, privilege: string) → bool | does current user have privilege for table |
has_table_privilege(table: oid, privilege: string) → bool | does current user have privilege for table |
has_table_privilege(user: string, table: string, privilege: string) → bool | does user have privilege for table |
has_table_privilege(user: string, table: oid, privilege: string) → bool | does user have privilege for table |
has_table_privilege(user: oid, table: string, privilege: string) → bool | does user have privilege for table |
has_table_privilege(user: oid, table: oid, privilege: string) → bool | does user have privilege for table |
has_tablespace_privilege(tablespace: string, privilege: string) → bool | does current user have privilege for tablespace |
has_tablespace_privilege(tablespace: oid, privilege: string) → bool | does current user have privilege for tablespace |
has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool | does user have privilege for tablespace |
has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool | does user have privilege for tablespace |
has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool | does user have privilege for tablespace |
has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool | does user have privilege for tablespace |
has_type_privilege(type: string, privilege: string) → bool | does current user have privilege for type |
has_type_privilege(type: oid, privilege: string) → bool | does current user have privilege for type |
has_type_privilege(user: string, type: string, privilege: string) → bool | does user have privilege for type |
has_type_privilege(user: string, type: oid, privilege: string) → bool | does user have privilege for type |
has_type_privilege(user: oid, type: string, privilege: string) → bool | does user have privilege for type |
has_type_privilege(user: oid, type: oid, privilege: string) → bool | does user have privilege for type |
json_array_elements(input: jsonb) → setof tuple{jsonb} | Expands a JSON array to a set of JSON values. |
json_array_elements_text(input: jsonb) → setof tuple{string} | Expands a JSON array to a set of text values. |
json_each(input: jsonb) → setof tuple{string, jsonb} | Expands the outermost JSON or JSONB object into a set of key/value pairs. |
json_each_text(input: jsonb) → setof tuple{string, string} | Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text. |
json_object_keys(input: jsonb) → setof tuple{string} | Returns sorted set of keys in the outermost JSON object. |
jsonb_array_elements(input: jsonb) → setof tuple{jsonb} | Expands a JSON array to a set of JSON values. |
jsonb_array_elements_text(input: jsonb) → setof tuple{string} | Expands a JSON array to a set of text values. |
jsonb_each(input: jsonb) → setof tuple{string, jsonb} | Expands the outermost JSON or JSONB object into a set of key/value pairs. |
jsonb_each_text(input: jsonb) → setof tuple{string, string} | Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text. |
jsonb_object_keys(input: jsonb) → setof tuple{string} | Returns sorted set of keys in the outermost JSON object. |
oid(int: int) → oid | Converts an integer to an OID. |
pg_get_keywords() → setof tuple{string, string, string} | Produces a virtual table containing the keywords known to the SQL parser. |
unnest(input: anyelement[]) → anyelement | Returns the input array as a set of rows |
Aggregate Functions
Function → Returns | Description |
---|---|
array_agg(arg1: bool) → bool[] | Aggregates the selected values into an array. |
array_agg(arg1: bytes) → bytes[] | Aggregates the selected values into an array. |
array_agg(arg1: date) → date[] | Aggregates the selected values into an array. |
array_agg(arg1: decimal) → decimal[] | Aggregates the selected values into an array. |
array_agg(arg1: float) → float[] | Aggregates the selected values into an array. |
array_agg(arg1: inet) → inet[] | Aggregates the selected values into an array. |
array_agg(arg1: int) → int[] | Aggregates the selected values into an array. |
array_agg(arg1: interval) → interval[] | Aggregates the selected values into an array. |
array_agg(arg1: string) → string[] | Aggregates the selected values into an array. |
array_agg(arg1: time) → time[] | Aggregates the selected values into an array. |
array_agg(arg1: timestamp) → timestamp[] | Aggregates the selected values into an array. |
array_agg(arg1: timestamptz) → timestamptz[] | Aggregates the selected values into an array. |
array_agg(arg1: uuid) → uuid[] | Aggregates the selected values into an array. |
array_agg(arg1: oid) → oid[] | Aggregates the selected values into an array. |
avg(arg1: decimal) → decimal | Calculates the average of the selected values. |
avg(arg1: float) → float | Calculates the average of the selected values. |
avg(arg1: int) → decimal | Calculates the average of the selected values. |
bool_and(arg1: bool) → bool | Calculates the boolean value of |
bool_or(arg1: bool) → bool | Calculates the boolean value of |
concat_agg(arg1: bytes) → bytes | Concatenates all selected values. |
concat_agg(arg1: string) → string | Concatenates all selected values. |
count(arg1: anyelement) → int | Calculates the number of selected elements. |
count_rows() → int | Calculates the number of rows. |
json_agg(arg1: anyelement) → jsonb | aggregates values as a JSON or JSONB array |
jsonb_agg(arg1: anyelement) → jsonb | aggregates values as a JSON or JSONB array |
max(arg1: bool) → bool | Identifies the maximum selected value. |
max(arg1: bytes) → bytes | Identifies the maximum selected value. |
max(arg1: date) → date | Identifies the maximum selected value. |
max(arg1: decimal) → decimal | Identifies the maximum selected value. |
max(arg1: float) → float | Identifies the maximum selected value. |
max(arg1: inet) → inet | Identifies the maximum selected value. |
max(arg1: int) → int | Identifies the maximum selected value. |
max(arg1: interval) → interval | Identifies the maximum selected value. |
max(arg1: string) → string | Identifies the maximum selected value. |
max(arg1: time) → time | Identifies the maximum selected value. |
max(arg1: timestamp) → timestamp | Identifies the maximum selected value. |
max(arg1: timestamptz) → timestamptz | Identifies the maximum selected value. |
max(arg1: uuid) → uuid | Identifies the maximum selected value. |
max(arg1: jsonb) → jsonb | Identifies the maximum selected value. |
max(arg1: oid) → oid | Identifies the maximum selected value. |
min(arg1: bool) → bool | Identifies the minimum selected value. |
min(arg1: bytes) → bytes | Identifies the minimum selected value. |
min(arg1: date) → date | Identifies the minimum selected value. |
min(arg1: decimal) → decimal | Identifies the minimum selected value. |
min(arg1: float) → float | Identifies the minimum selected value. |
min(arg1: inet) → inet | Identifies the minimum selected value. |
min(arg1: int) → int | Identifies the minimum selected value. |
min(arg1: interval) → interval | Identifies the minimum selected value. |
min(arg1: string) → string | Identifies the minimum selected value. |
min(arg1: time) → time | Identifies the minimum selected value. |
min(arg1: timestamp) → timestamp | Identifies the minimum selected value. |
min(arg1: timestamptz) → timestamptz | Identifies the minimum selected value. |
min(arg1: uuid) → uuid | Identifies the minimum selected value. |
min(arg1: jsonb) → jsonb | Identifies the minimum selected value. |
min(arg1: oid) → oid | Identifies the minimum selected value. |
sqrdiff(arg1: decimal) → decimal | Calculates the sum of squared differences from the mean of the selected values. |
sqrdiff(arg1: float) → float | Calculates the sum of squared differences from the mean of the selected values. |
sqrdiff(arg1: int) → decimal | Calculates the sum of squared differences from the mean of the selected values. |
stddev(arg1: decimal) → decimal | Calculates the standard deviation of the selected values. |
stddev(arg1: float) → float | Calculates the standard deviation of the selected values. |
stddev(arg1: int) → decimal | Calculates the standard deviation of the selected values. |
sum(arg1: decimal) → decimal | Calculates the sum of the selected values. |
sum(arg1: float) → float | Calculates the sum of the selected values. |
sum(arg1: int) → decimal | Calculates the sum of the selected values. |
sum(arg1: interval) → interval | Calculates the sum of the selected values. |
sum_int(arg1: int) → int | Calculates the sum of the selected values. |
variance(arg1: decimal) → decimal | Calculates the variance of the selected values. |
variance(arg1: float) → float | Calculates the variance of the selected values. |
variance(arg1: int) → decimal | Calculates the variance of the selected values. |
xor_agg(arg1: bytes) → bytes | Calculates the bitwise XOR of the selected values. |
xor_agg(arg1: int) → int | Calculates the bitwise XOR of the selected values. |
Operators
The following table lists all CockroachDB operators from highest to lowest precedence, i.e., the order in which they will be evaluated within a statement. Operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right.
Order of Precedence | Operator | Name | Operator Arity |
---|---|---|---|
1 | . |
Member field access operator | binary |
2 | :: |
Type cast | binary |
3 | - |
Unary minus | unary (prefix) |
~ |
Bitwise not | unary (prefix) | |
4 | ^ |
Exponentiation | binary |
5 | * |
Multiplication | binary |
/ |
Division | binary | |
// |
Floor division | binary | |
% |
Modulo | binary | |
6 | + |
Addition | binary |
- |
Subtraction | binary | |
7 | << |
Bitwise left-shift | binary |
>> |
Bitwise right-shift | binary | |
8 | & |
Bitwise AND | binary |
9 | # |
Bitwise XOR | binary |
10 | | |
Bitwise OR | binary |
11 | || |
Concatenation | binary |
< ANY , SOME , ALL |
Multi-valued "less than" comparison | binary | |
> ANY , SOME , ALL |
Multi-valued "greater than" comparison | binary | |
= ANY , SOME , ALL |
Multi-valued "equal" comparison | binary | |
<= ANY , SOME , ALL |
Multi-valued "less than or equal" comparison | binary | |
>= ANY , SOME , ALL |
Multi-valued "greater than or equal" comparison | binary | |
<> ANY / != ANY , <> SOME / != SOME , <> ALL / != ALL |
Multi-valued "not equal" comparison | binary | |
[NOT] LIKE ANY , [NOT] LIKE SOME , [NOT] LIKE ALL |
Multi-valued LIKE comparison |
binary | |
[NOT] ILIKE ANY , [NOT] ILIKE SOME , [NOT] ILIKE ALL |
Multi-valued ILIKE comparison |
binary | |
12 | [NOT] BETWEEN |
Value is [not] within the range specified | binary |
[NOT] BETWEEN SYMMETRIC |
Like [NOT] BETWEEN , but in non-sorted order. For example, whereas a BETWEEN b AND c means b <= a <= c , a BETWEEN SYMMETRIC b AND c means (b <= a <= c) OR (c <= a <= b) . |
binary | |
[NOT] IN |
Value is [not] in the set of values specified | binary | |
[NOT] LIKE |
Matches [or not] LIKE expression, case sensitive | binary | |
[NOT] ILIKE |
Matches [or not] LIKE expression, case insensitive | binary | |
[NOT] SIMILAR |
Matches [or not] SIMILAR TO regular expression | binary | |
~ |
Matches regular expression, case sensitive | binary | |
!~ |
Does not match regular expression, case sensitive | binary | |
~* |
Matches regular expression, case insensitive | binary | |
!~* |
Does not match regular expression, case insensitive | binary | |
13 | = |
Equal | binary |
< |
Less than | binary | |
> |
Greater than | binary | |
<= |
Less than or equal to | binary | |
>= |
Greater than or equal to | binary | |
!= , <> |
Not equal | binary | |
14 | IS [DISTINCT FROM] |
Equal, considering NULL as value |
binary |
IS NOT [DISTINCT FROM] |
a IS NOT b equivalent to NOT (a IS b) |
binary | |
ISNULL , IS UNKNOWN , NOTNULL , IS NOT UNKNOWN |
Equivalent to IS NULL / IS NOT NULL |
unary (postfix) | |
IS NAN , IS NOT NAN |
Comparison with the floating-point NaN value | unary (postfix) | |
IS OF(...) |
Type predicate | unary (postfix) | |
15 | NOT |
Logical NOT | unary |
16 | AND |
Logical AND | binary |
17 | OR |
Logical OR | binary |
Supported Operations
# | Return |
int # int | int |
#> | Return |
jsonb #> string[] | jsonb |
#>> | Return |
jsonb #>> string[] | string |
% | Return |
decimal % decimal | decimal |
decimal % int | decimal |
float % float | float |
int % decimal | decimal |
int % int | int |
& | Return |
inet & inet | inet |
int & int | int |
- | Return |
- decimal | decimal |
- float | float |
- int | int |
- interval | interval |
date - date | int |
date - int | date |
date - interval | timestamptz |
date - time | timestamp |
decimal - decimal | decimal |
decimal - int | decimal |
float - float | float |
inet - inet | int |
inet - int | inet |
int - decimal | decimal |
int - int | int |
interval - interval | interval |
jsonb - int | jsonb |
jsonb - string | jsonb |
time - interval | time |
time - time | interval |
timestamp - interval | timestamp |
timestamp - timestamp | interval |
timestamp - timestamptz | interval |
timestamptz - interval | timestamptz |
timestamptz - timestamp | interval |
timestamptz - timestamptz | interval |
-> | Return |
jsonb -> int | jsonb |
jsonb -> string | jsonb |
->> | Return |
jsonb ->> int | string |
jsonb ->> string | string |
/ | Return |
decimal / decimal | decimal |
decimal / int | decimal |
float / float | float |
int / decimal | decimal |
int / int | decimal |
interval / float | interval |
interval / int | interval |
// | Return |
decimal // decimal | decimal |
decimal // int | decimal |
float // float | float |
int // decimal | decimal |
int // int | int |
< | Return |
bool < bool | bool |
bytes < bytes | bool |
collatedstring < collatedstring | bool |
date < date | bool |
date < timestamp | bool |
date < timestamptz | bool |
decimal < decimal | bool |
decimal < float | bool |
decimal < int | bool |
float < decimal | bool |
float < float | bool |
float < int | bool |
inet < inet | bool |
int < decimal | bool |
int < float | bool |
int < int | bool |
interval < interval | bool |
oid < oid | bool |
string < string | bool |
time < time | bool |
timestamp < date | bool |
timestamp < timestamp | bool |
timestamp < timestamptz | bool |
timestamptz < date | bool |
timestamptz < timestamp | bool |
timestamptz < timestamptz | bool |
tuple < tuple | bool |
uuid < uuid | bool |
<< | Return |
inet << inet | bool |
int << int | int |
<= | Return |
bool <= bool | bool |
bytes <= bytes | bool |
collatedstring <= collatedstring | bool |
date <= date | bool |
date <= timestamp | bool |
date <= timestamptz | bool |
decimal <= decimal | bool |
decimal <= float | bool |
decimal <= int | bool |
float <= decimal | bool |
float <= float | bool |
float <= int | bool |
inet <= inet | bool |
int <= decimal | bool |
int <= float | bool |
int <= int | bool |
interval <= interval | bool |
oid <= oid | bool |
string <= string | bool |
time <= time | bool |
timestamp <= date | bool |
timestamp <= timestamp | bool |
timestamp <= timestamptz | bool |
timestamptz <= date | bool |
timestamptz <= timestamp | bool |
timestamptz <= timestamptz | bool |
tuple <= tuple | bool |
uuid <= uuid | bool |
<@ | Return |
jsonb <@ jsonb | bool |
= | Return |
bool = bool | bool |
bool[] = bool[] | bool |
bytes = bytes | bool |
bytes[] = bytes[] | bool |
collatedstring = collatedstring | bool |
date = date | bool |
date = timestamp | bool |
date = timestamptz | bool |
date[] = date[] | bool |
decimal = decimal | bool |
decimal = float | bool |
decimal = int | bool |
decimal[] = decimal[] | bool |
float = decimal | bool |
float = float | bool |
float = int | bool |
float[] = float[] | bool |
inet = inet | bool |
inet[] = inet[] | bool |
int = decimal | bool |
int = float | bool |
int = int | bool |
int[] = int[] | bool |
interval = interval | bool |
interval[] = interval[] | bool |
jsonb = jsonb | bool |
oid = oid | bool |
string = string | bool |
string[] = string[] | bool |
time = time | bool |
time[] = time[] | bool |
timestamp = date | bool |
timestamp = timestamp | bool |
timestamp = timestamptz | bool |
timestamp[] = timestamp[] | bool |
timestamptz = date | bool |
timestamptz = timestamp | bool |
timestamptz = timestamptz | bool |
timestamptz = timestamptz | bool |
tuple = tuple | bool |
uuid = uuid | bool |
uuid[] = uuid[] | bool |
>> | Return |
inet >> inet | bool |
int >> int | int |
? | Return |
jsonb ? string | bool |
?& | Return |
jsonb ?& string[] | bool |
?| | Return |
jsonb ?| string[] | bool |
@> | Return |
jsonb @> jsonb | bool |
ILIKE | Return |
string ILIKE string | bool |
IN | Return |
bool IN tuple | bool |
bytes IN tuple | bool |
collatedstring IN tuple | bool |
date IN tuple | bool |
decimal IN tuple | bool |
float IN tuple | bool |
inet IN tuple | bool |
int IN tuple | bool |
interval IN tuple | bool |
jsonb IN tuple | bool |
oid IN tuple | bool |
string IN tuple | bool |
time IN tuple | bool |
timestamp IN tuple | bool |
timestamptz IN tuple | bool |
tuple IN tuple | bool |
uuid IN tuple | bool |
IS NOT DISTINCT FROM | Return |
bool IS NOT DISTINCT FROM bool | bool |
bool[] IS NOT DISTINCT FROM bool[] | bool |
bytes IS NOT DISTINCT FROM bytes | bool |
bytes[] IS NOT DISTINCT FROM bytes[] | bool |
collatedstring IS NOT DISTINCT FROM collatedstring | bool |
date IS NOT DISTINCT FROM date | bool |
date IS NOT DISTINCT FROM timestamp | bool |
date IS NOT DISTINCT FROM timestamptz | bool |
date[] IS NOT DISTINCT FROM date[] | bool |
decimal IS NOT DISTINCT FROM decimal | bool |
decimal IS NOT DISTINCT FROM float | bool |
decimal IS NOT DISTINCT FROM int | bool |
decimal[] IS NOT DISTINCT FROM decimal[] | bool |
float IS NOT DISTINCT FROM decimal | bool |
float IS NOT DISTINCT FROM float | bool |
float IS NOT DISTINCT FROM int | bool |
float[] IS NOT DISTINCT FROM float[] | bool |
inet IS NOT DISTINCT FROM inet | bool |
inet[] IS NOT DISTINCT FROM inet[] | bool |
int IS NOT DISTINCT FROM decimal | bool |
int IS NOT DISTINCT FROM float | bool |
int IS NOT DISTINCT FROM int | bool |
int[] IS NOT DISTINCT FROM int[] | bool |
interval IS NOT DISTINCT FROM interval | bool |
interval[] IS NOT DISTINCT FROM interval[] | bool |
jsonb IS NOT DISTINCT FROM jsonb | bool |
oid IS NOT DISTINCT FROM oid | bool |
string IS NOT DISTINCT FROM string | bool |
string[] IS NOT DISTINCT FROM string[] | bool |
time IS NOT DISTINCT FROM time | bool |
time[] IS NOT DISTINCT FROM time[] | bool |
timestamp IS NOT DISTINCT FROM date | bool |
timestamp IS NOT DISTINCT FROM timestamp | bool |
timestamp IS NOT DISTINCT FROM timestamptz | bool |
timestamp[] IS NOT DISTINCT FROM timestamp[] | bool |
timestamptz IS NOT DISTINCT FROM date | bool |
timestamptz IS NOT DISTINCT FROM timestamp | bool |
timestamptz IS NOT DISTINCT FROM timestamptz | bool |
timestamptz IS NOT DISTINCT FROM timestamptz | bool |
tuple IS NOT DISTINCT FROM tuple | bool |
unknown IS NOT DISTINCT FROM unknown | bool |
uuid IS NOT DISTINCT FROM uuid | bool |
uuid[] IS NOT DISTINCT FROM uuid[] | bool |
LIKE | Return |
string LIKE string | bool |
SIMILAR TO | Return |
string SIMILAR TO string | bool |
^ | Return |
decimal ^ decimal | decimal |
decimal ^ int | decimal |
float ^ float | float |
int ^ decimal | decimal |
int ^ int | int |
| | Return |
inet | inet | inet |
int | int | int |
~ | Return |
~ inet | inet |
~ int | int |
string ~ string | bool |
~* | Return |
string ~* string | bool |