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 |
---|---|

`AT TIME ZONE` |
`timezone()` |

`CURRENT_CATALOG` |
`current_catalog()` |

`COLLATION FOR` |
`pg_collation_for()` |

`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()` |

## Function volatility

A function's *volatility* is a promise to the optimizer about the behavior of the function.

Type | Description | Examples |
---|---|---|

Volatile | The function can modify the state of the database and is not guaranteed to return the same results given the same arguments in any context. | `random` , `crdb_internal.force_error` , `nextval` , `now` |

Stable | The function is guaranteed to return the same results given the same arguments whenever it is evaluated within the same statement. The optimizer can optimize multiple calls of the function to a single call. | `current_timestamp` , `current_date` |

Immutable | The function does not depend on configuration settings and is guaranteed to return the same results given the same arguments in any context. The optimizer can pre-evaluate the function when a query calls it with constant arguments. | `log` , `from_json` |

Leakproof | The function does not depend on configuration settings and is guaranteed to return the same results given the same arguments in any context. In addition, no information about the arguments is conveyed except via the return value. Any function that might throw an error depending on the values of its arguments is not leakproof. Leakproof is strictly stronger than Immutable. | Integer comparison |

## 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

### Array functions

Function → Returns | Description | Volatility |
---|---|---|

`array_append(array: bool[], elem: bool) → bool[]` | Appends | Immutable |

`array_append(array: bytes[], elem: bytes) → bytes[]` | Appends | Immutable |

`array_append(array: date[], elem: date) → date[]` | Appends | Immutable |

`array_append(array: decimal[], elem: decimal) → decimal[]` | Appends | Immutable |

`array_append(array: float[], elem: float) → float[]` | Appends | Immutable |

`array_append(array: inet[], elem: inet) → inet[]` | Appends | Immutable |

`array_append(array: int[], elem: int) → int[]` | Appends | Immutable |

`array_append(array: interval[], elem: interval) → interval[]` | Appends | Immutable |

`array_append(array: string[], elem: string) → string[]` | Appends | Immutable |

`array_append(array: time[], elem: time) → time[]` | Appends | Immutable |

`array_append(array: timestamp[], elem: timestamp) → timestamp[]` | Appends | Immutable |

`array_append(array: timestamptz[], elem: timestamptz) → timestamptz[]` | Appends | Immutable |

`array_append(array: uuid[], elem: uuid) → uuid[]` | Appends | Immutable |

`array_append(array: anyenum[], elem: anyenum) → anyenum[]` | Appends | Immutable |

`array_append(array: box2d[], elem: box2d) → box2d[]` | Appends | Immutable |

`array_append(array: geography[], elem: geography) → geography[]` | Appends | Immutable |

`array_append(array: geometry[], elem: geometry) → geometry[]` | Appends | Immutable |

`array_append(array: jsonb[], elem: jsonb) → jsonb[]` | Appends | Immutable |

`array_append(array: oid[], elem: oid) → oid[]` | Appends | Immutable |

`array_append(array: timetz[], elem: timetz) → timetz[]` | Appends | Immutable |

`array_append(array: tuple[], elem: tuple) → tuple[]` | Appends | Immutable |

`array_append(array: varbit[], elem: varbit) → varbit[]` | Appends | Immutable |

`array_cat(left: bool[], right: bool[]) → bool[]` | Appends two arrays. | Immutable |

`array_cat(left: bytes[], right: bytes[]) → bytes[]` | Appends two arrays. | Immutable |

`array_cat(left: date[], right: date[]) → date[]` | Appends two arrays. | Immutable |

`array_cat(left: decimal[], right: decimal[]) → decimal[]` | Appends two arrays. | Immutable |

`array_cat(left: float[], right: float[]) → float[]` | Appends two arrays. | Immutable |

`array_cat(left: inet[], right: inet[]) → inet[]` | Appends two arrays. | Immutable |

`array_cat(left: int[], right: int[]) → int[]` | Appends two arrays. | Immutable |

`array_cat(left: interval[], right: interval[]) → interval[]` | Appends two arrays. | Immutable |

`array_cat(left: string[], right: string[]) → string[]` | Appends two arrays. | Immutable |

`array_cat(left: time[], right: time[]) → time[]` | Appends two arrays. | Immutable |

`array_cat(left: timestamp[], right: timestamp[]) → timestamp[]` | Appends two arrays. | Immutable |

`array_cat(left: timestamptz[], right: timestamptz[]) → timestamptz[]` | Appends two arrays. | Immutable |

`array_cat(left: uuid[], right: uuid[]) → uuid[]` | Appends two arrays. | Immutable |

`array_cat(left: anyenum[], right: anyenum[]) → anyenum[]` | Appends two arrays. | Immutable |

`array_cat(left: box2d[], right: box2d[]) → box2d[]` | Appends two arrays. | Immutable |

`array_cat(left: geography[], right: geography[]) → geography[]` | Appends two arrays. | Immutable |

`array_cat(left: geometry[], right: geometry[]) → geometry[]` | Appends two arrays. | Immutable |

`array_cat(left: jsonb[], right: jsonb[]) → jsonb[]` | Appends two arrays. | Immutable |

`array_cat(left: oid[], right: oid[]) → oid[]` | Appends two arrays. | Immutable |

`array_cat(left: timetz[], right: timetz[]) → timetz[]` | Appends two arrays. | Immutable |

`array_cat(left: tuple[], right: tuple[]) → tuple[]` | Appends two arrays. | Immutable |

`array_cat(left: varbit[], right: varbit[]) → varbit[]` | Appends two arrays. | Immutable |

`array_length(input: anyelement[], array_dimension: int) → int` | Calculates the length of | Immutable |

`array_lower(input: anyelement[], array_dimension: int) → int` | Calculates the minimum value of | Immutable |

`array_position(array: bool[], elem: bool) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: bytes[], elem: bytes) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: date[], elem: date) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: decimal[], elem: decimal) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: float[], elem: float) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: inet[], elem: inet) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: int[], elem: int) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: interval[], elem: interval) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: string[], elem: string) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: time[], elem: time) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: timestamp[], elem: timestamp) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: timestamptz[], elem: timestamptz) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: uuid[], elem: uuid) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: anyenum[], elem: anyenum) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: box2d[], elem: box2d) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: geography[], elem: geography) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: geometry[], elem: geometry) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: jsonb[], elem: jsonb) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: oid[], elem: oid) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: timetz[], elem: timetz) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: tuple[], elem: tuple) → int` | Return the index of the first occurrence of | Immutable |

`array_position(array: varbit[], elem: varbit) → int` | Return the index of the first occurrence of | Immutable |

`array_positions(array: bool[], elem: bool) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: bytes[], elem: bytes) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: date[], elem: date) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: decimal[], elem: decimal) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: float[], elem: float) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: inet[], elem: inet) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: int[], elem: int) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: interval[], elem: interval) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: string[], elem: string) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: time[], elem: time) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: timestamp[], elem: timestamp) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: timestamptz[], elem: timestamptz) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: uuid[], elem: uuid) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: anyenum[], elem: anyenum) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: box2d[], elem: box2d) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: geography[], elem: geography) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: geometry[], elem: geometry) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: jsonb[], elem: jsonb) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: oid[], elem: oid) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: timetz[], elem: timetz) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: tuple[], elem: tuple) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_positions(array: varbit[], elem: varbit) → int[]` | Returns and array of indexes of all occurrences of | Immutable |

`array_prepend(elem: bool, array: bool[]) → bool[]` | Prepends | Immutable |

`array_prepend(elem: bytes, array: bytes[]) → bytes[]` | Prepends | Immutable |

`array_prepend(elem: date, array: date[]) → date[]` | Prepends | Immutable |

`array_prepend(elem: decimal, array: decimal[]) → decimal[]` | Prepends | Immutable |

`array_prepend(elem: float, array: float[]) → float[]` | Prepends | Immutable |

`array_prepend(elem: inet, array: inet[]) → inet[]` | Prepends | Immutable |

`array_prepend(elem: int, array: int[]) → int[]` | Prepends | Immutable |

`array_prepend(elem: interval, array: interval[]) → interval[]` | Prepends | Immutable |

`array_prepend(elem: string, array: string[]) → string[]` | Prepends | Immutable |

`array_prepend(elem: time, array: time[]) → time[]` | Prepends | Immutable |

`array_prepend(elem: timestamp, array: timestamp[]) → timestamp[]` | Prepends | Immutable |

`array_prepend(elem: timestamptz, array: timestamptz[]) → timestamptz[]` | Prepends | Immutable |

`array_prepend(elem: uuid, array: uuid[]) → uuid[]` | Prepends | Immutable |

`array_prepend(elem: anyenum, array: anyenum[]) → anyenum[]` | Prepends | Immutable |

`array_prepend(elem: box2d, array: box2d[]) → box2d[]` | Prepends | Immutable |

`array_prepend(elem: geography, array: geography[]) → geography[]` | Prepends | Immutable |

`array_prepend(elem: geometry, array: geometry[]) → geometry[]` | Prepends | Immutable |

`array_prepend(elem: jsonb, array: jsonb[]) → jsonb[]` | Prepends | Immutable |

`array_prepend(elem: oid, array: oid[]) → oid[]` | Prepends | Immutable |

`array_prepend(elem: timetz, array: timetz[]) → timetz[]` | Prepends | Immutable |

`array_prepend(elem: tuple, array: tuple[]) → tuple[]` | Prepends | Immutable |

`array_prepend(elem: varbit, array: varbit[]) → varbit[]` | Prepends | Immutable |

`array_remove(array: bool[], elem: bool) → bool[]` | Remove from | Immutable |

`array_remove(array: bytes[], elem: bytes) → bytes[]` | Remove from | Immutable |

`array_remove(array: date[], elem: date) → date[]` | Remove from | Immutable |

`array_remove(array: decimal[], elem: decimal) → decimal[]` | Remove from | Immutable |

`array_remove(array: float[], elem: float) → float[]` | Remove from | Immutable |

`array_remove(array: inet[], elem: inet) → inet[]` | Remove from | Immutable |

`array_remove(array: int[], elem: int) → int[]` | Remove from | Immutable |

`array_remove(array: interval[], elem: interval) → interval[]` | Remove from | Immutable |

`array_remove(array: string[], elem: string) → string[]` | Remove from | Immutable |

`array_remove(array: time[], elem: time) → time[]` | Remove from | Immutable |

`array_remove(array: timestamp[], elem: timestamp) → timestamp[]` | Remove from | Immutable |

`array_remove(array: timestamptz[], elem: timestamptz) → timestamptz[]` | Remove from | Immutable |

`array_remove(array: uuid[], elem: uuid) → uuid[]` | Remove from | Immutable |

`array_remove(array: anyenum[], elem: anyenum) → anyenum[]` | Remove from | Immutable |

`array_remove(array: box2d[], elem: box2d) → box2d[]` | Remove from | Immutable |

`array_remove(array: geography[], elem: geography) → geography[]` | Remove from | Immutable |

`array_remove(array: geometry[], elem: geometry) → geometry[]` | Remove from | Immutable |

`array_remove(array: jsonb[], elem: jsonb) → jsonb[]` | Remove from | Immutable |

`array_remove(array: oid[], elem: oid) → oid[]` | Remove from | Immutable |

`array_remove(array: timetz[], elem: timetz) → timetz[]` | Remove from | Immutable |

`array_remove(array: tuple[], elem: tuple) → tuple[]` | Remove from | Immutable |

`array_remove(array: varbit[], elem: varbit) → varbit[]` | Remove from | Immutable |

`array_replace(array: bool[], toreplace: bool, replacewith: bool) → bool[]` | Replace all occurrences of | Immutable |

`array_replace(array: bytes[], toreplace: bytes, replacewith: bytes) → bytes[]` | Replace all occurrences of | Immutable |

`array_replace(array: date[], toreplace: date, replacewith: date) → date[]` | Replace all occurrences of | Immutable |

`array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) → decimal[]` | Replace all occurrences of | Immutable |

`array_replace(array: float[], toreplace: float, replacewith: float) → float[]` | Replace all occurrences of | Immutable |

`array_replace(array: inet[], toreplace: inet, replacewith: inet) → inet[]` | Replace all occurrences of | Immutable |

`array_replace(array: int[], toreplace: int, replacewith: int) → int[]` | Replace all occurrences of | Immutable |

`array_replace(array: interval[], toreplace: interval, replacewith: interval) → interval[]` | Replace all occurrences of | Immutable |

`array_replace(array: string[], toreplace: string, replacewith: string) → string[]` | Replace all occurrences of | Immutable |

`array_replace(array: time[], toreplace: time, replacewith: time) → time[]` | Replace all occurrences of | Immutable |

`array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp) → timestamp[]` | Replace all occurrences of | Immutable |

`array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) → timestamptz[]` | Replace all occurrences of | Immutable |

`array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) → uuid[]` | Replace all occurrences of | Immutable |

`array_replace(array: anyenum[], toreplace: anyenum, replacewith: anyenum) → anyenum[]` | Replace all occurrences of | Immutable |

`array_replace(array: box2d[], toreplace: box2d, replacewith: box2d) → box2d[]` | Replace all occurrences of | Immutable |

`array_replace(array: geography[], toreplace: geography, replacewith: geography) → geography[]` | Replace all occurrences of | Immutable |

`array_replace(array: geometry[], toreplace: geometry, replacewith: geometry) → geometry[]` | Replace all occurrences of | Immutable |

`array_replace(array: jsonb[], toreplace: jsonb, replacewith: jsonb) → jsonb[]` | Replace all occurrences of | Immutable |

`array_replace(array: oid[], toreplace: oid, replacewith: oid) → oid[]` | Replace all occurrences of | Immutable |

`array_replace(array: timetz[], toreplace: timetz, replacewith: timetz) → timetz[]` | Replace all occurrences of | Immutable |

`array_replace(array: tuple[], toreplace: tuple, replacewith: tuple) → tuple[]` | Replace all occurrences of | Immutable |

`array_replace(array: varbit[], toreplace: varbit, replacewith: varbit) → varbit[]` | Replace all occurrences of | Immutable |

`array_to_string(input: anyelement[], delim: string) → string` | Join an array into a string with a delimiter. | Stable |

`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. | Stable |

`array_upper(input: anyelement[], array_dimension: int) → int` | Calculates the maximum value of | Immutable |

`cardinality(input: anyelement[]) → int` | Calculates the number of elements contained in | Immutable |

`crdb_internal.merge_statement_stats(input: jsonb[]) → jsonb` | Merge an array of roachpb.StatementStatistics into a single JSONB object | Immutable |

`crdb_internal.merge_stats_metadata(input: jsonb[]) → jsonb` | Merge an array of StmtStatsMetadata into a single JSONB object | Immutable |

`crdb_internal.merge_transaction_stats(input: jsonb[]) → jsonb` | Merge an array of roachpb.TransactionStatistics into a single JSONB object | Immutable |

`string_to_array(str: string, delimiter: string) → string[]` | Split a string into components on a delimiter. | Immutable |

`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. | Immutable |

### BOOL functions

Function → Returns | Description | Volatility |
---|---|---|

`ilike_escape(unescaped: string, pattern: string, escape: string) → bool` | Matches case insensetively | Immutable |

`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. | Immutable |

`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. | Immutable |

`inet_same_family(val: inet, val: inet) → bool` | Checks if two IP addresses are of the same IP family. | Immutable |

`like_escape(unescaped: string, pattern: string, escape: string) → bool` | Matches | Immutable |

`not_ilike_escape(unescaped: string, pattern: string, escape: string) → bool` | Checks whether | Immutable |

`not_like_escape(unescaped: string, pattern: string, escape: string) → bool` | Checks whether | Immutable |

`not_similar_to_escape(unescaped: string, pattern: string, escape: string) → bool` | Checks whether | Immutable |

### Comparison functions

Function → Returns | Description | Volatility |
---|---|---|

`greatest(anyelement...) → anyelement` | Returns the element with the greatest value. | Immutable |

`least(anyelement...) → anyelement` | Returns the element with the lowest value. | Immutable |

`num_nonnulls(anyelement...) → int` | Returns the number of nonnull arguments. | Immutable |

`num_nulls(anyelement...) → int` | Returns the number of null arguments. | Immutable |

### Cryptographic functions

Function → Returns | Description | Volatility |
---|---|---|

`digest(data: bytes, type: string) → bytes` | Computes a binary hash of the given | Immutable |

`digest(data: string, type: string) → bytes` | Computes a binary hash of the given | Leakproof |

`hmac(data: bytes, key: bytes, type: string) → bytes` | Calculates hashed MAC for | Immutable |

`hmac(data: string, key: string, type: string) → bytes` | Calculates hashed MAC for | Leakproof |

### DECIMAL functions

Function → Returns | Description | Volatility |
---|---|---|

`hlc_to_timestamp(hlc: decimal) → timestamptz` | Returns a TimestampTZ representation of a CockroachDB HLC in decimal form. Note that a TimestampTZ has less precision than a CockroachDB HLC. It is intended as a convenience function to display HLCs in a print-friendly form. Use the decimal value if you rely on the HLC for accuracy. | Immutable |

### Date and time functions

Function → Returns | Description | Volatility |
---|---|---|

`age(end: timestamptz, begin: timestamptz) → interval` | Calculates the interval between Note this may not be an accurate time span since years and months are normalized from days, and years and months are out of context. To avoid normalizing days into months and years, use the timestamptz subtraction operator. | Immutable |

`age(val: timestamptz) → interval` | Calculates the interval between Note this may not be an accurate time span since years and months are normalized
from days, and years and months are out of context. To avoid normalizing days into
months and years, use | Stable |

`clock_timestamp() → timestamp` | Returns the current system time on one of the cluster nodes. | Volatile |

`clock_timestamp() → timestamptz` | Returns the current system time on one of the cluster nodes. | Volatile |

`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. | Stable |

`current_timestamp() → date` | 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. | Stable |

`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. | Stable |

`current_timestamp() → timestamptz` | Returns the time of the current transaction. This function is the preferred overload and will be evaluated by default. | Stable |

`current_timestamp(precision: int) → date` | Returns the time of the current transaction. | Stable |

`current_timestamp(precision: int) → timestamp` | Returns the time of the current transaction. | Stable |

`current_timestamp(precision: int) → timestamptz` | Returns the time of the current transaction. This function is the preferred overload and will be evaluated by default. | Stable |

`date_part(element: string, input: date) → float` | Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch | Immutable |

`date_part(element: string, input: interval) → float` | Extracts Compatible elements: millennium, century, decade, year, month, day, hour, minute, second, millisecond, microsecond, epoch | Immutable |

`date_part(element: string, input: time) → float` | Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch | Immutable |

`date_part(element: string, input: timestamp) → float` | Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch | Immutable |

`date_part(element: string, input: timestamptz) → float` | Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute | Stable |

`date_part(element: string, input: timetz) → float` | Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute | Immutable |

`date_trunc(element: string, input: date) → timestamptz` | Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. | Stable |

`date_trunc(element: string, input: interval) → interval` | Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. | Stable |

`date_trunc(element: string, input: time) → interval` | Truncates Compatible elements: hour, minute, second, millisecond, microsecond. | Immutable |

`date_trunc(element: string, input: timestamp) → timestamp` | Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. | Immutable |

`date_trunc(element: string, input: timestamptz) → timestamptz` |
| Stable |

`experimental_follower_read_timestamp() → timestamptz` | Same as follower_read_timestamp. This name is deprecated. | Volatile |

`experimental_strftime(input: date, extract_format: string) → string` | From | Immutable |

`experimental_strftime(input: timestamp, extract_format: string) → string` | From | Immutable |

`experimental_strftime(input: timestamptz, extract_format: string) → string` | From | Immutable |

`experimental_strptime(input: string, format: string) → timestamptz` | Returns | Immutable |

`extract(element: string, input: date) → float` | Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch | Immutable |

`extract(element: string, input: interval) → float` | Extracts Compatible elements: millennium, century, decade, year, month, day, hour, minute, second, millisecond, microsecond, epoch | Immutable |

`extract(element: string, input: time) → float` | Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch | Immutable |

`extract(element: string, input: timestamp) → float` | Extracts | Immutable |

`extract(element: string, input: timestamptz) → float` | Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute | Stable |

`extract(element: string, input: timetz) → float` | Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute | Immutable |

`extract_duration(element: string, input: interval) → int` | Extracts | Immutable |

`follower_read_timestamp() → timestamptz` | Returns a timestamp which is very likely to be safe to perform against a follower replica. This function is intended to be used with an AS OF SYSTEM TIME clause to perform historical reads against a time which is recent but sufficiently old for reads to be performed against the closest replica as opposed to the currently leaseholder for a given range. Note that this function requires an enterprise license on a CCL distribution to return a result that is less likely the closest replica. It is otherwise hardcoded as -4.8s from the statement time, which may not result in reading from the nearest replica. | Volatile |

`localtimestamp() → date` | Returns the time of the current transaction. | Stable |

`localtimestamp() → timestamp` | Returns the time of the current transaction. This function is the preferred overload and will be evaluated by default. | Stable |

`localtimestamp() → timestamptz` | Returns the time of the current transaction. | Stable |

`localtimestamp(precision: int) → date` | Returns the time of the current transaction. | Stable |

`localtimestamp(precision: int) → timestamp` | Returns the time of the current transaction. This function is the preferred overload and will be evaluated by default. | Stable |

`localtimestamp(precision: int) → timestamptz` | Returns the time of the current transaction. | Stable |

`now() → date` | Returns the time of the current transaction. | Stable |

`now() → timestamp` | Returns the time of the current transaction. | Stable |

`now() → timestamptz` | Returns the time of the current transaction. This function is the preferred overload and will be evaluated by default. | Stable |

`statement_timestamp() → timestamp` | Returns the start time of the current statement. | Stable |

`statement_timestamp() → timestamptz` | Returns the start time of the current statement. | Stable |

`timeofday() → string` | Returns the current system time on one of the cluster nodes as a string. | Stable |

`timezone(timezone: string, time: time) → timetz` | Treat given time without time zone as located in the specified time zone. | Stable |

`timezone(timezone: string, timestamp: timestamp) → timestamptz` | Treat given time stamp without time zone as located in the specified time zone. | Immutable |

`timezone(timezone: string, timestamptz: timestamptz) → timestamp` | Convert given time stamp with time zone to the new time zone, with no time zone designation. | Immutable |

`timezone(timezone: string, timestamptz_string: string) → timestamp` | Convert given time stamp with time zone to the new time zone, with no time zone designation. | Stable |

`timezone(timezone: string, timetz: timetz) → timetz` | Convert given time with time zone to the new time zone. | Stable |

`to_char(date: date) → string` | Convert an date to a string assuming the ISO, MDY DateStyle. | Immutable |

`to_char(interval: interval) → string` | Convert an interval to a string assuming the Postgres IntervalStyle. | Immutable |

`to_char(timestamp: timestamp) → string` | Convert an timestamp to a string assuming the ISO, MDY DateStyle. | Immutable |

`transaction_timestamp() → date` | Returns the time of the current transaction. | Stable |

`transaction_timestamp() → timestamp` | Returns the time of the current transaction. | Stable |

`transaction_timestamp() → timestamptz` | Returns the time of the current transaction. This function is the preferred overload and will be evaluated by default. | Stable |

`with_max_staleness(max_staleness: interval) → timestamptz` | When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp within the staleness bound that allows execution of the reads at the nearest available replica without blocking. Note this function requires an enterprise license on a CCL distribution. | Volatile |

`with_max_staleness(max_staleness: interval, nearest_only: bool) → timestamptz` | When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp within the staleness bound that allows execution of the reads at the nearest available replica without blocking. If nearest_only is set to true, reads that cannot be served using the nearest available replica will error. Note this function requires an enterprise license on a CCL distribution. | Volatile |

`with_min_timestamp(min_timestamp: timestamptz) → timestamptz` | When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp before the min_timestamp that allows execution of the reads at the nearest available replica without blocking. Note this function requires an enterprise license on a CCL distribution. | Volatile |

`with_min_timestamp(min_timestamp: timestamptz, nearest_only: bool) → timestamptz` | When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp before the min_timestamp that allows execution of the reads at the nearest available replica without blocking. If nearest_only is set to true, reads that cannot be served using the nearest available replica will error. Note this function requires an enterprise license on a CCL distribution. | Volatile |

### Enum functions

### FLOAT functions

Function → Returns | Description | Volatility |
---|---|---|

`abs(val: decimal) → decimal` | Calculates the absolute value of | Immutable |

`abs(val: float) → float` | Calculates the absolute value of | Immutable |

`abs(val: int) → int` | Calculates the absolute value of | Immutable |

`acos(val: float) → float` | Calculates the inverse cosine of | Immutable |

`acosd(val: float) → float` | Calculates the inverse cosine of | Immutable |

`acosh(val: float) → float` | Calculates the inverse hyperbolic cosine of | Immutable |

`asin(val: float) → float` | Calculates the inverse sine of | Immutable |

`asind(val: float) → float` | Calculates the inverse sine of | Immutable |

`asinh(val: float) → float` | Calculates the inverse hyperbolic sine of | Immutable |

`atan(val: float) → float` | Calculates the inverse tangent of | Immutable |

`atan2(x: float, y: float) → float` | Calculates the inverse tangent of | Immutable |

`atan2d(x: float, y: float) → float` | Calculates the inverse tangent of | Immutable |

`atand(val: float) → float` | Calculates the inverse tangent of | Immutable |

`atanh(val: float) → float` | Calculates the inverse hyperbolic tangent of | Immutable |

`cbrt(val: decimal) → decimal` | Calculates the cube root (∛) of | Immutable |

`cbrt(val: float) → float` | Calculates the cube root (∛) of | Immutable |

`ceil(val: decimal) → decimal` | Calculates the smallest integer not smaller than | Immutable |

`ceil(val: float) → float` | Calculates the smallest integer not smaller than | Immutable |

`ceil(val: int) → float` | Calculates the smallest integer not smaller than | Immutable |

`ceiling(val: decimal) → decimal` | Calculates the smallest integer not smaller than | Immutable |

`ceiling(val: float) → float` | Calculates the smallest integer not smaller than | Immutable |

`ceiling(val: int) → float` | Calculates the smallest integer not smaller than | Immutable |

`cos(val: float) → float` | Calculates the cosine of | Immutable |

`cosd(val: float) → float` | Calculates the cosine of | Immutable |

`cosh(val: float) → float` | Calculates the hyperbolic cosine of | Immutable |

`cot(val: float) → float` | Calculates the cotangent of | Immutable |

`cotd(val: float) → float` | Calculates the cotangent of | Immutable |

`degrees(val: float) → float` | Converts | Immutable |

`div(x: decimal, y: decimal) → decimal` | Calculates the integer quotient of | Immutable |

`div(x: float, y: float) → float` | Calculates the integer quotient of | Immutable |

`div(x: int, y: int) → int` | Calculates the integer quotient of | Immutable |

`exp(val: decimal) → decimal` | Calculates | Immutable |

`exp(val: float) → float` | Calculates | Immutable |

`floor(val: decimal) → decimal` | Calculates the largest integer not greater than | Immutable |

`floor(val: float) → float` | Calculates the largest integer not greater than | Immutable |

`floor(val: int) → float` | Calculates the largest integer not greater than | Immutable |

`isnan(val: decimal) → bool` | Returns true if | Immutable |

`isnan(val: float) → bool` | Returns true if | Immutable |

`ln(val: decimal) → decimal` | Calculates the natural log of | Immutable |

`ln(val: float) → float` | Calculates the natural log of | Immutable |

`log(b: decimal, x: decimal) → decimal` | Calculates the base | Immutable |

`log(b: float, x: float) → float` | Calculates the base | Immutable |

`log(val: decimal) → decimal` | Calculates the base 10 log of | Immutable |

`log(val: float) → float` | Calculates the base 10 log of | Immutable |

`mod(x: decimal, y: decimal) → decimal` | Calculates | Immutable |

`mod(x: float, y: float) → float` | Calculates | Immutable |

`mod(x: int, y: int) → int` | Calculates | Immutable |

`pi() → float` | Returns the value for pi (3.141592653589793). | Immutable |

`pow(x: decimal, y: decimal) → decimal` | Calculates | Immutable |

`pow(x: float, y: float) → float` | Calculates | Immutable |

`pow(x: int, y: int) → int` | Calculates | Immutable |

`power(x: decimal, y: decimal) → decimal` | Calculates | Immutable |

`power(x: float, y: float) → float` | Calculates | Immutable |

`power(x: int, y: int) → int` | Calculates | Immutable |

`radians(val: float) → float` | Converts | Immutable |

`random() → float` | Returns a random floating-point number between 0 (inclusive) and 1 (exclusive). Note that the value contains at most 53 bits of randomness. | Volatile |

`round(input: decimal, decimal_accuracy: int) → decimal` | Keeps | Immutable |

`round(input: float, decimal_accuracy: int) → float` | Keeps | Immutable |

`round(val: decimal) → decimal` | Rounds | Immutable |

`round(val: float) → float` | Rounds | Immutable |

`sign(val: decimal) → decimal` | Determines the sign of | Immutable |

`sign(val: float) → float` | Determines the sign of | Immutable |

`sign(val: int) → int` | Determines the sign of | Immutable |

`sin(val: float) → float` | Calculates the sine of | Immutable |

`sind(val: float) → float` | Calculates the sine of | Immutable |

`sinh(val: float) → float` | Calculates the hyperbolic sine of | Immutable |

`sqrt(val: decimal) → decimal` | Calculates the square root of | Immutable |

`sqrt(val: float) → float` | Calculates the square root of | Immutable |

`tan(val: float) → float` | Calculates the tangent of | Immutable |

`tand(val: float) → float` | Calculates the tangent of | Immutable |

`tanh(val: float) → float` | Calculates the hyperbolic tangent of | Immutable |

`trunc(val: decimal) → decimal` | Truncates the decimal values of | Immutable |

`trunc(val: float) → float` | Truncates the decimal values of | Immutable |

### ID generation functions

Function → Returns | Description | Volatility |
---|---|---|

`experimental_uuid_v4() → bytes` | Returns a UUID. | Volatile |

`gen_random_ulid() → uuid` | Generates a random ULID and returns it as a value of UUID type. | Volatile |

`gen_random_uuid() → uuid` | Generates a random UUID and returns it as a value of UUID type. | Volatile |

`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. | Volatile |

`unordered_unique_rowid() → int` | Returns a unique ID. 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. The way it is generated there is no ordering | Volatile |

`uuid_generate_v4() → uuid` | Generates a random UUID and returns it as a value of UUID type. | Volatile |

`uuid_v4() → bytes` | Returns a UUID. | Volatile |

### INET functions

Function → Returns | Description | Volatility |
---|---|---|

`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, | Immutable |

`broadcast(val: inet) → inet` | Gets the broadcast address for the network address represented by the value. For example, | Immutable |

`family(val: inet) → int` | Extracts the IP family of the value; 4 for IPv4, 6 for IPv6. For example, | Immutable |

`host(val: inet) → string` | Extracts the address part of the combined address/prefixlen value as text. For example, | Immutable |

`hostmask(val: inet) → inet` | Creates an IP host mask corresponding to the prefix length in the value. For example, | Immutable |

`masklen(val: inet) → int` | Retrieves the prefix length stored in the value. For example, | Immutable |

`netmask(val: inet) → inet` | Creates an IP network mask corresponding to the prefix length in the value. For example, | Immutable |

`set_masklen(val: inet, prefixlen: int) → inet` | Sets the prefix length of For example, | Immutable |

`text(val: inet) → string` | Converts the IP address and prefix length to text. | Immutable |

### INT functions

Function → Returns | Description | Volatility |
---|---|---|

`crc32c(bytes...) → int` | Calculates the CRC-32 hash using the Castagnoli polynomial. | Leakproof |

`crc32c(string...) → int` | Calculates the CRC-32 hash using the Castagnoli polynomial. | Leakproof |

`crc32ieee(bytes...) → int` | Calculates the CRC-32 hash using the IEEE polynomial. | Leakproof |

`crc32ieee(string...) → int` | Calculates the CRC-32 hash using the IEEE polynomial. | Leakproof |

`fnv32(bytes...) → int` | Calculates the 32-bit FNV-1 hash value of a set of values. | Leakproof |

`fnv32(string...) → int` | Calculates the 32-bit FNV-1 hash value of a set of values. | Leakproof |

`fnv32a(bytes...) → int` | Calculates the 32-bit FNV-1a hash value of a set of values. | Leakproof |

`fnv32a(string...) → int` | Calculates the 32-bit FNV-1a hash value of a set of values. | Leakproof |

`fnv64(bytes...) → int` | Calculates the 64-bit FNV-1 hash value of a set of values. | Leakproof |

`fnv64(string...) → int` | Calculates the 64-bit FNV-1 hash value of a set of values. | Leakproof |

`fnv64a(bytes...) → int` | Calculates the 64-bit FNV-1a hash value of a set of values. | Leakproof |

`fnv64a(string...) → int` | Calculates the 64-bit FNV-1a hash value of a set of values. | Leakproof |

`levenshtein(source: string, target: string) → int` | Calculates the Levenshtein distance between two strings. Maximum input length is 255 characters. | Immutable |

`levenshtein(source: string, target: string, ins_cost: int, del_cost: int, sub_cost: int) → int` | Calculates the Levenshtein distance between two strings. The cost parameters specify how much to charge for each edit operation. Maximum input length is 255 characters. | Immutable |

`width_bucket(operand: decimal, b1: decimal, b2: decimal, count: int) → int` | return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2. | Immutable |

`width_bucket(operand: int, b1: int, b2: int, count: int) → int` | return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2. | Immutable |

`width_bucket(operand: anyelement, thresholds: anyelement[]) → int` | return the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained | Immutable |

### JSONB functions

Function → Returns | Description | Volatility |
---|---|---|

`array_to_json(array: anyelement[]) → jsonb` | Returns the array as JSON or JSONB. | Stable |

`array_to_json(array: anyelement[], pretty_bool: bool) → jsonb` | Returns the array as JSON or JSONB. | Stable |

`crdb_internal.json_to_pb(pbname: string, json: jsonb) → bytes` | Convert JSONB data to protocol message bytes | Immutable |

`crdb_internal.pb_to_json(pbname: string, data: bytes) → jsonb` | Converts protocol message to its JSONB representation. | Immutable |

`crdb_internal.pb_to_json(pbname: string, data: bytes, emit_defaults: bool) → jsonb` | Converts protocol message to its JSONB representation. | Immutable |

`crdb_internal.pb_to_json(pbname: string, data: bytes, emit_defaults: bool, emit_redacted: bool) → jsonb` | Converts protocol message to its JSONB representation. | Immutable |

`crdb_internal.read_file(uri: string) → bytes` | Read the content of the file at the supplied external storage URI | Volatile |

`crdb_internal.write_file(data: bytes, uri: string) → int` | Write the content passed to a file at the supplied external storage URI | Volatile |

`json_array_length(json: jsonb) → int` | Returns the number of elements in the outermost JSON or JSONB array. | Immutable |

`json_build_array(anyelement...) → jsonb` | Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list. | Stable |

`json_build_object(anyelement...) → jsonb` | Builds a JSON object out of a variadic argument list. | Stable |

`json_extract_path(jsonb, string...) → jsonb` | Returns the JSON value pointed to by the variadic arguments. | Immutable |

`json_extract_path_text(jsonb, string...) → string` | Returns the JSON value as text pointed to by the variadic arguments. | Immutable |

`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. | Immutable |

`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. | Immutable |

`json_remove_path(val: jsonb, path: string[]) → jsonb` | Remove the specified path from the JSON object. | Immutable |

`json_set(val: jsonb, path: string[], to: jsonb) → jsonb` | Returns the JSON value pointed to by the variadic arguments. | Immutable |

`json_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb` | Returns the JSON value pointed to by the variadic arguments. If | Immutable |

`json_strip_nulls(from_json: jsonb) → jsonb` | Returns from_json with all object fields that have null values omitted. Other null values are untouched. | Immutable |

`json_typeof(val: jsonb) → string` | Returns the type of the outermost JSON value as a text string. | Immutable |

`json_valid(string: string) → bool` | Returns whether the given string is a valid JSON or not | Immutable |

`jsonb_array_length(json: jsonb) → int` | Returns the number of elements in the outermost JSON or JSONB array. | Immutable |

`jsonb_build_array(anyelement...) → jsonb` | Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list. | Stable |

`jsonb_build_object(anyelement...) → jsonb` | Builds a JSON object out of a variadic argument list. | Stable |

`jsonb_exists_any(json: jsonb, array: string[]) → bool` | Returns whether any of the strings in the text array exist as top-level keys or array elements | Immutable |

`jsonb_extract_path(jsonb, string...) → jsonb` | Returns the JSON value pointed to by the variadic arguments. | Immutable |

`jsonb_extract_path_text(jsonb, string...) → string` | Returns the JSON value as text pointed to by the variadic arguments. | Immutable |

`jsonb_insert(target: jsonb, path: string[], new_val: jsonb) → jsonb` | Returns the JSON value pointed to by the variadic arguments. | Immutable |

`jsonb_insert(target: jsonb, path: string[], new_val: jsonb, insert_after: bool) → jsonb` | Returns the JSON value pointed to by the variadic arguments. If | Immutable |

`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. | Immutable |

`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. | Immutable |

`jsonb_pretty(val: jsonb) → string` | Returns the given JSON value as a STRING indented and with newlines. | Immutable |

`jsonb_set(val: jsonb, path: string[], to: jsonb) → jsonb` | Returns the JSON value pointed to by the variadic arguments. | Immutable |

`jsonb_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb` | Returns the JSON value pointed to by the variadic arguments. If | Immutable |

`jsonb_strip_nulls(from_json: jsonb) → jsonb` | Returns from_json with all object fields that have null values omitted. Other null values are untouched. | Immutable |

`jsonb_typeof(val: jsonb) → string` | Returns the type of the outermost JSON value as a text string. | Immutable |

`row_to_json(row: tuple) → jsonb` | Returns the row as a JSON object. | Stable |

`to_json(val: anyelement) → jsonb` | Returns the value as JSON or JSONB. | Stable |

`to_jsonb(val: anyelement) → jsonb` | Returns the value as JSON or JSONB. | Stable |

### Multi-region functions

Function → Returns | Description | Volatility |
---|---|---|

`crdb_internal.filter_multiregion_fields_from_zone_config_sql(val: string) → string` | Takes in a CONFIGURE ZONE SQL statement and returns a modified SQL statement omitting multi-region related zone configuration fields. If the CONFIGURE ZONE statement can be inferred by the database’s or table’s zone configuration this will return NULL. | Stable |

`crdb_internal.reset_multi_region_zone_configs_for_database(id: int) → bool` | Resets the zone configuration for a multi-region database to match its original state. No-ops if the given database ID is not multi-region enabled. | Volatile |

`crdb_internal.reset_multi_region_zone_configs_for_table(id: int) → bool` | Resets the zone configuration for a multi-region table to match its original state. No-ops if the given table ID is not a multi-region table. | Volatile |

`crdb_internal.validate_multi_region_zone_configs() → bool` | Validates all multi-region zone configurations are correctly setup for the current database, including all tables, indexes and partitions underneath. Returns an error if validation fails. This builtin uses un-leased versions of the each descriptor, requiring extra round trips. | Volatile |

`default_to_database_primary_region(val: string) → string` | Returns the given region if the region has been added to the current database. Otherwise, this will return the primary region of the current database. This will error if the current database is not a multi-region database. | Stable |

`gateway_region() → string` | Returns the region of the connection’s current node as defined by the locality flag on node startup. Returns an error if no region is set. | Stable |

`rehome_row() → string` | Returns the region of the connection’s current node as defined by the locality flag on node startup. Returns an error if no region is set. | Stable |

### STRING[] functions

Function → Returns | Description | Volatility | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

`regexp_split_to_array(string: string, pattern: string) → string[]` | Split string using a POSIX regular expression as the delimiter. | Immutable | |||||||||||||||||||||||||||||||

`regexp_split_to_array(string: string, pattern: string, flags: string) → string[]` | Split string using a POSIX regular expression as the delimiter with flags. CockroachDB supports the following flags:
| Immutable |

### Sequence functions

Function → Returns | Description | Volatility |
---|---|---|

`currval(sequence_name: string) → int` | Returns the latest value obtained with nextval for this sequence in this session. | Volatile |

`currval(sequence_name: regclass) → int` | Returns the latest value obtained with nextval for this sequence in this session. | Volatile |

`lastval() → int` | Return value most recently obtained with nextval in this session. | Volatile |

`nextval(sequence_name: string) → int` | Advances the given sequence and returns its new value. | Volatile |

`nextval(sequence_name: regclass) → int` | Advances the given sequence and returns its new value. | Volatile |

`setval(sequence_name: string, value: int) → int` | Set the given sequence’s current value. The next call to nextval will return | Volatile |

`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 | Volatile |

`setval(sequence_name: regclass, value: int) → int` | Set the given sequence’s current value. The next call to nextval will return | Volatile |

`setval(sequence_name: regclass, 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 | Volatile |

### Set-returning functions

Function → Returns | Description | Volatility | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

`aclexplode(aclitems: string[]) → tuple{oid AS grantor, oid AS grantee, string AS privilege_type, bool AS is_grantable}` | Produces a virtual table containing aclitem stuff (returns no rows as this feature is unsupported in CockroachDB) | Stable | |||||||||||||||||||||||||||||||

`crdb_internal.testing_callback(name: string) → int` | For internal CRDB testing only. The function calls a callback identified by | Volatile | |||||||||||||||||||||||||||||||

`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. | Volatile | |||||||||||||||||||||||||||||||

`generate_series(start: int, end: int) → int` | Produces a virtual table containing the integer values from | Immutable | |||||||||||||||||||||||||||||||

`generate_series(start: int, end: int, step: int) → int` | Produces a virtual table containing the integer values from | Immutable | |||||||||||||||||||||||||||||||

`generate_series(start: timestamp, end: timestamp, step: interval) → timestamp` | Produces a virtual table containing the timestamp values from | Immutable | |||||||||||||||||||||||||||||||

`generate_series(start: timestamptz, end: timestamptz, step: interval) → timestamptz` | Produces a virtual table containing the timestampTZ values from | Immutable | |||||||||||||||||||||||||||||||

`generate_subscripts(array: anyelement[]) → int` | Returns a series comprising the given array’s subscripts. | Immutable | |||||||||||||||||||||||||||||||

`generate_subscripts(array: anyelement[], dim: int) → int` | Returns a series comprising the given array’s subscripts. | Immutable | |||||||||||||||||||||||||||||||

`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. | Immutable | |||||||||||||||||||||||||||||||

`information_schema._pg_expandarray(input: anyelement[]) → tuple{anyelement AS x, int AS n}` | Returns the input array as a set of rows with an index | Immutable | |||||||||||||||||||||||||||||||

`json_array_elements(input: jsonb) → jsonb` | Expands a JSON array to a set of JSON values. | Immutable | |||||||||||||||||||||||||||||||

`json_array_elements_text(input: jsonb) → string` | Expands a JSON array to a set of text values. | Immutable | |||||||||||||||||||||||||||||||

`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. | Immutable | |||||||||||||||||||||||||||||||

`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. | Immutable | |||||||||||||||||||||||||||||||

`json_object_keys(input: jsonb) → string` | Returns sorted set of keys in the outermost JSON object. | Immutable | |||||||||||||||||||||||||||||||

`json_populate_record(base: anyelement, from_json: jsonb) → anyelement` | Expands the object in from_json to a row whose columns match the record type defined by base. | Stable | |||||||||||||||||||||||||||||||

`json_populate_recordset(base: anyelement, from_json: jsonb) → anyelement` | Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base | Stable | |||||||||||||||||||||||||||||||

`jsonb_array_elements(input: jsonb) → jsonb` | Expands a JSON array to a set of JSON values. | Immutable | |||||||||||||||||||||||||||||||

`jsonb_array_elements_text(input: jsonb) → string` | Expands a JSON array to a set of text values. | Immutable | |||||||||||||||||||||||||||||||

`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. | Immutable | |||||||||||||||||||||||||||||||

`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. | Immutable | |||||||||||||||||||||||||||||||

`jsonb_object_keys(input: jsonb) → string` | Returns sorted set of keys in the outermost JSON object. | Immutable | |||||||||||||||||||||||||||||||

`jsonb_populate_record(base: anyelement, from_json: jsonb) → anyelement` | Expands the object in from_json to a row whose columns match the record type defined by base. | Stable | |||||||||||||||||||||||||||||||

`jsonb_populate_recordset(base: anyelement, from_json: jsonb) → anyelement` | Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base | Stable | |||||||||||||||||||||||||||||||

`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. | Immutable | |||||||||||||||||||||||||||||||

`regexp_split_to_table(string: string, pattern: string) → string` | Split string using a POSIX regular expression as the delimiter. | Immutable | |||||||||||||||||||||||||||||||

`regexp_split_to_table(string: string, pattern: string, flags: string) → string` | Split string using a POSIX regular expression as the delimiter with flags. CockroachDB supports the following flags:
| Immutable | |||||||||||||||||||||||||||||||

`unnest(anyelement[], anyelement[], anyelement[]...) → tuple{anyelement AS unnest, anyelement AS unnest, anyelement AS unnest}` | Returns the input arrays as a set of rows | Immutable | |||||||||||||||||||||||||||||||

`unnest(input: anyelement[]) → anyelement` | Returns the input array as a set of rows | Immutable |

### Spatial functions

Function → Returns | Description | Volatility |
---|---|---|

`_st_contains(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if no points of geometry_b lie in the exterior of geometry_a, and there is at least one point in the interior of geometry_b that lies in the interior of geometry_a. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_containsproperly(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_coveredby(geography_a: geography, geography_b: geography) → bool` | Returns true if no point in geography_a is outside geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_coveredby(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if no point in geometry_a is outside geometry_b. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_covers(geography_a: geography, geography_b: geography) → bool` | Returns true if no point in geography_b is outside geography_a. This function utilizes the S2 library for spherical calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_covers(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if no point in geometry_b is outside geometry_a. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_crosses(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a has some - but not all - interior points in common with geometry_b. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_dfullywithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, inclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than or equal to distance units. This function variant does not utilize any spatial index. | Immutable |

`_st_dfullywithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, exclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than distance units. This function variant does not utilize any spatial index. | Immutable |

`_st_dwithin(geography_a: geography, geography_b: geography, distance: float) → bool` | Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_dwithin(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool` | Returns true if any of geography_a is within distance meters of geography_b, inclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_dwithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if any of geometry_a is within distance units of geometry_b, inclusive. This function variant does not utilize any spatial index. | Immutable |

`_st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float) → bool` | Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool` | Returns true if any of geography_a is within distance meters of geography_b, exclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_dwithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if any of geometry_a is within distance units of geometry_b, exclusive. This function variant does not utilize any spatial index. | Immutable |

`_st_equals(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a is spatially equal to geometry_b, i.e. ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = true. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_intersects(geography_a: geography, geography_b: geography) → bool` | Returns true if geography_a shares any portion of space with geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant does not utilize any spatial index. | Immutable |

`_st_intersects(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a shares any portion of space with geometry_b. The calculations performed are have a precision of 1cm. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_overlaps(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a intersects but does not completely contain geometry_b, or vice versa. “Does not completely” implies ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = false. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_touches(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if the only points in common between geometry_a and geometry_b are on the boundary. Note points do not touch other points. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`_st_within(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a is completely inside geometry_b. This function utilizes the GEOS module. This function variant does not utilize any spatial index. | Immutable |

`addgeometrycolumn(catalog_name: string, schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int) → string` | Adds a new geometry column to an existing table and returns metadata about the column created. | Volatile |

`addgeometrycolumn(catalog_name: string, schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string` | Adds a new geometry column to an existing table and returns metadata about the column created. | Volatile |

`addgeometrycolumn(schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int) → string` | Adds a new geometry column to an existing table and returns metadata about the column created. | Volatile |

`addgeometrycolumn(schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string` | Adds a new geometry column to an existing table and returns metadata about the column created. | Volatile |

`addgeometrycolumn(table_name: string, column_name: string, srid: int, type: string, dimension: int) → string` | Adds a new geometry column to an existing table and returns metadata about the column created. | Volatile |

`addgeometrycolumn(table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string` | Adds a new geometry column to an existing table and returns metadata about the column created. | Volatile |

`geometrytype(geometry: geometry) → string` | Returns the type of geometry as a string. This function utilizes the GEOS module. | Immutable |

`geomfromewkb(val: bytes) → geometry` | Returns the Geometry from an EWKB representation. | Immutable |

`geomfromewkt(val: string) → geometry` | Returns the Geometry from an EWKT representation. | Immutable |

`postgis_addbbox(geometry: geometry) → geometry` | Compatibility placeholder function with PostGIS. This does not perform any operation on the Geometry. | Immutable |

`postgis_dropbbox(geometry: geometry) → geometry` | Compatibility placeholder function with PostGIS. This does not perform any operation on the Geometry. | Immutable |

`postgis_extensions_upgrade() → string` | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |

`postgis_full_version() → string` | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |

`postgis_geos_version() → string` | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |

`postgis_getbbox(geometry: geometry) → box2d` | Returns a box2d encapsulating the given Geometry. | Immutable |

`postgis_hasbbox(geometry: geometry) → bool` | Returns whether a given Geometry has a bounding box. False for points and empty geometries; always true otherwise. | Immutable |

`postgis_lib_build_date() → string` | Immutable | |

`postgis_lib_version() → string` | Immutable | |

`postgis_liblwgeom_version() → string` | Immutable | |

`postgis_libxml_version() → string` | Immutable | |

`postgis_proj_version() → string` | Immutable | |

`postgis_scripts_build_date() → string` | Immutable | |

`postgis_scripts_installed() → string` | Immutable | |

`postgis_scripts_released() → string` | Immutable | |

`postgis_version() → string` | Immutable | |

`postgis_wagyu_version() → string` | Immutable | |

`st_addmeasure(geometry: geometry, start: float, end: float) → geometry` | Returns a copy of a LineString or MultiLineString with measure coordinates linearly interpolated between the specified start and end values. Any existing M coordinates will be overwritten. | Immutable |

`st_addpoint(line_string: geometry, point: geometry) → geometry` | Adds a Point to the end of a LineString. | Immutable |

`st_addpoint(line_string: geometry, point: geometry, index: int) → geometry` | Adds a Point to a LineString at the given 0-based index (-1 to append). | Immutable |

`st_affine(geometry: geometry, a: float, b: float, c: float, d: float, e: float, f: float, g: float, h: float, i: float, x_off: float, y_off: float, z_off: float) → geometry` | Applies a 3D affine transformation to the given geometry. The matrix transformation will be applied as follows for each coordinate:
/ a b c x_off \ / x | Immutable |

`st_affine(geometry: geometry, a: float, b: float, d: float, e: float, x_off: float, y_off: float) → geometry` | Applies a 2D affine transformation to the given geometry. The matrix transformation will be applied as follows for each coordinate:
/ a b x_off \ / x | Immutable |

`st_angle(line1: geometry, line2: geometry) → float` | Returns the clockwise angle between two LINESTRING geometries, treating them as vectors between their start- and endpoints. Returns NULL if any vectors have 0 length. | Immutable |

`st_angle(point1: geometry, point2: geometry, point3: geometry) → float` | Returns the clockwise angle between the vectors formed by point2,point1 and point2,point3. The arguments must be POINT geometries. Returns NULL if any vectors have 0 length. | Immutable |

`st_angle(point1: geometry, point2: geometry, point3: geometry, point4: geometry) → float` | Returns the clockwise angle between the vectors formed by point1,point2 and point3,point4. The arguments must be POINT geometries. Returns NULL if any vectors have 0 length. | Immutable |

`st_area(geography: geography) → float` | Returns the area of the given geography in meters^2. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_area(geography: geography, use_spheroid: bool) → float` | Returns the area of the given geography in meters^2. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_area(geometry: geometry) → float` | Returns the area of the given geometry. This function utilizes the GEOS module. | Immutable |

`st_area(geometry_str: string) → float` | Returns the area of the given geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_area2d(geometry: geometry) → float` | Returns the area of the given geometry. This function utilizes the GEOS module. | Immutable |

`st_asbinary(geography: geography) → bytes` | Returns the WKB representation of a given Geography. | Immutable |

`st_asbinary(geography: geography, xdr_or_ndr: string) → bytes` | Returns the WKB representation of a given Geography. This variant has a second argument denoting the encoding - | Immutable |

`st_asbinary(geometry: geometry) → bytes` | Returns the WKB representation of a given Geometry. | Immutable |

`st_asbinary(geometry: geometry, xdr_or_ndr: string) → bytes` | Returns the WKB representation of a given Geometry. This variant has a second argument denoting the encoding - | Immutable |

`st_asencodedpolyline(geometry: geometry) → string` | Returns the geometry as an Encoded Polyline. This format is used by Google Maps with precision=5 and by Open Source Routing Machine with precision=5 and 6. Preserves 5 decimal places. | Immutable |

`st_asencodedpolyline(geometry: geometry, precision: int4) → string` | Returns the geometry as an Encoded Polyline. This format is used by Google Maps with precision=5 and by Open Source Routing Machine with precision=5 and 6. Precision specifies how many decimal places will be preserved in Encoded Polyline. Value should be the same on encoding and decoding, or coordinates will be incorrect. | Immutable |

`st_asewkb(geography: geography) → bytes` | Returns the EWKB representation of a given Geography. | Immutable |

`st_asewkb(geometry: geometry) → bytes` | Returns the EWKB representation of a given Geometry. | Immutable |

`st_asewkt(geography: geography) → string` | Returns the EWKT representation of a given Geography. A default of 15 decimal digits is used. | Immutable |

`st_asewkt(geography: geography, max_decimal_digits: int) → string` | Returns the EWKT representation of a given Geography. The max_decimal_digits parameter controls the maximum decimal digits to print after the | Immutable |

`st_asewkt(geometry: geometry) → string` | Returns the EWKT representation of a given Geometry. A maximum of 15 decimal digits is used. | Immutable |

`st_asewkt(geometry: geometry, max_decimal_digits: int) → string` | Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the | Immutable |

`st_asewkt(geometry_str: string) → string` | Returns the EWKT representation of a given Geometry. A maximum of 15 decimal digits is used. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_asewkt(geometry_str: string, max_decimal_digits: int) → string` | Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_asgeojson(geography: geography) → string` | Returns the GeoJSON representation of a given Geography. Coordinates have a maximum of 9 decimal digits. | Immutable |

`st_asgeojson(geography: geography, max_decimal_digits: int) → string` | Returns the GeoJSON representation of a given Geography with max_decimal_digits output for each coordinate value. | Immutable |

`st_asgeojson(geography: geography, max_decimal_digits: int, options: int) → string` | Returns the GeoJSON representation of a given Geography with max_decimal_digits output for each coordinate value. Options is a flag that can be bitmasked. The options are: - 0: no option (default for Geography)
- 1: GeoJSON BBOX
- 2: GeoJSON Short CRS (e.g EPSG:4326)
- 4: GeoJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)
- 8: GeoJSON Short CRS if not EPSG:4326
| Immutable |

`st_asgeojson(geometry: geometry) → string` | Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits. | Immutable |

`st_asgeojson(geometry: geometry, max_decimal_digits: int) → string` | Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. | Immutable |

`st_asgeojson(geometry: geometry, max_decimal_digits: int, options: int) → string` | Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. Options is a flag that can be bitmasked. The options are: - 0: no option
- 1: GeoJSON BBOX
- 2: GeoJSON Short CRS (e.g EPSG:4326)
- 4: GeoJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)
- 8: GeoJSON Short CRS if not EPSG:4326 (default for Geometry)
| Immutable |

`st_asgeojson(geometry_str: string) → string` | Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_asgeojson(geometry_str: string, max_decimal_digits: int) → string` | Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_asgeojson(geometry_str: string, max_decimal_digits: int, options: int) → string` | Options is a flag that can be bitmasked. The options are: - 0: no option
- 1: GeoJSON BBOX
- 2: GeoJSON Short CRS (e.g EPSG:4326)
- 4: GeoJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)
- 8: GeoJSON Short CRS if not EPSG:4326 (default for Geometry)
This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_asgeojson(row: tuple) → string` | Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits. | Immutable |

`st_asgeojson(row: tuple, geo_column: string) → string` | Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. Coordinates have a maximum of 9 decimal digits. | Stable |

`st_asgeojson(row: tuple, geo_column: string, max_decimal_digits: int) → string` | Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. max_decimal_digits will be output for each coordinate value. | Stable |

`st_asgeojson(row: tuple, geo_column: string, max_decimal_digits: int, pretty: bool) → string` | Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. max_decimal_digits will be output for each coordinate value. Output will be pretty printed in JSON if pretty is true. | Stable |

`st_ashexewkb(geography: geography) → string` | Returns the EWKB representation in hex of a given Geography. | Immutable |

`st_ashexewkb(geography: geography, xdr_or_ndr: string) → string` | Returns the EWKB representation in hex of a given Geography. This variant has a second argument denoting the encoding - | Immutable |

`st_ashexewkb(geometry: geometry) → string` | Returns the EWKB representation in hex of a given Geometry. | Immutable |

`st_ashexewkb(geometry: geometry, xdr_or_ndr: string) → string` | Returns the EWKB representation in hex of a given Geometry. This variant has a second argument denoting the encoding - | Immutable |

`st_ashexwkb(geography: geography) → string` | Returns the WKB representation in hex of a given Geography. | Immutable |

`st_ashexwkb(geometry: geometry) → string` | Returns the WKB representation in hex of a given Geometry. | Immutable |

`st_askml(geography: geography) → string` | Returns the KML representation of a given Geography. | Immutable |

`st_askml(geometry: geometry) → string` | Returns the KML representation of a given Geometry. | Immutable |

`st_askml(geometry_str: string) → string` | Returns the KML representation of a given Geometry. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_astext(geography: geography) → string` | Returns the WKT representation of a given Geography. A default of 15 decimal digits is used. | Immutable |

`st_astext(geography: geography, max_decimal_digits: int) → string` | Returns the WKT representation of a given Geography. The max_decimal_digits parameter controls the maximum decimal digits to print after the | Immutable |

`st_astext(geometry: geometry) → string` | Returns the WKT representation of a given Geometry. A maximum of 15 decimal digits is used. | Immutable |

`st_astext(geometry: geometry, max_decimal_digits: int) → string` | Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the | Immutable |

`st_astext(geometry_str: string) → string` | Returns the WKT representation of a given Geometry. A maximum of 15 decimal digits is used. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_astext(geometry_str: string, max_decimal_digits: int) → string` |
This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_astwkb(geometry: geometry, precision_xy: int) → bytes` | Returns the TWKB representation of a given geometry. | Immutable |

`st_astwkb(geometry: geometry, precision_xy: int, precision_z: int) → bytes` | Returns the TWKB representation of a given geometry. | Immutable |

`st_astwkb(geometry: geometry, precision_xy: int, precision_z: int, precision_m: int) → bytes` | Returns the TWKB representation of a given geometry. | Immutable |

`st_azimuth(geography_a: geography, geography_b: geography) → float` | Returns the azimuth in radians of the segment defined by the given point geographies, or NULL if the two points are coincident. It is solved using the Inverse geodesic problem. The azimuth is angle is referenced from north, and is positive clockwise: North = 0; East = π/2; South = π; West = 3π/2. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_azimuth(geometry_a: geometry, geometry_b: geometry) → float` | Returns the azimuth in radians of the segment defined by the given point geometries, or NULL if the two points are coincident. The azimuth is angle is referenced from north, and is positive clockwise: North = 0; East = π/2; South = π; West = 3π/2. | Immutable |

`st_boundary(geometry: geometry) → geometry` | Returns the closure of the combinatorial boundary of this Geometry. This function utilizes the GEOS module. | Immutable |

`st_box2dfromgeohash(geohash: string) → box2d` | Return a Box2D from a GeoHash string with max precision. | Immutable |

`st_box2dfromgeohash(geohash: string, precision: int) → box2d` | Return a Box2D from a GeoHash string with supplied precision. | Immutable |

`st_buffer(geography: geography, distance: float) → geography` | Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. | Immutable |

`st_buffer(geography: geography, distance: float, buffer_style_params: string) → geography` | Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are: - quad_segs=<int>, default 8
- endcap=<round|flat|butt|square>, default round
- join=<round|mitre|miter|bevel>, default round
- side=<both|left|right>, default both
- mitre_limit=<float>, default 5.0
This function utilizes the GEOS module. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. | Immutable |

`st_buffer(geography: geography, distance: float, quad_segs: int) → geography` | Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant approximates the circle into quad_seg segments per line (the default is 8). This function utilizes the GEOS module. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. | Immutable |

`st_buffer(geometry: geometry, distance: decimal) → geometry` | This function utilizes the GEOS module. | Immutable |

`st_buffer(geometry: geometry, distance: float) → geometry` | This function utilizes the GEOS module. | Immutable |

`st_buffer(geometry: geometry, distance: float, buffer_style_params: string) → geometry` | This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are: - quad_segs=<int>, default 8
- endcap=<round|flat|butt|square>, default round
- join=<round|mitre|miter|bevel>, default round
- side=<both|left|right>, default both
- mitre_limit=<float>, default 5.0
This function utilizes the GEOS module. | Immutable |

`st_buffer(geometry: geometry, distance: float, quad_segs: int) → geometry` | This variant approximates the circle into quad_seg segments per line (the default is 8). This function utilizes the GEOS module. | Immutable |

`st_buffer(geometry: geometry, distance: int) → geometry` | This function utilizes the GEOS module. | Immutable |

`st_buffer(geometry_str: string, distance: decimal) → geometry` | This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_buffer(geometry_str: string, distance: float) → geometry` | This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_buffer(geometry_str: string, distance: float, buffer_style_params: string) → geometry` | This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are: - quad_segs=<int>, default 8
- endcap=<round|flat|butt|square>, default round
- join=<round|mitre|miter|bevel>, default round
- side=<both|left|right>, default both
- mitre_limit=<float>, default 5.0
This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_buffer(geometry_str: string, distance: float, quad_segs: int) → geometry` | This variant approximates the circle into quad_seg segments per line (the default is 8). This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_buffer(geometry_str: string, distance: int) → geometry` | This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_centroid(geography: geography) → geography` | Returns the centroid of given geography. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_centroid(geography: geography, use_spheroid: bool) → geography` | Returns the centroid of given geography. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_centroid(geometry: geometry) → geometry` | Returns the centroid of the given geometry. This function utilizes the GEOS module. | Immutable |

`st_centroid(geometry_str: string) → geometry` | Returns the centroid of the given geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_clipbybox2d(geometry: geometry, box2d: box2d) → geometry` | Clips the geometry to conform to the bounding box specified by box2d. | Immutable |

`st_closestpoint(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the 2-dimensional point on geometry_a that is closest to geometry_b. This is the first point of the shortest line. | Immutable |

`st_collectionextract(geometry: geometry, type: int) → geometry` | Given a collection, returns a multitype consisting only of elements of the specified type. If there are no elements of the given type, an EMPTY geometry is returned. Types are specified as 1=POINT, 2=LINESTRING, 3=POLYGON - other types are not supported. | Immutable |

`st_collectionhomogenize(geometry: geometry) → geometry` | Returns the “simplest” representation of a collection’s contents. Collections of a single type will be returned as an appopriate multitype, or a singleton if it only contains a single geometry. | Immutable |

`st_combinebbox(box2d: box2d, geometry: geometry) → box2d` | Combines the current bounding box with the bounding box of the Geometry. | Immutable |

`st_contains(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if no points of geometry_b lie in the exterior of geometry_a, and there is at least one point in the interior of geometry_b that lies in the interior of geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_containsproperly(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_convexhull(geometry: geometry) → geometry` | Returns a geometry that represents the Convex Hull of the given geometry. This function utilizes the GEOS module. | Immutable |

`st_coorddim(geometry: geometry) → int` | Returns the number of coordinate dimensions of a given Geometry. | Immutable |

`st_coveredby(geography_a: geography, geography_b: geography) → bool` | Returns true if no point in geography_a is outside geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_coveredby(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if no point in geometry_a is outside geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_coveredby(geometry_a_str: string, geometry_b_str: string) → bool` | Returns true if no point in geometry_a is outside geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_covers(geography_a: geography, geography_b: geography) → bool` | Returns true if no point in geography_b is outside geography_a. This function utilizes the S2 library for spherical calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_covers(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if no point in geometry_b is outside geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_covers(geometry_a_str: string, geometry_b_str: string) → bool` | Returns true if no point in geometry_b is outside geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_crosses(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a has some - but not all - interior points in common with geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dfullywithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, inclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than or equal to distance units. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dfullywithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, exclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than distance units. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_difference(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the difference of two Geometries. This function utilizes the GEOS module. | Immutable |

`st_dimension(geometry: geometry) → int` | Returns the number of topological dimensions of a given Geometry. | Immutable |

`st_disjoint(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a does not overlap, touch or is within geometry_b. This function utilizes the GEOS module. | Immutable |

`st_distance(geography_a: geography, geography_b: geography) → float` | Returns the distance in meters between geography_a and geography_b. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_distance(geography_a: geography, geography_b: geography, use_spheroid: bool) → float` | Returns the distance in meters between geography_a and geography_b."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_distance(geometry_a: geometry, geometry_b: geometry) → float` | Returns the distance between the given geometries. | Immutable |

`st_distance(geometry_a_str: string, geometry_b_str: string) → float` | Returns the distance between the given geometries. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_distancesphere(geometry_a: geometry, geometry_b: geometry) → float` | Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a sphere. This function utilizes the S2 library for spherical calculations. | Immutable |

`st_distancespheroid(geometry_a: geometry, geometry_b: geometry) → float` | Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a spheroid."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_dwithin(geography_a: geography, geography_b: geography, distance: float) → bool` | Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dwithin(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool` | Returns true if any of geography_a is within distance meters of geography_b, inclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dwithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if any of geometry_a is within distance units of geometry_b, inclusive. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dwithin(geometry_a_str: string, geometry_b_str: string, distance: float) → bool` | Returns true if any of geometry_a is within distance units of geometry_b, inclusive. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float) → bool` | Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool` | Returns true if any of geography_a is within distance meters of geography_b, exclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dwithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool` | Returns true if any of geometry_a is within distance units of geometry_b, exclusive. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_dwithinexclusive(geometry_a_str: string, geometry_b_str: string, distance: float) → bool` | Returns true if any of geometry_a is within distance units of geometry_b, exclusive. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_endpoint(geometry: geometry) → geometry` | Returns the last point of a geometry which has shape LineString. Returns NULL if the geometry is not a LineString. | Immutable |

`st_envelope(box2d: box2d) → geometry` | Returns a bounding geometry for the given box. | Immutable |

`st_envelope(geometry: geometry) → geometry` | Returns a bounding envelope for the given geometry. For geometries which have a POINT or LINESTRING bounding box (i.e. is a single point or a horizontal or vertical line), a POINT or LINESTRING is returned. Otherwise, the returned POLYGON will be ordered Bottom Left, Top Left, Top Right, Bottom Right, Bottom Left. | Immutable |

`st_equals(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a is spatially equal to geometry_b, i.e. ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = true. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_estimatedextent(schema_name: string, table_name: string, geocolumn_name: string) → box2d` | Returns the estimated extent of the geometries in the column of the given table. This currently always returns NULL. | Stable |

`st_estimatedextent(schema_name: string, table_name: string, geocolumn_name: string, parent_only: bool) → box2d` | Returns the estimated extent of the geometries in the column of the given table. This currently always returns NULL. The parent_only boolean is always ignored. | Stable |

`st_estimatedextent(table_name: string, geocolumn_name: string) → box2d` | Returns the estimated extent of the geometries in the column of the given table. This currently always returns NULL. | Stable |

`st_expand(box2d: box2d, delta: float) → box2d` | Extends the box2d by delta units across all dimensions. | Immutable |

`st_expand(box2d: box2d, delta_x: float, delta_y: float) → box2d` | Extends the box2d by delta_x units in the x dimension and delta_y units in the y dimension. | Immutable |

`st_expand(geometry: geometry, delta: float) → geometry` | Extends the bounding box represented by the geometry by delta units across all dimensions, returning a Polygon representing the new bounding box. | Immutable |

`st_expand(geometry: geometry, delta_x: float, delta_y: float) → geometry` | Extends the bounding box represented by the geometry by delta_x units in the x dimension and delta_y units in the y dimension, returning a Polygon representing the new bounding box. | Immutable |

`st_exteriorring(geometry: geometry) → geometry` | Returns the exterior ring of a Polygon as a LineString. Returns NULL if the shape is not a Polygon. | Immutable |

`st_flipcoordinates(geometry: geometry) → geometry` | Returns a new geometry with the X and Y axes flipped. | Immutable |

`st_force2d(geometry: geometry) → geometry` | Returns a Geometry that is forced into XY layout with any Z or M dimensions discarded. | Immutable |

`st_force3d(geometry: geometry) → geometry` | Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to 0. If a M coordinate is present, it will be discarded. | Immutable |

`st_force3d(geometry: geometry, defaultZ: float) → geometry` | Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified default Z value. If a M coordinate is present, it will be discarded. | Immutable |

`st_force3dm(geometry: geometry) → geometry` | Returns a Geometry that is forced into XYM layout. If a M coordinate doesn’t exist, it will be set to 0. If a Z coordinate is present, it will be discarded. | Immutable |

`st_force3dm(geometry: geometry, defaultM: float) → geometry` | Returns a Geometry that is forced into XYM layout. If a M coordinate doesn’t exist, it will be set to the specified default M value. If a Z coordinate is present, it will be discarded. | Immutable |

`st_force3dz(geometry: geometry) → geometry` | Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to 0. If a M coordinate is present, it will be discarded. | Immutable |

`st_force3dz(geometry: geometry, defaultZ: float) → geometry` | Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified default Z value. If a M coordinate is present, it will be discarded. | Immutable |

`st_force4d(geometry: geometry) → geometry` | Returns a Geometry that is forced into XYZM layout. If a Z coordinate doesn’t exist, it will be set to 0. If a M coordinate doesn’t exist, it will be set to 0. | Immutable |

`st_force4d(geometry: geometry, defaultZ: float) → geometry` | Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified default Z value. If a M coordinate doesn’t exist, it will be set to 0. | Immutable |

`st_force4d(geometry: geometry, defaultZ: float, defaultM: float) → geometry` | Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified Z value. If a M coordinate doesn’t exist, it will be set to the specified M value. | Immutable |

`st_forcecollection(geometry: geometry) → geometry` | Converts the geometry into a GeometryCollection. | Immutable |

`st_forcepolygonccw(geometry: geometry) → geometry` | Returns a Geometry where all Polygon objects have exterior rings in the counter-clockwise orientation and interior rings in the clockwise orientation. Non-Polygon objects are unchanged. | Immutable |

`st_forcepolygoncw(geometry: geometry) → geometry` | Returns a Geometry where all Polygon objects have exterior rings in the clockwise orientation and interior rings in the counter-clockwise orientation. Non-Polygon objects are unchanged. | Immutable |

`st_frechetdistance(geometry_a: geometry, geometry_b: geometry) → float` | Returns the Frechet distance between the given geometries. This function utilizes the GEOS module. | Immutable |

`st_frechetdistance(geometry_a: geometry, geometry_b: geometry, densify_frac: float) → float` | Returns the Frechet distance between the given geometries, with the given segment densification (range 0.0-1.0, -1 to disable). Smaller densify_frac gives a more accurate Fréchet distance. However, the computation time and memory usage increases with the square of the number of subsegments. This function utilizes the GEOS module. | Immutable |

`st_generatepoints(geometry: geometry, npoints: int4) → geometry` | Generates pseudo-random points until the requested number are found within the input area. Uses system time as a seed. The requested number of points must be not larger than 65336. | Volatile |

`st_generatepoints(geometry: geometry, npoints: int4, seed: int4) → geometry` | Generates pseudo-random points until the requested number are found within the input area. The requested number of points must be not larger than 65336. | Immutable |

`st_geogfromewkb(val: bytes) → geography` | Returns the Geography from an EWKB representation. | Immutable |

`st_geogfromewkt(val: string) → geography` | Returns the Geography from an EWKT representation. | Immutable |

`st_geogfromgeojson(val: string) → geography` | Returns the Geography from an GeoJSON representation. | Immutable |

`st_geogfromgeojson(val: jsonb) → geography` | Returns the Geography from an GeoJSON representation. | Immutable |

`st_geogfromtext(str: string, srid: int) → geography` | Returns the Geography from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_geogfromtext(val: string) → geography` | Returns the Geography from a WKT or EWKT representation. | Immutable |

`st_geogfromwkb(bytes: bytes, srid: int) → geography` | Returns the Geography from a WKB (or EWKB) representation with the given SRID set. | Immutable |

`st_geogfromwkb(val: bytes) → geography` | Returns the Geography from a WKB (or EWKB) representation. | Immutable |

`st_geographyfromtext(str: string, srid: int) → geography` | Returns the Geography from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_geographyfromtext(val: string) → geography` | Returns the Geography from a WKT or EWKT representation. | Immutable |

`st_geohash(geography: geography) → string` | Returns a GeoHash representation of the geeographywith full precision if a point is provided, or with variable precision based on the size of the feature. | Immutable |

`st_geohash(geography: geography, precision: int) → string` | Returns a GeoHash representation of the geography with the supplied precision. | Immutable |

`st_geohash(geometry: geometry) → string` | Returns a GeoHash representation of the geometry with full precision if a point is provided, or with variable precision based on the size of the feature. This will error any coordinates are outside the bounds of longitude/latitude. | Immutable |

`st_geohash(geometry: geometry, precision: int) → string` | Returns a GeoHash representation of the geometry with the supplied precision. This will error any coordinates are outside the bounds of longitude/latitude. | Immutable |

`st_geomcollfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not GeometryCollection, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_geomcollfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not GeometryCollection, NULL is returned. | Immutable |

`st_geomcollfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not GeometryCollection, NULL is returned. | Immutable |

`st_geomcollfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not GeometryCollection, NULL is returned. | Immutable |

`st_geometryfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_geometryfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. | Immutable |

`st_geometryn(geometry: geometry, n: int) → geometry` | Returns the n-th Geometry (1-indexed). Returns NULL if out of bounds. | Immutable |

`st_geometrytype(geometry: geometry) → string` | Returns the type of geometry as a string prefixed with This function utilizes the GEOS module. | Immutable |

`st_geomfromewkb(val: bytes) → geometry` | Returns the Geometry from an EWKB representation. | Immutable |

`st_geomfromewkt(val: string) → geometry` | Returns the Geometry from an EWKT representation. | Immutable |

`st_geomfromgeohash(geohash: string) → geometry` | Return a POLYGON Geometry from a GeoHash string with max precision. | Immutable |

`st_geomfromgeohash(geohash: string, precision: int) → geometry` | Return a POLYGON Geometry from a GeoHash string with supplied precision. | Immutable |

`st_geomfromgeojson(val: string) → geometry` | Returns the Geometry from an GeoJSON representation. | Immutable |

`st_geomfromgeojson(val: jsonb) → geometry` | Returns the Geometry from an GeoJSON representation. | Immutable |

`st_geomfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_geomfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. | Immutable |

`st_geomfromwkb(bytes: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with the given SRID set. | Immutable |

`st_geomfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. | Immutable |

`st_hasarc(geometry: geometry) → bool` | Returns whether there is a CIRCULARSTRING in the geometry. | Immutable |

`st_hausdorffdistance(geometry_a: geometry, geometry_b: geometry) → float` | Returns the Hausdorff distance between the given geometries. This function utilizes the GEOS module. | Immutable |

`st_hausdorffdistance(geometry_a: geometry, geometry_b: geometry, densify_frac: float) → float` | Returns the Hausdorff distance between the given geometries, with the given segment densification (range 0.0-1.0). This function utilizes the GEOS module. | Immutable |

`st_interiorringn(geometry: geometry, n: int) → geometry` | Returns the n-th (1-indexed) interior ring of a Polygon as a LineString. Returns NULL if the shape is not a Polygon, or the ring does not exist. | Immutable |

`st_intersection(geography_a: geography, geography_b: geography) → geography` | Returns the point intersections of the given geographies. This function utilizes the GEOS module. | Immutable |

`st_intersection(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the point intersections of the given geometries. This function utilizes the GEOS module. | Immutable |

`st_intersection(geometry_a_str: string, geometry_b_str: string) → geometry` | Returns the point intersections of the given geometries. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_intersects(geography_a: geography, geography_b: geography) → bool` | Returns true if geography_a shares any portion of space with geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_intersects(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a shares any portion of space with geometry_b. The calculations performed are have a precision of 1cm. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_intersects(geometry_a_str: string, geometry_b_str: string) → bool` | Returns true if geometry_a shares any portion of space with geometry_b. The calculations performed are have a precision of 1cm. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_isclosed(geometry: geometry) → bool` | Returns whether the geometry is closed as defined by whether the start and end points are coincident. Points are considered closed, empty geometries are not. For collections and multi-types, all members must be closed, as must all polygon rings. | Immutable |

`st_iscollection(geometry: geometry) → bool` | Returns whether the geometry is of a collection type (including multi-types). | Immutable |

`st_isempty(geometry: geometry) → bool` | Returns whether the geometry is empty. | Immutable |

`st_ispolygonccw(geometry: geometry) → bool` | Returns whether the Polygon objects inside the Geometry have exterior rings in the counter-clockwise orientation and interior rings in the clockwise orientation. Non-Polygon objects are considered counter-clockwise. | Immutable |

`st_ispolygoncw(geometry: geometry) → bool` | Returns whether the Polygon objects inside the Geometry have exterior rings in the clockwise orientation and interior rings in the counter-clockwise orientation. Non-Polygon objects are considered clockwise. | Immutable |

`st_isring(geometry: geometry) → bool` | Returns whether the geometry is a single linestring that is closed and simple, as defined by ST_IsClosed and ST_IsSimple. This function utilizes the GEOS module. | Immutable |

`st_issimple(geometry: geometry) → bool` | Returns true if the geometry has no anomalous geometric points, e.g. that it intersects with or lies tangent to itself. This function utilizes the GEOS module. | Immutable |

`st_isvalid(geometry: geometry) → bool` | Returns whether the geometry is valid as defined by the OGC spec. This function utilizes the GEOS module. | Immutable |

`st_isvalid(geometry: geometry, flags: int) → bool` | Returns whether the geometry is valid. For flags=0, validity is defined by the OGC spec. For flags=1, validity considers self-intersecting rings forming holes as valid as per ESRI. This is not valid under OGC and CRDB spatial operations may not operate correctly. This function utilizes the GEOS module. | Immutable |

`st_isvalidreason(geometry: geometry) → string` | Returns a string containing the reason the geometry is invalid along with the point of interest, or “Valid Geometry” if it is valid. Validity is defined by the OGC spec. This function utilizes the GEOS module. | Immutable |

`st_isvalidreason(geometry: geometry, flags: int) → string` | Returns the reason the geometry is invalid or “Valid Geometry” if it is valid. For flags=0, validity is defined by the OGC spec. For flags=1, validity considers self-intersecting rings forming holes as valid as per ESRI. This is not valid under OGC and CRDB spatial operations may not operate correctly. This function utilizes the GEOS module. | Immutable |

`st_isvalidtrajectory(geometry: geometry) → bool` | Returns whether the geometry encodes a valid trajectory. Note the geometry must be a LineString with M coordinates. | Immutable |

`st_length(geography: geography) → float` | Returns the length of the given geography in meters. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_length(geography: geography, use_spheroid: bool) → float` | Returns the length of the given geography in meters. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_length(geometry: geometry) → float` | Returns the length of the given geometry. Note ST_Length is only valid for LineString - use ST_Perimeter for Polygon. This function utilizes the GEOS module. | Immutable |

`st_length(geometry_str: string) → float` | Returns the length of the given geometry. Note ST_Length is only valid for LineString - use ST_Perimeter for Polygon. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. | Immutable |

`st_length2d(geometry: geometry) → float` | Returns the length of the given geometry. Note ST_Length is only valid for LineString - use ST_Perimeter for Polygon. This function utilizes the GEOS module. | Immutable |

`st_linecrossingdirection(linestring_a: geometry, linestring_b: geometry) → int` | Returns an interger value defining behavior of crossing of lines: 0: lines do not cross, -1: linestring_b crosses linestring_a from right to left, 1: linestring_b crosses linestring_a from left to right, -2: linestring_b crosses linestring_a multiple times from right to left, 2: linestring_b crosses linestring_a multiple times from left to right, -3: linestring_b crosses linestring_a multiple times from left to left, 3: linestring_b crosses linestring_a multiple times from right to right. Note that the top vertex of the segment touching another line does not count as a crossing, but the bottom vertex of segment touching another line is considered a crossing. | Immutable |

`st_linefromencodedpolyline(encoded_polyline: string) → geometry` | Creates a LineString from an Encoded Polyline string. Returns valid results only if the polyline was encoded with 5 decimal places. See http://developers.google.com/maps/documentation/utilities/polylinealgorithm | Immutable |

`st_linefromencodedpolyline(encoded_polyline: string, precision: int4) → geometry` | Creates a LineString from an Encoded Polyline string. Precision specifies how many decimal places will be preserved in Encoded Polyline. Value should be the same on encoding and decoding, or coordinates will be incorrect. See http://developers.google.com/maps/documentation/utilities/polylinealgorithm | Immutable |

`st_linefrommultipoint(geometry: geometry) → geometry` | Creates a LineString from a MultiPoint geometry. | Immutable |

`st_linefromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not LineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_linefromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not LineString, NULL is returned. | Immutable |

`st_linefromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not LineString, NULL is returned. | Immutable |

`st_linefromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not LineString, NULL is returned. | Immutable |

`st_lineinterpolatepoint(geometry: geometry, fraction: float) → geometry` | Returns a point along the given LineString which is at given fraction of LineString’s total length. This function utilizes the GEOS module. | Immutable |

`st_lineinterpolatepoints(geometry: geometry, fraction: float) → geometry` | Returns one or more points along the LineString which is at an integral multiples of given fraction of LineString’s total length. Note If the result has zero or one points, it will be returned as a POINT. If it has two or more points, it will be returned as a MULTIPOINT. This function utilizes the GEOS module. | Immutable |

`st_lineinterpolatepoints(geometry: geometry, fraction: float, repeat: bool) → geometry` | Returns one or more points along the LineString which is at an integral multiples of given fraction of LineString’s total length. If repeat is false (default true) then it returns first point. Note If the result has zero or one points, it will be returned as a POINT. If it has two or more points, it will be returned as a MULTIPOINT. This function utilizes the GEOS module. | Immutable |

`st_linelocatepoint(line: geometry, point: geometry) → float` | Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length. | Immutable |

`st_linemerge(geometry: geometry) → geometry` | Returns a LineString or MultiLineString by joining together constituents of a MultiLineString with matching endpoints. If the input is not a MultiLineString or LineString, an empty GeometryCollection is returned. This function utilizes the GEOS module. | Immutable |

`st_linestringfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not LineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_linestringfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not LineString, NULL is returned. | Immutable |

`st_linestringfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not LineString, NULL is returned. | Immutable |

`st_linestringfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not LineString, NULL is returned. | Immutable |

`st_linesubstring(linestring: geometry, start_fraction: decimal, end_fraction: decimal) → geometry` | Return a linestring being a substring of the input one starting and ending at the given fractions of total 2D length. Second and third arguments are float8 values between 0 and 1. | Immutable |

`st_linesubstring(linestring: geometry, start_fraction: float, end_fraction: float) → geometry` | Return a linestring being a substring of the input one starting and ending at the given fractions of total 2D length. Second and third arguments are float8 values between 0 and 1. | Immutable |

`st_longestline(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the LineString corresponds to the max distance across every pair of points comprising the given geometries. Note if geometries are the same, it will return the LineString with the maximum distance between the geometry’s vertexes. The function will return the longest line that was discovered first when comparing maximum distances if more than one is found. | Immutable |

`st_m(geometry: geometry) → float` | Returns the M coordinate of a geometry if it is a Point. | Immutable |

`st_makebox2d(geometry_a: geometry, geometry_b: geometry) → box2d` | Creates a box2d from two points. Errors if arguments are not two non-empty points. | Immutable |

`st_makepoint(x: float, y: float) → geometry` | Returns a new Point with the given X and Y coordinates. | Immutable |

`st_makepoint(x: float, y: float, z: float) → geometry` | Returns a new Point with the given X, Y, and Z coordinates. | Immutable |

`st_makepoint(x: float, y: float, z: float, m: float) → geometry` | Returns a new Point with the given X, Y, Z, and M coordinates. | Immutable |

`st_makepointm(x: float, y: float, m: float) → geometry` | Returns a new Point with the given X, Y, and M coordinates. | Immutable |

`st_makepolygon(geometry: geometry) → geometry` | Returns a new Polygon with the given outer LineString. | Immutable |

`st_makepolygon(outer: geometry, interior: anyelement[]) → geometry` | Returns a new Polygon with the given outer LineString and interior (hole) LineString(s). | Immutable |

`st_makevalid(geometry: geometry) → geometry` | Returns a valid form of the given geometry according to the OGC spec. This function utilizes the GEOS module. | Immutable |

`st_maxdistance(geometry_a: geometry, geometry_b: geometry) → float` | Returns the maximum distance across every pair of points comprising the given geometries. Note if the geometries are the same, it will return the maximum distance between the geometry’s vertexes. | Immutable |

`st_memsize(geometry: geometry) → int` | Returns the amount of memory space (in bytes) the geometry takes. | Immutable |

`st_minimumboundingcircle(geometry: geometry) → geometry` | Returns the smallest circle polygon that can fully contain a geometry. | Immutable |

`st_minimumboundingcircle(geometry: geometry, num_segs: int) → geometry` | Returns the smallest circle polygon that can fully contain a geometry. | Immutable |

`st_minimumboundingradius(geometry: geometry) → tuple{geometry AS center, float AS radius}` | Returns a record containing the center point and radius of the smallest circle that can fully contains the given geometry. | Immutable |

`st_minimumclearance(geometry: geometry) → float` | Returns the minimum distance a vertex can move before producing an invalid geometry. Returns Infinity if no minimum clearance can be found (e.g. for a single point). | Immutable |

`st_minimumclearanceline(geometry: geometry) → geometry` | Returns a LINESTRING spanning the minimum distance a vertex can move before producing an invalid geometry. If no minimum clearance can be found (e.g. for a single point), an empty LINESTRING is returned. | Immutable |

`st_mlinefromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_mlinefromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_mlinefromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_mlinefromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_mpointfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_mpointfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPoint, NULL is returned. | Immutable |

`st_mpointfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPoint, NULL is returned. | Immutable |

`st_mpointfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. | Immutable |

`st_mpolyfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_mpolyfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_mpolyfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_mpolyfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_multi(geometry: geometry) → geometry` | Returns the geometry as a new multi-geometry, e.g converts a POINT to a MULTIPOINT. If the input is already a multitype or collection, it is returned as is. | Immutable |

`st_multilinefromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_multilinefromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_multilinefromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_multilinefromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_multilinestringfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_multilinestringfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_multilinestringfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_multilinestringfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. | Immutable |

`st_multipointfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_multipointfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPoint, NULL is returned. | Immutable |

`st_multipointfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPoint, NULL is returned. | Immutable |

`st_multipointfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. | Immutable |

`st_multipolyfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_multipolyfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_multipolyfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_multipolyfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_multipolygonfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_multipolygonfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_multipolygonfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_multipolygonfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. | Immutable |

`st_ndims(geometry: geometry) → int` | Returns the number of coordinate dimensions of a given Geometry. | Immutable |

`st_node(geometry: geometry) → geometry` | Adds a node on a geometry for each intersection. Resulting geometry is always a MultiLineString. | Immutable |

`st_normalize(geometry: geometry) → geometry` | Returns the geometry in its normalized form. This function utilizes the GEOS module. | Immutable |

`st_npoints(geometry: geometry) → int` | Returns the number of points in a given Geometry. Works for any shape type. | Immutable |

`st_nrings(geometry: geometry) → int` | Returns the number of rings in a Polygon Geometry. Returns 0 if the shape is not a Polygon. | Immutable |

`st_numgeometries(geometry: geometry) → int` | Returns the number of shapes inside a given Geometry. | Immutable |

`st_numinteriorring(geometry: geometry) → int` | Returns the number of interior rings in a Polygon Geometry. Returns NULL if the shape is not a Polygon. | Immutable |

`st_numinteriorrings(geometry: geometry) → int` | Returns the number of interior rings in a Polygon Geometry. Returns NULL if the shape is not a Polygon. | Immutable |

`st_numpoints(geometry: geometry) → int` | Returns the number of points in a LineString. Returns NULL if the Geometry is not a LineString. | Immutable |

`st_orderingequals(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a is exactly equal to geometry_b, having all coordinates in the same order, as well as the same type, SRID, bounding box, and so on. | Immutable |

`st_orientedenvelope(geometry: geometry) → geometry` | Returns a minimum rotated rectangle enclosing a geometry. Note that more than one minimum rotated rectangle may exist. May return a Point or LineString in the case of degenerate inputs. | Immutable |

`st_overlaps(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a intersects but does not completely contain geometry_b, or vice versa. “Does not completely” implies ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = false. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_perimeter(geography: geography) → float` | Returns the perimeter of the given geography in meters. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_perimeter(geography: geography, use_spheroid: bool) → float` | Returns the perimeter of the given geography in meters. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. | Immutable |

`st_perimeter(geometry: geometry) → float` | Returns the perimeter of the given geometry. Note ST_Perimeter is only valid for Polygon - use ST_Length for LineString. This function utilizes the GEOS module. | Immutable |

`st_perimeter2d(geometry: geometry) → float` | Returns the perimeter of the given geometry. Note ST_Perimeter is only valid for Polygon - use ST_Length for LineString. This function utilizes the GEOS module. | Immutable |

`st_point(x: float, y: float) → geometry` | Returns a new Point with the given X and Y coordinates. | Immutable |

`st_pointfromgeohash(geohash: string) → geometry` | Return a POINT Geometry from a GeoHash string with max precision. | Immutable |

`st_pointfromgeohash(geohash: string, precision: int) → geometry` | Return a POINT Geometry from a GeoHash string with supplied precision. | Immutable |

`st_pointfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Point, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_pointfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Point, NULL is returned. | Immutable |

`st_pointfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Point, NULL is returned. | Immutable |

`st_pointfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not Point, NULL is returned. | Immutable |

`st_pointinsidecircle(geometry: geometry, x_coord: float, y_coord: float, radius: float) → bool` | Returns the true if the geometry is a point and is inside the circle. Returns false otherwise. | Immutable |

`st_pointn(geometry: geometry, n: int) → geometry` | Returns the n-th Point of a LineString (1-indexed). Returns NULL if out of bounds or not a LineString. | Immutable |

`st_pointonsurface(geometry: geometry) → geometry` | Returns a point that intersects with the given Geometry. This function utilizes the GEOS module. | Immutable |

`st_points(geometry: geometry) → geometry` | Returns all coordinates in the given Geometry as a MultiPoint, including duplicates. | Immutable |

`st_polyfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Polygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_polyfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Polygon, NULL is returned. | Immutable |

`st_polyfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Polygon, NULL is returned. | Immutable |

`st_polyfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not Polygon, NULL is returned. | Immutable |

`st_polygon(geometry: geometry, srid: int) → geometry` | Returns a new Polygon from the given LineString and sets its SRID. It is equivalent to ST_MakePolygon with a single argument followed by ST_SetSRID. | Immutable |

`st_polygonfromtext(str: string, srid: int) → geometry` | Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Polygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. | Immutable |

`st_polygonfromtext(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Polygon, NULL is returned. | Immutable |

`st_polygonfromwkb(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Polygon, NULL is returned. | Immutable |

`st_polygonfromwkb(wkb: bytes, srid: int) → geometry` | Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not Polygon, NULL is returned. | Immutable |

`st_project(geography: geography, distance: float, azimuth: float) → geography` | Returns a point projected from a start point along a geodesic using a given distance and azimuth (bearing). This is known as the direct geodesic problem. The distance is given in meters. Negative values are supported. The azimuth (also known as heading or bearing) is given in radians. It is measured clockwise from true north (azimuth zero). East is azimuth π/2 (90 degrees); south is azimuth π (180 degrees); west is azimuth 3π/2 (270 degrees). Negative azimuth values and values greater than 2π (360 degrees) are supported. | Immutable |

`st_relate(geometry_a: geometry, geometry_b: geometry) → string` | Returns the DE-9IM spatial relation between geometry_a and geometry_b. This function utilizes the GEOS module. | Immutable |

`st_relate(geometry_a: geometry, geometry_b: geometry, bnr: int) → string` | Returns the DE-9IM spatial relation between geometry_a and geometry_b using the given boundary node rule (1:OGC/MOD2, 2:Endpoint, 3:MultivalentEndpoint, 4:MonovalentEndpoint). This function utilizes the GEOS module. | Immutable |

`st_relate(geometry_a: geometry, geometry_b: geometry, pattern: string) → bool` | Returns whether the DE-9IM spatial relation between geometry_a and geometry_b matches the DE-9IM pattern. This function utilizes the GEOS module. | Immutable |

`st_relatematch(intersection_matrix: string, pattern: string) → bool` | Returns whether the given DE-9IM intersection matrix satisfies the given pattern. | Immutable |

`st_removepoint(line_string: geometry, index: int) → geometry` | Removes the Point at the given 0-based index and returns the modified LineString geometry. | Immutable |

`st_removerepeatedpoints(geometry: geometry) → geometry` | Returns a geometry with repeated points removed. | Immutable |

`st_removerepeatedpoints(geometry: geometry, tolerance: float) → geometry` | Returns a geometry with repeated points removed, within the given distance tolerance. | Immutable |

`st_reverse(geometry: geometry) → geometry` | Returns a modified geometry by reversing the order of its vertices. | Immutable |

`st_rotate(g: geometry, angle_radians: float) → geometry` | Returns a modified Geometry whose coordinates are rotated around the origin by a rotation angle. | Immutable |

`st_rotate(g: geometry, angle_radians: float, origin_point: geometry) → geometry` | Returns a modified Geometry whose coordinates are rotated around the provided origin by a rotation angle. | Immutable |

`st_rotate(g: geometry, angle_radians: float, origin_x: float, origin_y: float) → geometry` | Returns a modified Geometry whose coordinates are rotated around the provided origin by a rotation angle. | Immutable |

`st_rotatex(g: geometry, angle_radians: float) → geometry` | Returns a modified Geometry whose coordinates are rotated about the x axis by a rotation angle. | Immutable |

`st_rotatey(g: geometry, angle_radians: float) → geometry` | Returns a modified Geometry whose coordinates are rotated about the y axis by a rotation angle. | Immutable |

`st_rotatez(g: geometry, angle_radians: float) → geometry` | Returns a modified Geometry whose coordinates are rotated about the z axis by a rotation angle. | Immutable |

`st_s2covering(geography: geography) → geography` | Returns a geography which represents the S2 covering used by the index using the default index configuration. | Immutable |

`st_s2covering(geography: geography, settings: string) → geography` | Returns a geography which represents the S2 covering used by the index using the index configuration specified by the settings parameter. The settings parameter uses the same format as the parameters inside the WITH in CREATE INDEX … WITH (…), e.g. CREATE INDEX t_idx ON t USING GIST(geom) WITH (s2_max_level=15, s2_level_mod=3) can be tried using SELECT ST_S2Covering(geography, ‘s2_max_level=15,s2_level_mod=3’). | Immutable |

`st_s2covering(geometry: geometry) → geometry` | Returns a geometry which represents the S2 covering used by the index using the default index configuration. | Immutable |

`st_s2covering(geometry: geometry, settings: string) → geometry` | Returns a geometry which represents the S2 covering used by the index using the index configuration specified by the settings parameter. The settings parameter uses the same format as the parameters inside the WITH in CREATE INDEX … WITH (…), e.g. CREATE INDEX t_idx ON t USING GIST(geom) WITH (s2_max_level=15, s2_level_mod=3) can be tried using SELECT ST_S2Covering(geometry, ‘s2_max_level=15,s2_level_mod=3’). | Immutable |

`st_scale(g: geometry, factor: geometry) → geometry` | Returns a modified Geometry scaled by taking in a Geometry as the factor. | Immutable |

`st_scale(g: geometry, factor: geometry, origin: geometry) → geometry` | Returns a modified Geometry scaled by the Geometry factor relative to a false origin. | Immutable |

`st_scale(geometry: geometry, x_factor: float, y_factor: float) → geometry` | Returns a modified Geometry scaled by the given factors. | Immutable |

`st_segmentize(geography: geography, max_segment_length_meters: float) → geography` | Returns a modified Geography having no segment longer than the given max_segment_length meters. The calculations are done on a sphere. This function utilizes the S2 library for spherical calculations. | Immutable |

`st_segmentize(geometry: geometry, max_segment_length: float) → geometry` | Returns a modified Geometry having no segment longer than the given max_segment_length. Length units are in units of spatial reference. | Immutable |

`st_setpoint(line_string: geometry, index: int, point: geometry) → geometry` | Sets the Point at the given 0-based index and returns the modified LineString geometry. | Immutable |

`st_setsrid(geography: geography, srid: int) → geography` | Sets a Geography to a new SRID without transforming the coordinates. | Immutable |

`st_setsrid(geometry: geometry, srid: int) → geometry` | Sets a Geometry to a new SRID without transforming the coordinates. | Immutable |

`st_sharedpaths(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns a collection containing paths shared by the two input geometries. Those going in the same direction are in the first element of the collection, those going in the opposite direction are in the second element. The paths themselves are given in the direction of the first geometry. | Immutable |

`st_shiftlongitude(geometry: geometry) → geometry` | Returns a modified version of a geometry in which the longitude (X coordinate) of each point is incremented by 360 if it is <0 and decremented by 360 if it is >180. The result is only meaningful if the coordinates are in longitude/latitude. | Immutable |

`st_shortestline(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the LineString corresponds to the minimum distance across every pair of points comprising the given geometries. Note if geometries are the same, it will return the LineString with the minimum distance between the geometry’s vertexes. The function will return the shortest line that was discovered first when comparing minimum distances if more than one is found. | Immutable |

`st_simplify(geometry: geometry, tolerance: float) → geometry` | Simplifies the given geometry using the Douglas-Peucker algorithm. This function utilizes the GEOS module. | Immutable |

`st_simplify(geometry: geometry, tolerance: float, preserve_collapsed: bool) → geometry` | Simplifies the given geometry using the Douglas-Peucker algorithm, retaining objects that would be too small given the tolerance if preserve_collapsed is set to true. | Immutable |

`st_simplifypreservetopology(geometry: geometry, tolerance: float) → geometry` | Simplifies the given geometry using the Douglas-Peucker algorithm, avoiding the creation of invalid geometries. This function utilizes the GEOS module. | Immutable |

`st_snap(input: geometry, target: geometry, tolerance: float) → geometry` | Snaps the vertices and segments of input geometry the target geometry’s vertices. Tolerance is used to control where snapping is performed. The result geometry is the input geometry with the vertices snapped. If no snapping occurs then the input geometry is returned unchanged. | Immutable |

`st_snaptogrid(geometry: geometry, origin: geometry, size_x: float, size_y: float, size_z: float, size_m: float) → geometry` | Snap a geometry to a grid defined by the given origin and X, Y, Z, and M cell sizes. Any dimension with a 0 cell size will not be snapped. | Immutable |

`st_snaptogrid(geometry: geometry, origin_x: float, origin_y: float, size_x: float, size_y: float) → geometry` | Snap a geometry to a grid of with X coordinates snapped to size_x and Y coordinates snapped to size_y based on an origin of (origin_x, origin_y). | Immutable |

`st_snaptogrid(geometry: geometry, size: float) → geometry` | Snap a geometry to a grid of the given size. The specified size is only used to snap X and Y coordinates. | Immutable |

`st_snaptogrid(geometry: geometry, size_x: float, size_y: float) → geometry` | Snap a geometry to a grid of with X coordinates snapped to size_x and Y coordinates snapped to size_y. | Immutable |

`st_srid(geography: geography) → int` | Returns the Spatial Reference Identifier (SRID) for the ST_Geography as defined in spatial_ref_sys table. | Immutable |

`st_srid(geometry: geometry) → int` | Returns the Spatial Reference Identifier (SRID) for the ST_Geometry as defined in spatial_ref_sys table. | Immutable |

`st_startpoint(geometry: geometry) → geometry` | Returns the first point of a geometry which has shape LineString. Returns NULL if the geometry is not a LineString. | Immutable |

`st_subdivide(geometry: geometry) → geometry` | Returns a geometry divided into parts, where each part contains no more than 256 vertices. | Immutable |

`st_subdivide(geometry: geometry, max_vertices: int4) → geometry` | Returns a geometry divided into parts, where each part contains no more than the number of vertices provided. | Immutable |

`st_summary(geography: geography) → string` | Returns a text summary of the contents of the geography. Flags shown square brackets after the geometry type have the following meaning: - M: has M coordinate
- Z: has Z coordinate
- B: has a cached bounding box
- G: is geography
- S: has spatial reference system
| Immutable |

`st_summary(geometry: geometry) → string` | Returns a text summary of the contents of the geometry. Flags shown square brackets after the geometry type have the following meaning: - M: has M coordinate
- Z: has Z coordinate
- B: has a cached bounding box
- G: is geography
- S: has spatial reference system
| Immutable |

`st_swapordinates(geometry: geometry, swap_ordinate_string: string) → geometry` | Returns a version of the given geometry with given ordinates swapped. The swap_ordinate_string parameter is a 2-character string naming the ordinates to swap. Valid names are: x, y, z and m. | Immutable |

`st_symdifference(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the symmetric difference of both geometries. This function utilizes the GEOS module. | Immutable |

`st_symmetricdifference(geometry_a: geometry, geometry_b: geometry) → geometry` | Returns the symmetric difference of both geometries. This function utilizes the GEOS module. | Immutable |

`st_touches(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if the only points in common between geometry_a and geometry_b are on the boundary. Note points do not touch other points. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_transform(geometry: geometry, from_proj_text: string, srid: int) → geometry` | Transforms a geometry into the coordinate reference system assuming the from_proj_text to the new to_proj_text by projecting its coordinates. The supplied SRID is set on the new geometry. This function utilizes the PROJ library for coordinate projections. | Immutable |

`st_transform(geometry: geometry, from_proj_text: string, to_proj_text: string) → geometry` | Transforms a geometry into the coordinate reference system assuming the from_proj_text to the new to_proj_text by projecting its coordinates. This function utilizes the PROJ library for coordinate projections. | Immutable |

`st_transform(geometry: geometry, srid: int) → geometry` | Transforms a geometry into the given SRID coordinate reference system by projecting its coordinates. This function utilizes the PROJ library for coordinate projections. | Immutable |

`st_transform(geometry: geometry, to_proj_text: string) → geometry` | Transforms a geometry into the coordinate reference system referenced by the projection text by projecting its coordinates. This function utilizes the PROJ library for coordinate projections. | Immutable |

`st_translate(g: geometry, delta_x: float, delta_y: float) → geometry` | Returns a modified Geometry translated by the given deltas. | Immutable |

`st_translate(g: geometry, delta_x: float, delta_y: float, delta_z: float) → geometry` | Returns a modified Geometry translated by the given deltas. | Immutable |

`st_transscale(geometry: geometry, delta_x: float, delta_y: float, x_factor: float, y_factor: float) → geometry` | Translates the geometry using the deltaX and deltaY args, then scales it using the XFactor, YFactor args, working in 2D only. | Immutable |

`st_unaryunion(geometry: geometry) → geometry` | Returns a union of the components for any geometry or geometry collection provided. Dissolves boundaries of a multipolygon. | Immutable |

`st_voronoilines(geometry: geometry) → geometry` | Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry asthe boundaries between cells in that diagram as a MultiLineString. | Immutable |

`st_voronoilines(geometry: geometry, tolerance: float) → geometry` | Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry asthe boundaries between cells in that diagram as a MultiLineString. | Immutable |

`st_voronoilines(geometry: geometry, tolerance: float, extend_to: geometry) → geometry` | Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry asthe boundaries between cells in that diagram as a MultiLineString. | Immutable |

`st_voronoipolygons(geometry: geometry) → geometry` | Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry. | Immutable |

`st_voronoipolygons(geometry: geometry, tolerance: float) → geometry` | Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry. | Immutable |

`st_voronoipolygons(geometry: geometry, tolerance: float, extend_to: geometry) → geometry` | Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry. | Immutable |

`st_within(geometry_a: geometry, geometry_b: geometry) → bool` | Returns true if geometry_a is completely inside geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. | Immutable |

`st_wkbtosql(val: bytes) → geometry` | Returns the Geometry from a WKB (or EWKB) representation. | Immutable |

`st_wkttosql(val: string) → geometry` | Returns the Geometry from a WKT or EWKT representation. | Immutable |

`st_x(geometry: geometry) → float` | Returns the X coordinate of a geometry if it is a Point. | Immutable |

`st_y(geometry: geometry) → float` | Returns the Y coordinate of a geometry if it is a Point. | Immutable |

`st_z(geometry: geometry) → float` | Returns the Z coordinate of a geometry if it is a Point. | Immutable |

`st_zmflag(geometry: geometry) → int2` | Returns a code based on the ZM coordinate dimension of a geometry (XY = 0, XYM = 1, XYZ = 2, XYZM = 3). | Immutable |

### Stream Ingestion functions

Function → Returns | Description | Volatility |
---|---|---|

`crdb_internal.complete_stream_ingestion_job(job_id: int, cutover_ts: timestamptz) → int` | This function can be used to signal a running stream ingestion job to complete. The job will eventually stop ingesting, revert to the specified timestamp and leave the cluster in a consistent state. The specified timestamp can only be specified up to the microsecond. This function does not wait for the job to reach a terminal state, but instead returns the job id as soon as it has signaled the job to complete. This builtin can be used in conjunction with SHOW JOBS WHEN COMPLETE to ensure that the job has left the cluster in a consistent state. | Volatile |

`crdb_internal.replication_stream_progress(stream_id: int, frontier_ts: string) → bytes` | This function can be used on the consumer side to heartbeat its replication progress to a replication stream in the source cluster. The returns a StreamReplicationStatus message that indicates stream status (RUNNING, PAUSED, or STOPPED). | Volatile |

`crdb_internal.replication_stream_spec(stream_id: int) → bytes` | This function can be used on the consumer side to get a replication stream specification for the specified stream starting from the specified ‘start_from’ timestamp. The consumer will later call ‘stream_partition’ to a partition with the spec to start streaming. | Volatile |

`crdb_internal.start_replication_stream(tenant_id: int) → int` | This function can be used on the producer side to start a replication stream for the specified tenant. The returned stream ID uniquely identifies created stream. The caller must periodically invoke crdb_internal.heartbeat_stream() function to notify that the replication is still ongoing. | Volatile |

`crdb_internal.stream_partition(stream_id: int, partition_spec: bytes) → tuple{bytes AS stream_event}` | Stream partition data | Volatile |

### String and byte functions

Function → Returns | Description | Volatility | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

`ascii(val: string) → int` | Returns the character code of the first character in | Immutable | |||||||||||||||||||||||||||||||

`bit_length(val: bytes) → int` | Calculates the number of bits used to represent | Immutable | |||||||||||||||||||||||||||||||

`bit_length(val: string) → int` | Calculates the number of bits used to represent | Immutable | |||||||||||||||||||||||||||||||

`bit_length(val: varbit) → int` | Calculates the number of bits used to represent | Immutable | |||||||||||||||||||||||||||||||

`btrim(input: string, trim_chars: string) → string` | Removes any characters included in For example, | Immutable | |||||||||||||||||||||||||||||||

`btrim(val: string) → string` | Removes all spaces from the beginning and end of | Immutable | |||||||||||||||||||||||||||||||

`char_length(val: bytes) → int` | Calculates the number of bytes in | Immutable | |||||||||||||||||||||||||||||||

`char_length(val: string) → int` | Calculates the number of characters in | Immutable | |||||||||||||||||||||||||||||||

`character_length(val: bytes) → int` | Calculates the number of bytes in | Immutable | |||||||||||||||||||||||||||||||

`character_length(val: string) → int` | Calculates the number of characters in | Immutable | |||||||||||||||||||||||||||||||

`chr(val: int) → string` | Returns the character with the code given in | Immutable | |||||||||||||||||||||||||||||||

`compress(data: bytes, codec: string) → bytes` | Compress | Immutable | |||||||||||||||||||||||||||||||

`concat(string...) → string` | Concatenates a comma-separated list of strings. | Immutable | |||||||||||||||||||||||||||||||

`concat_ws(string...) → string` | Uses the first argument as a separator between the concatenation of the subsequent arguments. For example | Immutable | |||||||||||||||||||||||||||||||

`convert_from(str: bytes, enc: string) → string` | Decode the bytes in | Immutable | |||||||||||||||||||||||||||||||

`convert_to(str: string, enc: string) → bytes` | Encode the string | Immutable | |||||||||||||||||||||||||||||||

`crdb_internal.decode_plan_gist(gist: string) → string` | Returns rows of output similar to EXPLAIN from a gist such as those found in planGists element of the statistics column of the statement_statistics table. | Volatile | |||||||||||||||||||||||||||||||

`crdb_internal.show_create_all_schemas(database_name: string) → string` | Returns rows of CREATE schema statements. The output can be used to recreate a database.’ | Volatile | |||||||||||||||||||||||||||||||

`crdb_internal.show_create_all_tables(database_name: string) → string` | Returns rows of CREATE table statements followed by ALTER table statements that add table constraints. The rows are ordered by dependencies. All foreign keys are added after the creation of the table in the alter statements. It is not recommended to perform this operation on a database with many tables. The output can be used to recreate a database.’ | Volatile | |||||||||||||||||||||||||||||||

`crdb_internal.show_create_all_types(database_name: string) → string` | Returns rows of CREATE type statements. The output can be used to recreate a database.’ | Volatile | |||||||||||||||||||||||||||||||

`decode(text: string, format: string) → bytes` | Decodes | Immutable | |||||||||||||||||||||||||||||||

`decompress(data: bytes, codec: string) → bytes` | Decompress | Immutable | |||||||||||||||||||||||||||||||

`difference(source: string, target: string) → string` | Convert two strings to their Soundex codes and then reports the number of matching code positions. | Immutable | |||||||||||||||||||||||||||||||

`encode(data: bytes, format: string) → string` | Encodes | Immutable | |||||||||||||||||||||||||||||||

`from_ip(val: bytes) → string` | Converts the byte string representation of an IP to its character string representation. | Immutable | |||||||||||||||||||||||||||||||

`from_uuid(val: bytes) → string` | Converts the byte string representation of a UUID to its character string representation. | Immutable | |||||||||||||||||||||||||||||||

`get_bit(bit_string: varbit, index: int) → int` | Extracts a bit at given index in the bit array. | Immutable | |||||||||||||||||||||||||||||||

`get_bit(byte_string: bytes, index: int) → int` | Extracts a bit at the given index in the byte array. | Immutable | |||||||||||||||||||||||||||||||

`get_byte(byte_string: bytes, index: int) → int` | Extracts a byte at the given index in the byte array. | Immutable | |||||||||||||||||||||||||||||||

`initcap(val: string) → string` | Capitalizes the first letter of | Immutable | |||||||||||||||||||||||||||||||

`left(input: bytes, return_set: int) → bytes` | Returns the first | Immutable | |||||||||||||||||||||||||||||||

`left(input: string, return_set: int) → string` | Returns the first | Immutable | |||||||||||||||||||||||||||||||

`length(val: bytes) → int` | Calculates the number of bytes in | Immutable | |||||||||||||||||||||||||||||||

`length(val: string) → int` | Calculates the number of characters in | Immutable | |||||||||||||||||||||||||||||||

`length(val: varbit) → int` | Calculates the number of bits in | Immutable | |||||||||||||||||||||||||||||||

`lower(val: string) → string` | Converts all characters in | Immutable | |||||||||||||||||||||||||||||||

`lpad(string: string, length: int) → string` | Pads | Immutable | |||||||||||||||||||||||||||||||

`lpad(string: string, length: int, fill: string) → string` | Pads | Immutable | |||||||||||||||||||||||||||||||

`ltrim(input: string, trim_chars: string) → string` | Removes any characters included in For example, | Immutable | |||||||||||||||||||||||||||||||

`ltrim(val: string) → string` | Removes all spaces from the beginning (left-hand side) of | Immutable | |||||||||||||||||||||||||||||||

`md5(bytes...) → string` | Calculates the MD5 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`md5(string...) → string` | Calculates the MD5 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`octet_length(val: bytes) → int` | Calculates the number of bytes used to represent | Immutable | |||||||||||||||||||||||||||||||

`octet_length(val: string) → int` | Calculates the number of bytes used to represent | Immutable | |||||||||||||||||||||||||||||||

`octet_length(val: varbit) → int` | Calculates the number of bits used to represent | Immutable | |||||||||||||||||||||||||||||||

`overlay(input: string, overlay_val: string, start_pos: int) → string` | Replaces characters in For example, | Immutable | |||||||||||||||||||||||||||||||

`overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string` | Deletes the characters in | Immutable | |||||||||||||||||||||||||||||||

`parse_date(string: string, datestyle: string) → date` | Parses a date assuming it is in format specified by DateStyle. | Immutable | |||||||||||||||||||||||||||||||

`parse_date(val: string) → date` | Parses a date assuming it is in MDY format. | Immutable | |||||||||||||||||||||||||||||||

`parse_interval(string: string, style: string) → interval` | Convert a string to an interval using the given IntervalStyle. | Immutable | |||||||||||||||||||||||||||||||

`parse_interval(val: string) → interval` | Convert a string to an interval assuming the Postgres IntervalStyle. | Immutable | |||||||||||||||||||||||||||||||

`parse_time(string: string, timestyle: string) → time` | Parses a time assuming the date (if any) is in format specified by DateStyle. | Immutable | |||||||||||||||||||||||||||||||

`parse_time(val: string) → time` | Parses a time assuming the date (if any) is in MDY format. | Immutable | |||||||||||||||||||||||||||||||

`parse_timestamp(string: string, datestyle: string) → timestamp` | Convert a string containing an absolute timestamp to the corresponding timestamp assuming dates formatted using the given DateStyle. | Immutable | |||||||||||||||||||||||||||||||

`parse_timestamp(val: string) → timestamp` | Convert a string containing an absolute timestamp to the corresponding timestamp assuming dates are in MDY format. | Immutable | |||||||||||||||||||||||||||||||

`parse_timetz(string: string, timestyle: string) → timetz` | Parses a timetz assuming the date (if any) is in format specified by DateStyle. | Immutable | |||||||||||||||||||||||||||||||

`parse_timetz(val: string) → timetz` | Parses a timetz assuming the date (if any) is in MDY format. | Immutable | |||||||||||||||||||||||||||||||

`prettify_statement(statement: string, line_width: int, align_mode: int, case_mode: int) → string` | Prettifies a statement using a user-configured pretty-printing config. Align mode values range from 0 - 3, representing no, partial, full, and extra alignment respectively. Case mode values range between 0 - 1, representing lower casing and upper casing respectively. | Immutable | |||||||||||||||||||||||||||||||

`prettify_statement(val: string) → string` | Prettifies a statement using a the default pretty-printing config. | Immutable | |||||||||||||||||||||||||||||||

`quote_ident(val: string) → string` | Return | Immutable | |||||||||||||||||||||||||||||||

`quote_literal(val: string) → string` | Return | Immutable | |||||||||||||||||||||||||||||||

`quote_literal(val: anyelement) → string` | Coerce | Stable | |||||||||||||||||||||||||||||||

`quote_nullable(val: string) → string` | Coerce | Immutable | |||||||||||||||||||||||||||||||

`quote_nullable(val: anyelement) → string` | Coerce | Stable | |||||||||||||||||||||||||||||||

`regexp_extract(input: string, regex: string) → string` | Returns the first match for the Regular Expression | Immutable | |||||||||||||||||||||||||||||||

`regexp_replace(input: string, regex: string, replace: string) → string` | Replaces matches for the Regular Expression | Immutable | |||||||||||||||||||||||||||||||

`regexp_replace(input: string, regex: string, replace: string, flags: string) → string` | Replaces matches for the regular expression CockroachDB supports the following flags:
| Immutable | |||||||||||||||||||||||||||||||

`repeat(input: string, repeat_counter: int) → string` | Concatenates For example, | Immutable | |||||||||||||||||||||||||||||||

`replace(input: string, find: string, replace: string) → string` | Replaces all occurrences of | Immutable | |||||||||||||||||||||||||||||||

`reverse(val: string) → string` | Reverses the order of the string’s characters. | Immutable | |||||||||||||||||||||||||||||||

`right(input: bytes, return_set: int) → bytes` | Returns the last | Immutable | |||||||||||||||||||||||||||||||

`right(input: string, return_set: int) → string` | Returns the last | Immutable | |||||||||||||||||||||||||||||||

`rpad(string: string, length: int) → string` | Pads | Immutable | |||||||||||||||||||||||||||||||

`rpad(string: string, length: int, fill: string) → string` | Pads | Immutable | |||||||||||||||||||||||||||||||

`rtrim(input: string, trim_chars: string) → string` | Removes any characters included in For example, | Immutable | |||||||||||||||||||||||||||||||

`rtrim(val: string) → string` | Removes all spaces from the end (right-hand side) of | Immutable | |||||||||||||||||||||||||||||||

`set_bit(bit_string: varbit, index: int, to_set: int) → varbit` | Updates a bit at given index in the bit array. | Immutable | |||||||||||||||||||||||||||||||

`set_bit(byte_string: bytes, index: int, to_set: int) → bytes` | Updates a bit at the given index in the byte array. | Immutable | |||||||||||||||||||||||||||||||

`set_byte(byte_string: bytes, index: int, to_set: int) → bytes` | Updates a byte at the given index in the byte array. | Immutable | |||||||||||||||||||||||||||||||

`sha1(bytes...) → string` | Calculates the SHA1 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha1(string...) → string` | Calculates the SHA1 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha224(bytes...) → string` | Calculates the SHA224 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha224(string...) → string` | Calculates the SHA224 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha256(bytes...) → string` | Calculates the SHA256 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha256(string...) → string` | Calculates the SHA256 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha384(bytes...) → string` | Calculates the SHA384 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha384(string...) → string` | Calculates the SHA384 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha512(bytes...) → string` | Calculates the SHA512 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`sha512(string...) → string` | Calculates the SHA512 hash value of a set of values. | Leakproof | |||||||||||||||||||||||||||||||

`similar_escape(pattern: string) → string` | Converts a SQL regexp | Immutable | |||||||||||||||||||||||||||||||

`similar_escape(pattern: string, escape: string) → string` | Converts a SQL regexp | Immutable | |||||||||||||||||||||||||||||||

`similar_to_escape(pattern: string) → string` | Converts a SQL regexp | Immutable | |||||||||||||||||||||||||||||||

`similar_to_escape(pattern: string, escape: string) → string` | Converts a SQL regexp | Immutable | |||||||||||||||||||||||||||||||

`similar_to_escape(unescaped: string, pattern: string, escape: string) → bool` | Matches | Immutable | |||||||||||||||||||||||||||||||

`soundex(source: string) → string` | Convert a string to its Soundex code. | Immutable | |||||||||||||||||||||||||||||||

`split_part(input: string, delimiter: string, return_index_pos: int) → string` | Splits For example, | Immutable | |||||||||||||||||||||||||||||||

`strpos(input: bytes, find: bytes) → int` | Calculates the position where the byte subarray | Immutable | |||||||||||||||||||||||||||||||

`strpos(input: string, find: string) → int` | Calculates the position where the string For example, | Immutable | |||||||||||||||||||||||||||||||

`strpos(input: varbit, find: varbit) → int` | Calculates the position where the bit subarray | Immutable | |||||||||||||||||||||||||||||||

`substr(input: bytes, start_pos: int) → bytes` | Returns a byte subarray of | Immutable | |||||||||||||||||||||||||||||||

`substr(input: bytes, start_pos: int, length: int) → bytes` | Returns a byte subarray of | Immutable | |||||||||||||||||||||||||||||||

`substr(input: string, regex: string) → string` | Returns a substring of | Immutable | |||||||||||||||||||||||||||||||

`substr(input: string, regex: string, escape_char: string) → string` | Returns a substring of ```
``` | Immutable | |||||||||||||||||||||||||||||||

`substr(input: string, start_pos: int) → string` | Returns a substring of | Immutable | |||||||||||||||||||||||||||||||

`substr(input: string, start_pos: int, length: int) → string` | Returns a substring of | Immutable | |||||||||||||||||||||||||||||||

`substr(input: varbit, start_pos: int) → varbit` | Returns a bit subarray of | Immutable | |||||||||||||||||||||||||||||||

`substr(input: varbit, start_pos: int, length: int) → varbit` | Returns a bit subarray of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: bytes, start_pos: int) → bytes` | Returns a byte subarray of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: bytes, start_pos: int, length: int) → bytes` | Returns a byte subarray of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: string, regex: string) → string` | Returns a substring of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: string, regex: string, escape_char: string) → string` | Returns a substring of ```
``` | Immutable | |||||||||||||||||||||||||||||||

`substring(input: string, start_pos: int) → string` | Returns a substring of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: string, start_pos: int, length: int) → string` | Returns a substring of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: varbit, start_pos: int) → varbit` | Returns a bit subarray of | Immutable | |||||||||||||||||||||||||||||||

`substring(input: varbit, start_pos: int, length: int) → varbit` | Returns a bit subarray of | Immutable | |||||||||||||||||||||||||||||||

`to_char_with_style(date: date, datestyle: string) → string` | Convert an date to a string assuming the string is formatted using the given DateStyle. | Immutable | |||||||||||||||||||||||||||||||

`to_char_with_style(interval: interval, style: string) → string` | Convert an interval to a string using the given IntervalStyle. | Immutable | |||||||||||||||||||||||||||||||

`to_char_with_style(timestamp: timestamp, datestyle: string) → string` | Convert an timestamp to a string assuming the string is formatted using the given DateStyle. | Immutable | |||||||||||||||||||||||||||||||

`to_english(val: int) → string` | This function enunciates the value of its argument using English cardinals. | Immutable | |||||||||||||||||||||||||||||||

`to_hex(val: bytes) → string` | Converts | Immutable | |||||||||||||||||||||||||||||||

`to_hex(val: int) → string` | Converts | Immutable | |||||||||||||||||||||||||||||||

`to_hex(val: string) → string` | Converts | Immutable | |||||||||||||||||||||||||||||||

`to_ip(val: string) → bytes` | Converts the character string representation of an IP to its byte string representation. | Immutable | |||||||||||||||||||||||||||||||

`to_uuid(val: string) → bytes` | Converts the character string representation of a UUID to its byte string representation. | Immutable | |||||||||||||||||||||||||||||||

`translate(input: string, find: string, replace: string) → string` | In For example, | Immutable | |||||||||||||||||||||||||||||||

`ulid_to_uuid(val: string) → uuid` | Converts a ULID string to its UUID-encoded representation. | Immutable | |||||||||||||||||||||||||||||||

`unaccent(val: string) → string` | Removes accents (diacritic signs) from the text provided in | Immutable | |||||||||||||||||||||||||||||||

`upper(val: string) → string` | Converts all characters in | Immutable |

### System info functions

Function → Returns | Description | Volatility |
---|---|---|

`cluster_logical_timestamp() → decimal` | Returns the logical time of the current transaction as a CockroachDB HLC in decimal form. Note that uses of this function disable server-side optimizations and may increase either contention or retry errors, or both. | Volatile |

`crdb_internal.active_version() → jsonb` | Returns the current active cluster version. | Volatile |

`crdb_internal.approximate_timestamp(timestamp: decimal) → timestamp` | Converts the crdb_internal_mvcc_timestamp column into an approximate timestamp. | Immutable |

`crdb_internal.assignment_cast(val: anyelement, type: anyelement) → anyelement` | This function is used internally to perform assignment casts during mutations. | Stable |

`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. Each returned row contains the range ID, the status (a roachpb.CheckConsistencyResponse_Status), and verbose detail. Example usage: SELECT * FROM crdb_internal.check_consistency(true, ‘\x02’, ‘\x04’) | Volatile |

`crdb_internal.check_password_hash_format(password: bytes) → string` | This function checks whether a string is a precomputed password hash. Returns the hash algorithm. | Immutable |

`crdb_internal.cluster_id() → uuid` | Returns the logical cluster ID for this tenant. | Stable |

`crdb_internal.cluster_name() → string` | Returns the cluster name. | Volatile |

`crdb_internal.cluster_setting_encoded_default(setting: string) → string` | Returns the encoded default value of the given cluster setting. | Immutable |

`crdb_internal.completed_migrations() → string[]` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.create_join_token() → string` | Creates a join token for use when adding a new node to a secure cluster. | Volatile |

`crdb_internal.create_session_revival_token() → bytes` | Generate a token that can be used to create a new session for the current user. | Volatile |

`crdb_internal.decode_cluster_setting(setting: string, value: string) → string` | Decodes the given encoded value for a cluster setting. | Immutable |

`crdb_internal.deserialize_session(session: bytes) → bool` | This function deserializes the serialized variables into the current session. | Volatile |

`crdb_internal.encode_key(table_id: int, index_id: int, row_tuple: anyelement) → bytes` | Generate the key for a row on a particular table and index. | Stable |

`crdb_internal.force_assertion_error(msg: string) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.force_error(errorCode: string, msg: string) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.force_log_fatal(msg: string) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.force_panic(msg: string) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.force_retry(val: interval) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.get_database_id(name: string) → int` | Stable | |

`crdb_internal.get_namespace_id(parent_id: int, name: string) → int` | Stable | |

`crdb_internal.get_namespace_id(parent_id: int, parent_schema_id: int, name: string) → int` | Stable | |

`crdb_internal.get_vmodule() → string` | Returns the vmodule configuration on the gateway node processing this request. | Volatile |

`crdb_internal.get_zone_config(namespace_id: int) → bytes` | Stable | |

`crdb_internal.has_role_option(option: string) → bool` | Returns whether the current user has the specified role option | Stable |

`crdb_internal.is_admin() → bool` | Retrieves the current user’s admin status. | Stable |

`crdb_internal.is_at_least_version(version: string) → bool` | Returns true if the cluster version is not older than the argument. | Volatile |

`crdb_internal.lease_holder(key: bytes) → int` | This function is used to fetch the leaseholder corresponding to a request key | Volatile |

`crdb_internal.list_sql_keys_in_range(range_id: int) → tuple{string AS key, string AS value}` | Returns all SQL K/V pairs within the requested range. | Volatile |

`crdb_internal.locality_value(key: string) → string` | Returns the value of the specified locality key. | Stable |

`crdb_internal.no_constant_folding(input: anyelement) → anyelement` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.node_executable_version() → string` | Returns the version of CockroachDB this node is running. | Volatile |

`crdb_internal.node_id() → int` | Returns the node ID. | Stable |

`crdb_internal.notice(msg: string) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.notice(severity: string, msg: string) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.num_geo_inverted_index_entries(table_id: int, index_id: int, val: geography) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Stable |

`crdb_internal.num_geo_inverted_index_entries(table_id: int, index_id: int, val: geometry) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Stable |

`crdb_internal.num_inverted_index_entries(val: anyelement[]) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Stable |

`crdb_internal.num_inverted_index_entries(val: anyelement[], version: int) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Stable |

`crdb_internal.num_inverted_index_entries(val: jsonb) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Stable |

`crdb_internal.num_inverted_index_entries(val: jsonb, version: int) → int` | This function is used only by CockroachDB’s developers for testing purposes. | Stable |

`crdb_internal.payloads_for_span(span_id: int) → tuple{string AS payload_type, jsonb AS payload_jsonb}` | Returns the payload(s) of the requested span and all its children. | Volatile |

`crdb_internal.payloads_for_trace(trace_id: int) → tuple{int AS span_id, string AS payload_type, jsonb AS payload_jsonb}` | Returns the payload(s) of the requested trace. | Volatile |

`crdb_internal.pretty_key(raw_key: bytes, skip_fields: int) → string` | This function is used only by CockroachDB’s developers for testing purposes. | Immutable |

`crdb_internal.pretty_span(raw_key_start: bytes, raw_key_end: bytes, skip_fields: int) → string` | This function is used only by CockroachDB’s developers for testing purposes. | Immutable |

`crdb_internal.range_stats(key: bytes) → jsonb` | This function is used to retrieve range statistics information as a JSON object. | Volatile |

`crdb_internal.repair_ttl_table_scheduled_job(oid: oid) → void` | Repairs the scheduled job for a TTL table if it is missing. | Volatile |

`crdb_internal.reset_index_usage_stats() → bool` | This function is used to clear the collected index usage statistics. | Volatile |

`crdb_internal.reset_sql_stats() → bool` | This function is used to clear the collected SQL statistics. | Volatile |

`crdb_internal.revalidate_unique_constraint(table_name: string, constraint_name: string) → void` | This function is used to revalidate the given unique constraint in the given table. Returns an error if validation fails. | Volatile |

`crdb_internal.revalidate_unique_constraints_in_all_tables() → void` | This function is used to revalidate all unique constraints in tables in the current database. Returns an error if validation fails. | Volatile |

`crdb_internal.revalidate_unique_constraints_in_table(table_name: string) → void` | This function is used to revalidate all unique constraints in the given table. Returns an error if validation fails. | Volatile |

`crdb_internal.round_decimal_values(val: decimal, scale: int) → decimal` | This function is used internally to round decimal values during mutations. | Immutable |

`crdb_internal.round_decimal_values(val: decimal[], scale: int) → decimal[]` | This function is used internally to round decimal array values during mutations. | Stable |

`crdb_internal.schedule_sql_stats_compaction(session: bytes) → bool` | This function is used to start a SQL stats compaction job. | Volatile |

`crdb_internal.serialize_session() → bytes` | This function serializes the variables in the current session. | Volatile |

`crdb_internal.set_trace_verbose(trace_id: int, verbosity: bool) → bool` | Returns true if root span was found and verbosity was set, false otherwise. | Volatile |

`crdb_internal.set_vmodule(vmodule_string: string) → int` | Set the equivalent of the | Volatile |

`crdb_internal.trace_id() → int` | Returns the current trace ID or an error if no trace is open. | Volatile |

`crdb_internal.unsafe_clear_gossip_info(key: string) → bool` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`crdb_internal.validate_session_revival_token(token: bytes) → bool` | Validate a token that was created by create_session_revival_token. Intended for testing. | Volatile |

`crdb_internal.validate_ttl_scheduled_jobs() → void` | Validate all TTL tables have a valid scheduled job attached. | Volatile |

`crdb_internal.void_func() → void` | This function is used only by CockroachDB’s developers for testing purposes. | Volatile |

`current_database() → string` | Returns the current database. | Stable |

`current_schema() → string` | Returns the current schema. | Stable |

`current_schemas(include_pg_catalog: bool) → string[]` | Returns the valid schemas in the search path. | Stable |

`current_user() → string` | Returns the current user. This function is provided for compatibility with PostgreSQL. | Stable |

`session_user() → string` | Returns the session user. This function is provided for compatibility with PostgreSQL. | Stable |

`version() → string` | Returns the node’s version of CockroachDB. | Volatile |

### System repair functions

Function → Returns | Description | Volatility |
---|---|---|

`crdb_internal.force_delete_table_data(id: int) → bool` | This function can be used to clear the data belonging to a table, when the table cannot be dropped. | Volatile |

### TIMETZ functions

Function → Returns | Description | Volatility |
---|---|---|

`current_time() → time` | Returns the current transaction’s time with no time zone. | Stable |

`current_time() → timetz` | Returns the current transaction’s time with time zone. This function is the preferred overload and will be evaluated by default. | Stable |

`current_time(precision: int) → time` | Returns the current transaction’s time with no time zone. | Stable |

`current_time(precision: int) → timetz` | Returns the current transaction’s time with time zone. This function is the preferred overload and will be evaluated by default. | Stable |

`localtime() → time` | Returns the current transaction’s time with no time zone. This function is the preferred overload and will be evaluated by default. | Stable |

`localtime() → timetz` | Returns the current transaction’s time with time zone. | Stable |

`localtime(precision: int) → time` | Returns the current transaction’s time with no time zone. This function is the preferred overload and will be evaluated by default. | Stable |

`localtime(precision: int) → timetz` | Returns the current transaction’s time with time zone. | Stable |

### UUID functions

Function → Returns | Description | Volatility |
---|---|---|

`uuid_to_ulid(val: uuid) → string` | Converts a UUID-encoded ULID to its string representation. | Immutable |

### Compatibility functions

Function → Returns | Description | Volatility |
---|---|---|

`current_setting(setting_name: string) → string` | System info | Stable |

`current_setting(setting_name: string, missing_ok: bool) → string` | System info | Stable |

`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. | Stable |

`getdatabaseencoding() → string` | Returns the current encoding name used by the database. | Stable |

`has_any_column_privilege(table: string, privilege: string) → bool` | Returns whether or not the current user has privileges for any column of table. | Stable |

`has_any_column_privilege(table: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for any column of table. | Stable |

`has_any_column_privilege(user: string, table: string, privilege: string) → bool` | Returns whether or not the user has privileges for any column of table. | Stable |

`has_any_column_privilege(user: string, table: oid, privilege: string) → bool` | Returns whether or not the user has privileges for any column of table. | Stable |

`has_any_column_privilege(user: oid, table: string, privilege: string) → bool` | Returns whether or not the user has privileges for any column of table. | Stable |

`has_any_column_privilege(user: oid, table: oid, privilege: string) → bool` | Returns whether or not the user has privileges for any column of table. | Stable |

`has_column_privilege(table: string, column: int, privilege: string) → bool` | Returns whether or not the current user has privileges for column. | Stable |

`has_column_privilege(table: string, column: string, privilege: string) → bool` | Returns whether or not the current user has privileges for column. | Stable |

`has_column_privilege(table: oid, column: int, privilege: string) → bool` | Returns whether or not the current user has privileges for column. | Stable |

`has_column_privilege(table: oid, column: string, privilege: string) → bool` | Returns whether or not the current user has privileges for column. | Stable |

`has_column_privilege(user: string, table: string, column: int, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: string, table: string, column: string, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: string, table: oid, column: int, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: string, table: oid, column: string, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: oid, table: string, column: int, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: oid, table: string, column: string, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: oid, table: oid, column: int, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_column_privilege(user: oid, table: oid, column: string, privilege: string) → bool` | Returns whether or not the user has privileges for column. | Stable |

`has_database_privilege(database: string, privilege: string) → bool` | Returns whether or not the current user has privileges for database. | Stable |

`has_database_privilege(database: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for database. | Stable |

`has_database_privilege(user: string, database: string, privilege: string) → bool` | Returns whether or not the user has privileges for database. | Stable |

`has_database_privilege(user: string, database: oid, privilege: string) → bool` | Returns whether or not the user has privileges for database. | Stable |

`has_database_privilege(user: oid, database: string, privilege: string) → bool` | Returns whether or not the user has privileges for database. | Stable |

`has_database_privilege(user: oid, database: oid, privilege: string) → bool` | Returns whether or not the user has privileges for database. | Stable |

`has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool` | Returns whether or not the current user has privileges for foreign-data wrapper. | Stable |

`has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for foreign-data wrapper. | Stable |

`has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool` | Returns whether or not the user has privileges for foreign-data wrapper. | Stable |

`has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool` | Returns whether or not the user has privileges for foreign-data wrapper. | Stable |

`has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool` | Returns whether or not the user has privileges for foreign-data wrapper. | Stable |

`has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool` | Returns whether or not the user has privileges for foreign-data wrapper. | Stable |

`has_function_privilege(function: string, privilege: string) → bool` | Returns whether or not the current user has privileges for function. | Stable |

`has_function_privilege(function: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for function. | Stable |

`has_function_privilege(user: string, function: string, privilege: string) → bool` | Returns whether or not the user has privileges for function. | Stable |

`has_function_privilege(user: string, function: oid, privilege: string) → bool` | Returns whether or not the user has privileges for function. | Stable |

`has_function_privilege(user: oid, function: string, privilege: string) → bool` | Returns whether or not the user has privileges for function. | Stable |

`has_function_privilege(user: oid, function: oid, privilege: string) → bool` | Returns whether or not the user has privileges for function. | Stable |

`has_language_privilege(language: string, privilege: string) → bool` | Returns whether or not the current user has privileges for language. | Stable |

`has_language_privilege(language: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for language. | Stable |

`has_language_privilege(user: string, language: string, privilege: string) → bool` | Returns whether or not the user has privileges for language. | Stable |

`has_language_privilege(user: string, language: oid, privilege: string) → bool` | Returns whether or not the user has privileges for language. | Stable |

`has_language_privilege(user: oid, language: string, privilege: string) → bool` | Returns whether or not the user has privileges for language. | Stable |

`has_language_privilege(user: oid, language: oid, privilege: string) → bool` | Returns whether or not the user has privileges for language. | Stable |

`has_schema_privilege(schema: string, privilege: string) → bool` | Returns whether or not the current user has privileges for schema. | Stable |

`has_schema_privilege(schema: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for schema. | Stable |

`has_schema_privilege(user: string, schema: string, privilege: string) → bool` | Returns whether or not the user has privileges for schema. | Stable |

`has_schema_privilege(user: string, schema: oid, privilege: string) → bool` | Returns whether or not the user has privileges for schema. | Stable |

`has_schema_privilege(user: oid, schema: string, privilege: string) → bool` | Returns whether or not the user has privileges for schema. | Stable |

`has_schema_privilege(user: oid, schema: oid, privilege: string) → bool` | Returns whether or not the user has privileges for schema. | Stable |

`has_sequence_privilege(sequence: string, privilege: string) → bool` | Returns whether or not the current user has privileges for sequence. | Stable |

`has_sequence_privilege(sequence: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for sequence. | Stable |

`has_sequence_privilege(user: string, sequence: string, privilege: string) → bool` | Returns whether or not the user has privileges for sequence. | Stable |

`has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool` | Returns whether or not the user has privileges for sequence. | Stable |

`has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool` | Returns whether or not the user has privileges for sequence. | Stable |

`has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool` | Returns whether or not the user has privileges for sequence. | Stable |

`has_server_privilege(server: string, privilege: string) → bool` | Returns whether or not the current user has privileges for foreign server. | Stable |

`has_server_privilege(server: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for foreign server. | Stable |

`has_server_privilege(user: string, server: string, privilege: string) → bool` | Returns whether or not the user has privileges for foreign server. | Stable |

`has_server_privilege(user: string, server: oid, privilege: string) → bool` | Returns whether or not the user has privileges for foreign server. | Stable |

`has_server_privilege(user: oid, server: string, privilege: string) → bool` | Returns whether or not the user has privileges for foreign server. | Stable |

`has_server_privilege(user: oid, server: oid, privilege: string) → bool` | Returns whether or not the user has privileges for foreign server. | Stable |

`has_table_privilege(table: string, privilege: string) → bool` | Returns whether or not the current user has privileges for table. | Stable |

`has_table_privilege(table: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for table. | Stable |

`has_table_privilege(user: string, table: string, privilege: string) → bool` | Returns whether or not the user has privileges for table. | Stable |

`has_table_privilege(user: string, table: oid, privilege: string) → bool` | Returns whether or not the user has privileges for table. | Stable |

`has_table_privilege(user: oid, table: string, privilege: string) → bool` | Returns whether or not the user has privileges for table. | Stable |

`has_table_privilege(user: oid, table: oid, privilege: string) → bool` | Returns whether or not the user has privileges for table. | Stable |

`has_tablespace_privilege(tablespace: string, privilege: string) → bool` | Returns whether or not the current user has privileges for tablespace. | Stable |

`has_tablespace_privilege(tablespace: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for tablespace. | Stable |

`has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool` | Returns whether or not the user has privileges for tablespace. | Stable |

`has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool` | Returns whether or not the user has privileges for tablespace. | Stable |

`has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool` | Returns whether or not the user has privileges for tablespace. | Stable |

`has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool` | Returns whether or not the user has privileges for tablespace. | Stable |

`has_type_privilege(type: string, privilege: string) → bool` | Returns whether or not the current user has privileges for type. | Stable |

`has_type_privilege(type: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for type. | Stable |

`has_type_privilege(user: string, type: string, privilege: string) → bool` | Returns whether or not the user has privileges for type. | Stable |

`has_type_privilege(user: string, type: oid, privilege: string) → bool` | Returns whether or not the user has privileges for type. | Stable |

`has_type_privilege(user: oid, type: string, privilege: string) → bool` | Returns whether or not the user has privileges for type. | Stable |

`has_type_privilege(user: oid, type: oid, privilege: string) → bool` | Returns whether or not the user has privileges for type. | Stable |

`information_schema._pg_numeric_precision(typid: oid, typmod: int4) → int` | Returns the precision of the given type with type modifier | Immutable |

`information_schema._pg_numeric_precision_radix(typid: oid, typmod: int4) → int` | Returns the radix of the given type with type modifier | Immutable |

`information_schema._pg_numeric_scale(typid: oid, typmod: int4) → int` | Returns the scale of the given type with type modifier | Immutable |

`oid(int: int) → oid` | Converts an integer to an OID. | Immutable |

`pg_backend_pid() → int` | Returns a numerical ID attached to this session. This ID is part of the query cancellation key used by the wire protocol. This function was only added for compatibility, and unlike in Postgres, the returned value does not correspond to a real process ID. | Stable |

`pg_collation_for(str: anyelement) → string` | Returns the collation of the argument | Stable |

`pg_column_is_updatable(reloid: oid, attnum: int2, include_triggers: bool) → bool` | Returns whether the given column can be updated. | Stable |

`pg_column_size(anyelement...) → int` | Return size in bytes of the column provided as an argument | Immutable |

`pg_get_serial_sequence(table_name: string, column_name: string) → string` | Returns the name of the sequence used by the given column_name in the table table_name. | Stable |

`pg_has_role(role: string, privilege: string) → bool` | Returns whether or not the current user has privileges for role. | Stable |

`pg_has_role(role: oid, privilege: string) → bool` | Returns whether or not the current user has privileges for role. | Stable |

`pg_has_role(user: string, role: string, privilege: string) → bool` | Returns whether or not the user has privileges for role. | Stable |

`pg_has_role(user: string, role: oid, privilege: string) → bool` | Returns whether or not the user has privileges for role. | Stable |

`pg_has_role(user: oid, role: string, privilege: string) → bool` | Returns whether or not the user has privileges for role. | Stable |

`pg_has_role(user: oid, role: oid, privilege: string) → bool` | Returns whether or not the user has privileges for role. | Stable |

`pg_is_other_temp_schema(oid: oid) → bool` | Returns true if the given OID is the OID of another session’s temporary schema. (This can be useful, for example, to exclude other sessions’ temporary tables from a catalog display.) | Stable |

`pg_my_temp_schema() → oid` | Returns the OID of the current session’s temporary schema, or zero if it has none (because it has not created any temporary tables). | Stable |

`pg_relation_is_updatable(reloid: oid, include_triggers: bool) → int4` | Returns the update events the relation supports. | Stable |

`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. | Volatile |

`pg_table_is_visible(oid: oid) → bool` | Returns whether the table with the given OID belongs to one of the schemas on the search path. | Stable |

`pg_type_is_visible(oid: oid) → bool` | Returns whether the type with the given OID belongs to one of the schemas on the search path. | Stable |

`set_config(setting_name: string, new_value: string, is_local: bool) → string` | System info | Volatile |

## Aggregate functions

For examples showing how to use aggregate functions, see the `SELECT`

clause documentation.

Non-commutative aggregate functions are sensitive to the order in which the rows are processed in the surrounding `SELECT`

clause. To specify the order in which input rows are processed, you can add an `ORDER BY`

clause within the function argument list. For examples, see the `SELECT`

clause documentation.

Function → Returns | Description | Volatility |
---|---|---|

`array_agg(arg1: bool) → bool[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: bytes) → bytes[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: date) → date[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: decimal) → decimal[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: float) → float[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: inet) → inet[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: int) → int[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: interval) → interval[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: string) → string[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: time) → time[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: timestamp) → timestamp[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: timestamptz) → timestamptz[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: uuid) → uuid[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: anyenum) → anyenum[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: box2d) → box2d[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: geography) → geography[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: geometry) → geometry[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: jsonb) → jsonb[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: oid) → oid[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: timetz) → timetz[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: tuple) → tuple[]` | Aggregates the selected values into an array. | Immutable |

`array_agg(arg1: varbit) → varbit[]` | Aggregates the selected values into an array. | Immutable |

`avg(arg1: decimal) → decimal` | Calculates the average of the selected values. | Immutable |

`avg(arg1: float) → float` | Calculates the average of the selected values. | Immutable |

`avg(arg1: int) → decimal` | Calculates the average of the selected values. | Immutable |

`avg(arg1: interval) → interval` | Calculates the average of the selected values. | Immutable |

`bit_and(arg1: int) → int` | Calculates the bitwise AND of all non-null input values, or null if none. | Immutable |

`bit_and(arg1: varbit) → varbit` | Calculates the bitwise AND of all non-null input values, or null if none. | Immutable |

`bit_or(arg1: int) → int` | Calculates the bitwise OR of all non-null input values, or null if none. | Immutable |

`bit_or(arg1: varbit) → varbit` | Calculates the bitwise OR of all non-null input values, or null if none. | Immutable |

`bool_and(arg1: bool) → bool` | Calculates the boolean value of | Immutable |

`bool_or(arg1: bool) → bool` | Calculates the boolean value of | Immutable |

`concat_agg(arg1: bytes) → bytes` | Concatenates all selected values. | Immutable |

`concat_agg(arg1: string) → string` | Concatenates all selected values. | Immutable |

`corr(arg1: decimal, arg2: decimal) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: decimal, arg2: float) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: decimal, arg2: int) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: float, arg2: decimal) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: float, arg2: float) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: float, arg2: int) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: int, arg2: decimal) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: int, arg2: float) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`corr(arg1: int, arg2: int) → float` | Calculates the correlation coefficient of the selected values. | Immutable |

`count(arg1: anyelement) → int` | Calculates the number of selected elements. | Immutable |

`count_rows() → int` | Calculates the number of rows. | Immutable |

`covar_pop(arg1: decimal, arg2: decimal) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: decimal, arg2: float) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: decimal, arg2: int) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: float, arg2: decimal) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: float, arg2: float) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: float, arg2: int) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: int, arg2: decimal) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: int, arg2: float) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_pop(arg1: int, arg2: int) → float` | Calculates the population covariance of the selected values. | Immutable |

`covar_samp(arg1: decimal, arg2: decimal) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: decimal, arg2: float) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: decimal, arg2: int) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: float, arg2: decimal) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: float, arg2: float) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: float, arg2: int) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: int, arg2: decimal) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: int, arg2: float) → float` | Calculates the sample covariance of the selected values. | Immutable |

`covar_samp(arg1: int, arg2: int) → float` | Calculates the sample covariance of the selected values. | Immutable |

`every(arg1: bool) → bool` | Calculates the boolean value of | Immutable |

`json_agg(arg1: anyelement) → jsonb` | Aggregates values as a JSON or JSONB array. | Stable |

`json_object_agg(arg1: string, arg2: anyelement) → jsonb` | Aggregates values as a JSON or JSONB object. | Stable |

`jsonb_agg(arg1: anyelement) → jsonb` | Aggregates values as a JSON or JSONB array. | Stable |

`jsonb_object_agg(arg1: string, arg2: anyelement) → jsonb` | Aggregates values as a JSON or JSONB object. | Stable |

`max(arg1: bool) → bool` | Identifies the maximum selected value. | Immutable |

`max(arg1: bytes) → bytes` | Identifies the maximum selected value. | Immutable |

`max(arg1: date) → date` | Identifies the maximum selected value. | Immutable |

`max(arg1: decimal) → decimal` | Identifies the maximum selected value. | Immutable |

`max(arg1: float) → float` | Identifies the maximum selected value. | Immutable |

`max(arg1: inet) → inet` | Identifies the maximum selected value. | Immutable |

`max(arg1: int) → int` | Identifies the maximum selected value. | Immutable |

`max(arg1: interval) → interval` | Identifies the maximum selected value. | Immutable |

`max(arg1: string) → string` | Identifies the maximum selected value. | Immutable |

`max(arg1: time) → time` | Identifies the maximum selected value. | Immutable |

`max(arg1: timestamp) → timestamp` | Identifies the maximum selected value. | Immutable |

`max(arg1: timestamptz) → timestamptz` | Identifies the maximum selected value. | Immutable |

`max(arg1: uuid) → uuid` | Identifies the maximum selected value. | Immutable |

`max(arg1: anyenum) → anyenum` | Identifies the maximum selected value. | Immutable |

`max(arg1: box2d) → box2d` | Identifies the maximum selected value. | Immutable |

`max(arg1: collatedstring{*}) → collatedstring{*}` | Identifies the maximum selected value. | Immutable |

`max(arg1: geography) → geography` | Identifies the maximum selected value. | Immutable |

`max(arg1: geometry) → geometry` | Identifies the maximum selected value. | Immutable |

`max(arg1: jsonb) → jsonb` | Identifies the maximum selected value. | Immutable |

`max(arg1: oid) → oid` | Identifies the maximum selected value. | Immutable |

`max(arg1: timetz) → timetz` | Identifies the maximum selected value. | Immutable |

`max(arg1: varbit) → varbit` | Identifies the maximum selected value. | Immutable |

`min(arg1: bool) → bool` | Identifies the minimum selected value. | Immutable |

`min(arg1: bytes) → bytes` | Identifies the minimum selected value. | Immutable |

`min(arg1: date) → date` | Identifies the minimum selected value. | Immutable |

`min(arg1: decimal) → decimal` | Identifies the minimum selected value. | Immutable |

`min(arg1: float) → float` | Identifies the minimum selected value. | Immutable |

`min(arg1: inet) → inet` | Identifies the minimum selected value. | Immutable |

`min(arg1: int) → int` | Identifies the minimum selected value. | Immutable |

`min(arg1: interval) → interval` | Identifies the minimum selected value. | Immutable |

`min(arg1: string) → string` | Identifies the minimum selected value. | Immutable |

`min(arg1: time) → time` | Identifies the minimum selected value. | Immutable |

`min(arg1: timestamp) → timestamp` | Identifies the minimum selected value. | Immutable |

`min(arg1: timestamptz) → timestamptz` | Identifies the minimum selected value. | Immutable |

`min(arg1: uuid) → uuid` | Identifies the minimum selected value. | Immutable |

`min(arg1: anyenum) → anyenum` | Identifies the minimum selected value. | Immutable |

`min(arg1: box2d) → box2d` | Identifies the minimum selected value. | Immutable |

`min(arg1: collatedstring{*}) → collatedstring{*}` | Identifies the minimum selected value. | Immutable |

`min(arg1: geography) → geography` | Identifies the minimum selected value. | Immutable |

`min(arg1: geometry) → geometry` | Identifies the minimum selected value. | Immutable |

`min(arg1: jsonb) → jsonb` | Identifies the minimum selected value. | Immutable |

`min(arg1: oid) → oid` | Identifies the minimum selected value. | Immutable |

`min(arg1: timetz) → timetz` | Identifies the minimum selected value. | Immutable |

`min(arg1: varbit) → varbit` | Identifies the minimum selected value. | Immutable |

`percentile_cont(arg1: float) → float` | Continuous percentile: returns a float corresponding to the specified fraction in the ordering, interpolating between adjacent input floats if needed. | Immutable |

`percentile_cont(arg1: float) → interval` | Continuous percentile: returns an interval corresponding to the specified fraction in the ordering, interpolating between adjacent input intervals if needed. | Immutable |

`percentile_cont(arg1: float[]) → float[]` | Continuous percentile: returns floats corresponding to the specified fractions in the ordering, interpolating between adjacent input floats if needed. | Immutable |

`percentile_cont(arg1: float[]) → interval[]` | Continuous percentile: returns intervals corresponding to the specified fractions in the ordering, interpolating between adjacent input intervals if needed. | Immutable |

`percentile_disc(arg1: float) → anyelement` | Discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction. | Immutable |

`percentile_disc(arg1: float[]) → anyelement` | Discrete percentile: returns input values whose position in the ordering equals or exceeds the specified fractions. | Immutable |

`regr_avgx(arg1: decimal, arg2: decimal) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: decimal, arg2: float) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: decimal, arg2: int) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: float, arg2: decimal) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: float, arg2: float) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: float, arg2: int) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: int, arg2: decimal) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: int, arg2: float) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgx(arg1: int, arg2: int) → float` | Calculates the average of the independent variable (sum(X)/N). | Immutable |

`regr_avgy(arg1: decimal, arg2: decimal) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: decimal, arg2: float) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: decimal, arg2: int) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: float, arg2: decimal) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: float, arg2: float) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: float, arg2: int) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: int, arg2: decimal) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: int, arg2: float) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_avgy(arg1: int, arg2: int) → float` | Calculates the average of the dependent variable (sum(Y)/N). | Immutable |

`regr_count(arg1: decimal, arg2: decimal) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: decimal, arg2: float) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: decimal, arg2: int) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: float, arg2: decimal) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: float, arg2: float) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: float, arg2: int) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: int, arg2: decimal) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: int, arg2: float) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_count(arg1: int, arg2: int) → int` | Calculates number of input rows in which both expressions are nonnull. | Immutable |

`regr_intercept(arg1: decimal, arg2: decimal) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: decimal, arg2: float) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: decimal, arg2: int) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: float, arg2: decimal) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: float, arg2: float) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: float, arg2: int) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: int, arg2: decimal) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: int, arg2: float) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_intercept(arg1: int, arg2: int) → float` | Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_r2(arg1: decimal, arg2: decimal) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: decimal, arg2: float) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: decimal, arg2: int) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: float, arg2: decimal) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: float, arg2: float) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: float, arg2: int) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: int, arg2: decimal) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: int, arg2: float) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_r2(arg1: int, arg2: int) → float` | Calculates square of the correlation coefficient. | Immutable |

`regr_slope(arg1: decimal, arg2: decimal) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: decimal, arg2: float) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: decimal, arg2: int) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: float, arg2: decimal) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: float, arg2: float) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: float, arg2: int) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: int, arg2: decimal) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: int, arg2: float) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_slope(arg1: int, arg2: int) → float` | Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs. | Immutable |

`regr_sxx(arg1: decimal, arg2: decimal) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: decimal, arg2: float) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: decimal, arg2: int) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: float, arg2: decimal) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: float, arg2: float) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: float, arg2: int) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: int, arg2: decimal) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: int, arg2: float) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxx(arg1: int, arg2: int) → float` | Calculates sum of squares of the independent variable. | Immutable |

`regr_sxy(arg1: decimal, arg2: decimal) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: decimal, arg2: float) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: decimal, arg2: int) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: float, arg2: decimal) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: float, arg2: float) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: float, arg2: int) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: int, arg2: decimal) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: int, arg2: float) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_sxy(arg1: int, arg2: int) → float` | Calculates sum of products of independent times dependent variable. | Immutable |

`regr_syy(arg1: decimal, arg2: decimal) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: decimal, arg2: float) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: decimal, arg2: int) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: float, arg2: decimal) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: float, arg2: float) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: float, arg2: int) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: int, arg2: decimal) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: int, arg2: float) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`regr_syy(arg1: int, arg2: int) → float` | Calculates sum of squares of the dependent variable. | Immutable |

`sqrdiff(arg1: decimal) → decimal` | Calculates the sum of squared differences from the mean of the selected values. | Immutable |

`sqrdiff(arg1: float) → float` | Calculates the sum of squared differences from the mean of the selected values. | Immutable |

`sqrdiff(arg1: int) → decimal` | Calculates the sum of squared differences from the mean of the selected values. | Immutable |

`st_collect(arg1: geometry) → geometry` | Collects geometries into a GeometryCollection or multi-type as appropriate. | Immutable |

`st_extent(arg1: geometry) → box2d` | Forms a Box2D that encapsulates all provided geometries. | Immutable |

`st_makeline(arg1: geometry) → geometry` | Forms a LineString from Point, MultiPoint or LineStrings. Other shapes will be ignored. | Immutable |

`st_memcollect(arg1: geometry) → geometry` | Collects geometries into a GeometryCollection or multi-type as appropriate. | Immutable |

`st_memunion(arg1: geometry) → geometry` | Applies a spatial union to the geometries provided. | Immutable |

`st_union(arg1: geometry) → geometry` | Applies a spatial union to the geometries provided. | Immutable |

`stddev(arg1: decimal) → decimal` | Calculates the standard deviation of the selected values. | Immutable |

`stddev(arg1: float) → float` | Calculates the standard deviation of the selected values. | Immutable |

`stddev(arg1: int) → decimal` | Calculates the standard deviation of the selected values. | Immutable |

`stddev_pop(arg1: decimal) → decimal` | Calculates the population standard deviation of the selected values. | Immutable |

`stddev_pop(arg1: float) → float` | Calculates the population standard deviation of the selected values. | Immutable |

`stddev_pop(arg1: int) → decimal` | Calculates the population standard deviation of the selected values. | Immutable |

`stddev_samp(arg1: decimal) → decimal` | Calculates the standard deviation of the selected values. | Immutable |

`stddev_samp(arg1: float) → float` | Calculates the standard deviation of the selected values. | Immutable |

`stddev_samp(arg1: int) → decimal` | Calculates the standard deviation of the selected values. | Immutable |

`string_agg(arg1: bytes, arg2: bytes) → bytes` | Concatenates all selected values using the provided delimiter. | Immutable |

`string_agg(arg1: string, arg2: string) → string` | Concatenates all selected values using the provided delimiter. | Immutable |

`sum(arg1: decimal) → decimal` | Calculates the sum of the selected values. | Immutable |

`sum(arg1: float) → float` | Calculates the sum of the selected values. | Immutable |

`sum(arg1: int) → decimal` | Calculates the sum of the selected values. | Immutable |

`sum(arg1: interval) → interval` | Calculates the sum of the selected values. | Immutable |

`sum_int(arg1: int) → int` | Calculates the sum of the selected values. | Immutable |

`var_pop(arg1: decimal) → decimal` | Calculates the population variance of the selected values. | Immutable |

`var_pop(arg1: float) → float` | Calculates the population variance of the selected values. | Immutable |

`var_pop(arg1: int) → decimal` | Calculates the population variance of the selected values. | Immutable |

`var_samp(arg1: decimal) → decimal` | Calculates the variance of the selected values. | Immutable |

`var_samp(arg1: float) → float` | Calculates the variance of the selected values. | Immutable |

`var_samp(arg1: int) → decimal` | Calculates the variance of the selected values. | Immutable |

`variance(arg1: decimal) → decimal` | Calculates the variance of the selected values. | Immutable |

`variance(arg1: float) → float` | Calculates the variance of the selected values. | Immutable |

`variance(arg1: int) → decimal` | Calculates the variance of the selected values. | Immutable |

`xor_agg(arg1: bytes) → bytes` | Calculates the bitwise XOR of the selected values. | Immutable |

`xor_agg(arg1: int) → int` | Calculates the bitwise XOR of the selected values. | Immutable |

## Window functions

Function → Returns | Description | Volatility |
---|---|---|

`cume_dist() → float` | Calculates the relative rank of the current row: (number of rows preceding or peer with current row) / (total rows). | Immutable |

`dense_rank() → int` | Calculates the rank of the current row without gaps; this function counts peer groups. | Immutable |

`first_value(val: bool) → bool` | Returns | Immutable |

`first_value(val: bytes) → bytes` | Returns | Immutable |

`first_value(val: date) → date` | Returns | Immutable |

`first_value(val: decimal) → decimal` | Returns | Immutable |

`first_value(val: float) → float` | Returns | Immutable |

`first_value(val: inet) → inet` | Returns | Immutable |

`first_value(val: int) → int` | Returns | Immutable |

`first_value(val: interval) → interval` | Returns | Immutable |

`first_value(val: string) → string` | Returns | Immutable |

`first_value(val: time) → time` | Returns | Immutable |

`first_value(val: timestamp) → timestamp` | Returns | Immutable |

`first_value(val: timestamptz) → timestamptz` | Returns | Immutable |

`first_value(val: uuid) → uuid` | Returns | Immutable |

`first_value(val: box2d) → box2d` | Returns | Immutable |

`first_value(val: geography) → geography` | Returns | Immutable |

`first_value(val: geometry) → geometry` | Returns | Immutable |

`first_value(val: jsonb) → jsonb` | Returns | Immutable |

`first_value(val: oid) → oid` | Returns | Immutable |

`first_value(val: timetz) → timetz` | Returns | Immutable |

`first_value(val: varbit) → varbit` | Returns | Immutable |

`lag(val: bool) → bool` | Returns | Immutable |

`lag(val: bool, n: int) → bool` | Returns | Immutable |

`lag(val: bool, n: int, default: bool) → bool` | Returns | Immutable |

`lag(val: bytes) → bytes` | Returns | Immutable |

`lag(val: bytes, n: int) → bytes` | Returns | Immutable |

`lag(val: bytes, n: int, default: bytes) → bytes` | Returns | Immutable |

`lag(val: date) → date` | Returns | Immutable |

`lag(val: date, n: int) → date` | Returns | Immutable |

`lag(val: date, n: int, default: date) → date` | Returns | Immutable |

`lag(val: decimal) → decimal` |
| Immutable |

`lag(val: decimal, n: int) → decimal` |
| Immutable |

`lag(val: decimal, n: int, default: decimal) → decimal` |
| Immutable |

`lag(val: float) → float` |
| Immutable |

`lag(val: float, n: int) → float` |
| Immutable |

`lag(val: float, n: int, default: float) → float` |
| Immutable |

`lag(val: inet) → inet` |
| Immutable |

`lag(val: inet, n: int) → inet` |
| Immutable |

`lag(val: inet, n: int, default: inet) → inet` |
| Immutable |

`lag(val: int) → int` |
| Immutable |

`lag(val: int, n: int) → int` |
| Immutable |

`lag(val: int, n: int, default: int) → int` |
| Immutable |

`lag(val: interval) → interval` |
| Immutable |

`lag(val: interval, n: int) → interval` |
| Immutable |

`lag(val: interval, n: int, default: interval) → interval` |
| Immutable |

`lag(val: string) → string` |
| Immutable |

`lag(val: string, n: int) → string` |
| Immutable |

`lag(val: string, n: int, default: string) → string` |
| Immutable |

`lag(val: time) → time` |
| Immutable |

`lag(val: time, n: int) → time` |
| Immutable |

`lag(val: time, n: int, default: time) → time` |
| Immutable |

`lag(val: timestamp) → timestamp` |
| Immutable |

`lag(val: timestamp, n: int) → timestamp` |
| Immutable |

`lag(val: timestamp, n: int, default: timestamp) → timestamp` |
| Immutable |

`lag(val: timestamptz) → timestamptz` |
| Immutable |

`lag(val: timestamptz, n: int) → timestamptz` |
| Immutable |

`lag(val: timestamptz, n: int, default: timestamptz) → timestamptz` |
| Immutable |

`lag(val: uuid) → uuid` |
| Immutable |

`lag(val: uuid, n: int) → uuid` |
| Immutable |

`lag(val: uuid, n: int, default: uuid) → uuid` |
| Immutable |

`lag(val: box2d) → box2d` |
| Immutable |

`lag(val: box2d, n: int) → box2d` |
| Immutable |

`lag(val: box2d, n: int, default: box2d) → box2d` |
| Immutable |

`lag(val: geography) → geography` |
| Immutable |

`lag(val: geography, n: int) → geography` |
| Immutable |

`lag(val: geography, n: int, default: geography) → geography` |
| Immutable |

`lag(val: geometry) → geometry` |
| Immutable |

`lag(val: geometry, n: int) → geometry` |
| Immutable |

`lag(val: geometry, n: int, default: geometry) → geometry` |
| Immutable |

`lag(val: jsonb) → jsonb` |
| Immutable |

`lag(val: jsonb, n: int) → jsonb` |
| Immutable |

`lag(val: jsonb, n: int, default: jsonb) → jsonb` |
| Immutable |

`lag(val: oid) → oid` |
| Immutable |

`lag(val: oid, n: int) → oid` |
| Immutable |

`lag(val: oid, n: int, default: oid) → oid` |
| Immutable |

`lag(val: timetz) → timetz` |
| Immutable |

`lag(val: timetz, n: int) → timetz` |
| Immutable |

`lag(val: timetz, n: int, default: timetz) → timetz` |
| Immutable |

`lag(val: varbit) → varbit` |
| Immutable |

`lag(val: varbit, n: int) → varbit` |
| Immutable |

`lag(val: varbit, n: int, default: varbit) → varbit` |
| Immutable |

`last_value(val: bool) → bool` | Returns | Immutable |

`last_value(val: bytes) → bytes` | Returns | Immutable |

`last_value(val: date) → date` | Returns | Immutable |

`last_value(val: decimal) → decimal` | Returns | Immutable |

`last_value(val: float) → float` | Returns | Immutable |

`last_value(val: inet) → inet` | Returns | Immutable |

`last_value(val: int) → int` | Returns | Immutable |

`last_value(val: interval) → interval` | Returns | Immutable |

`last_value(val: string) → string` | Returns | Immutable |

`last_value(val: time) → time` | Returns | Immutable |

`last_value(val: timestamp) → timestamp` | Returns | Immutable |

`last_value(val: timestamptz) → timestamptz` | Returns | Immutable |

`last_value(val: uuid) → uuid` | Returns | Immutable |

`last_value(val: box2d) → box2d` | Returns | Immutable |

`last_value(val: geography) → geography` | Returns | Immutable |

`last_value(val: geometry) → geometry` | Returns | Immutable |

`last_value(val: jsonb) → jsonb` | Returns | Immutable |

`last_value(val: oid) → oid` | Returns | Immutable |

`last_value(val: timetz) → timetz` | Returns | Immutable |

`last_value(val: varbit) → varbit` | Returns | Immutable |

`lead(val: bool) → bool` | Returns | Immutable |

`lead(val: bool, n: int) → bool` | Returns | Immutable |

`lead(val: bool, n: int, default: bool) → bool` | Returns | Immutable |

`lead(val: bytes) → bytes` | Returns | Immutable |

`lead(val: bytes, n: int) → bytes` | Returns | Immutable |

`lead(val: bytes, n: int, default: bytes) → bytes` | Returns | Immutable |

`lead(val: date) → date` | Returns | Immutable |

`lead(val: date, n: int) → date` | Returns | Immutable |

`lead(val: date, n: int, default: date) → date` | Returns | Immutable |

`lead(val: decimal) → decimal` |
| Immutable |

`lead(val: decimal, n: int) → decimal` |
| Immutable |

`lead(val: decimal, n: int, default: decimal) → decimal` |
| Immutable |

`lead(val: float) → float` |
| Immutable |

`lead(val: float, n: int) → float` |
| Immutable |

`lead(val: float, n: int, default: float) → float` |
| Immutable |

`lead(val: inet) → inet` |
| Immutable |

`lead(val: inet, n: int) → inet` |
| Immutable |

`lead(val: inet, n: int, default: inet) → inet` |
| Immutable |

`lead(val: int) → int` |
| Immutable |

`lead(val: int, n: int) → int` |
| Immutable |

`lead(val: int, n: int, default: int) → int` |
| Immutable |

`lead(val: interval) → interval` |
| Immutable |

`lead(val: interval, n: int) → interval` |
| Immutable |

`lead(val: interval, n: int, default: interval) → interval` |
| Immutable |

`lead(val: string) → string` |
| Immutable |

`lead(val: string, n: int) → string` |
| Immutable |

`lead(val: string, n: int, default: string) → string` |
| Immutable |

`lead(val: time) → time` |
| Immutable |

`lead(val: time, n: int) → time` |
| Immutable |

`lead(val: time, n: int, default: time) → time` |
| Immutable |

`lead(val: timestamp) → timestamp` |
| Immutable |

`lead(val: timestamp, n: int) → timestamp` |
| Immutable |

`lead(val: timestamp, n: int, default: timestamp) → timestamp` |
| Immutable |

`lead(val: timestamptz) → timestamptz` |
| Immutable |

`lead(val: timestamptz, n: int) → timestamptz` |
| Immutable |

`lead(val: timestamptz, n: int, default: timestamptz) → timestamptz` |
| Immutable |

`lead(val: uuid) → uuid` |
| Immutable |

`lead(val: uuid, n: int) → uuid` |
| Immutable |

`lead(val: uuid, n: int, default: uuid) → uuid` |
| Immutable |

`lead(val: box2d) → box2d` |
| Immutable |

`lead(val: box2d, n: int) → box2d` |
| Immutable |

`lead(val: box2d, n: int, default: box2d) → box2d` |
| Immutable |

`lead(val: geography) → geography` |
| Immutable |

`lead(val: geography, n: int) → geography` |
| Immutable |

`lead(val: geography, n: int, default: geography) → geography` |
| Immutable |

`lead(val: geometry) → geometry` |
| Immutable |

`lead(val: geometry, n: int) → geometry` |
| Immutable |

`lead(val: geometry, n: int, default: geometry) → geometry` |
| Immutable |

`lead(val: jsonb) → jsonb` |
| Immutable |

`lead(val: jsonb, n: int) → jsonb` |
| Immutable |

`lead(val: jsonb, n: int, default: jsonb) → jsonb` |
| Immutable |

`lead(val: oid) → oid` |
| Immutable |

`lead(val: oid, n: int) → oid` |
| Immutable |

`lead(val: oid, n: int, default: oid) → oid` |
| Immutable |

`lead(val: timetz) → timetz` |
| Immutable |

`lead(val: timetz, n: int) → timetz` |
| Immutable |

`lead(val: timetz, n: int, default: timetz) → timetz` |
| Immutable |

`lead(val: varbit) → varbit` |
| Immutable |

`lead(val: varbit, n: int) → varbit` |
| Immutable |

`lead(val: varbit, n: int, default: varbit) → varbit` |
| Immutable |

`nth_value(val: bool, n: int) → bool` | Returns | Immutable |

`nth_value(val: bytes, n: int) → bytes` | Returns | Immutable |

`nth_value(val: date, n: int) → date` | Returns | Immutable |

`nth_value(val: decimal, n: int) → decimal` |
| Immutable |

`nth_value(val: float, n: int) → float` |
| Immutable |

`nth_value(val: inet, n: int) → inet` |
| Immutable |

`nth_value(val: int, n: int) → int` |
| Immutable |

`nth_value(val: interval, n: int) → interval` |
| Immutable |

`nth_value(val: string, n: int) → string` |
| Immutable |

`nth_value(val: time, n: int) → time` |
| Immutable |

`nth_value(val: timestamp, n: int) → timestamp` |
| Immutable |

`nth_value(val: timestamptz, n: int) → timestamptz` |
| Immutable |

`nth_value(val: uuid, n: int) → uuid` |
| Immutable |

`nth_value(val: box2d, n: int) → box2d` |
| Immutable |

`nth_value(val: geography, n: int) → geography` |
| Immutable |

`nth_value(val: geometry, n: int) → geometry` |
| Immutable |

`nth_value(val: jsonb, n: int) → jsonb` |
| Immutable |

`nth_value(val: oid, n: int) → oid` |
| Immutable |

`nth_value(val: timetz, n: int) → timetz` |
| Immutable |

`nth_value(val: varbit, n: int) → varbit` |
| Immutable |

`ntile(n: int) → int` | Calculates an integer ranging from 1 to | Immutable |

`percent_rank() → float` | Calculates the relative rank of the current row: (rank - 1) / (total rows - 1). | Immutable |

`rank() → int` | Calculates the rank of the current row with gaps; same as row_number of its first peer. | Immutable |

`row_number() → int` | Calculates the number of the current row within its partition, counting from 1. | Immutable |

## 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 | |

`&&` |
Overlaps | 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 | |

`->` |
Access a JSONB field, returning a JSONB value. | binary | |

`->>` |
Access a JSONB field, returning a string. | binary | |

`@>` |
Tests whether the left JSONB field contains the right JSONB field. | binary | |

`>@` |
Tests whether the left JSONB field is contained by the right JSONB field. | binary | |

`#>` |
Access a JSONB field at the specified path, returning a JSONB value. | binary | |

`#>>` |
Access a JSONB field at the specified path, returning a string. | binary | |

`?` |
Does the key or element string exist within the JSONB value? | binary | |

`?&` |
Do all the key or element strings exist within the JSONB value? | binary | |

`?|` |
Do any of the key or element strings exist within the JSONB value? | 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 |

varbit `#` varbit | varbit |

`#>` | 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 |

varbit `&` varbit | varbit |

`&&` | Return |

anyelement `&&` anyelement | bool |

box2d `&&` box2d | bool |

box2d `&&` geometry | bool |

geometry `&&` box2d | bool |

geometry `&&` geometry | bool |

inet `&&` inet | bool |

`+` | Return |

`+` decimal | decimal |

`+` float | float |

`+` int | int |

`+` interval | interval |

date `+` int | date |

date `+` interval | timestamp |

date `+` time | timestamp |

date `+` timetz | timestamptz |

decimal `+` decimal | decimal |

decimal `+` int | decimal |

float `+` float | float |

inet `+` int | inet |

int `+` date | date |

int `+` decimal | decimal |

int `+` inet | inet |

int `+` int | int |

interval `+` date | timestamp |

interval `+` interval | interval |

interval `+` time | time |

interval `+` timestamp | timestamp |

interval `+` timestamptz | timestamptz |

interval `+` timetz | timetz |

time `+` date | timestamp |

time `+` interval | time |

timestamp `+` interval | timestamp |

timestamptz `+` interval | timestamptz |

timetz `+` date | timestamptz |

timetz `+` interval | timetz |

`-` | Return |

`-` decimal | decimal |

`-` float | float |

`-` int | int |

`-` interval | interval |

date `-` date | int |

date `-` int | date |

date `-` interval | timestamp |

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 |

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 |

timetz `-` interval | timetz |

`->` | 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 |

inet `<<` inet | bool |

int `<<` int | int |

varbit `<<` int | varbit |

`<@` | Return |

anyelement `<@` anyelement | bool |

jsonb `<@` jsonb | bool |

`>>` | Return |

inet `>>` inet | bool |

int `>>` int | int |

varbit `>>` int | varbit |

`?` | Return |

jsonb `?` string | bool |

`?&` | Return |

jsonb `?&` string[] | bool |

`?|` | Return |

jsonb `?|` string[] | bool |

`@>` | Return |

anyelement `@>` anyelement | bool |

jsonb `@>` jsonb | bool |

`ILIKE` | Return |

string `ILIKE` string | bool |

`IN` | Return |

anyenum `IN` tuple | bool |

bool `IN` tuple | bool |

box2d `IN` tuple | bool |

bytes `IN` tuple | bool |

collatedstring `IN` tuple | bool |

date `IN` tuple | bool |

decimal `IN` tuple | bool |

float `IN` tuple | bool |

geography `IN` tuple | bool |

geometry `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 |

timetz `IN` tuple | bool |

tuple `IN` tuple | bool |

uuid `IN` tuple | bool |

varbit `IN` tuple | bool |

`IS NOT DISTINCT FROM` | Return |

anyelement `IS NOT DISTINCT FROM` unknown | bool |

anyenum `IS NOT DISTINCT FROM` anyenum | bool |

bool `IS NOT DISTINCT FROM` bool | bool |

bool[] `IS NOT DISTINCT FROM` bool[] | bool |

box2d `IS NOT DISTINCT FROM` box2d | 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 |

geography `IS NOT DISTINCT FROM` geography | bool |

geometry `IS NOT DISTINCT FROM` geometry | 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` oid | 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` int | 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` timetz | 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 |

timetz `IS NOT DISTINCT FROM` time | bool |

timetz `IS NOT DISTINCT FROM` timetz | bool |

tuple `IS NOT DISTINCT FROM` tuple | bool |

unknown `IS NOT DISTINCT FROM` unknown | bool |

unknown `IS NOT DISTINCT FROM` void | bool |

uuid `IS NOT DISTINCT FROM` uuid | bool |

uuid[] `IS NOT DISTINCT FROM` uuid[] | bool |

varbit `IS NOT DISTINCT FROM` varbit | bool |

void `IS NOT DISTINCT FROM` unknown | 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 |

varbit `|` varbit | varbit |

`|/` | Return |

`|/` decimal | decimal |

`|/` float | float |

`||/` | Return |

`||/` decimal | decimal |

`||/` float | float |

`~` | Return |

`~` inet | inet |

`~` int | int |

`~` varbit | varbit |

box2d `~` box2d | bool |

box2d `~` geometry | bool |

geometry `~` box2d | bool |

geometry `~` geometry | bool |

string `~` string | bool |

`~*` | Return |

string `~*` string | bool |