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 |
User-defined functions
In addition to the built-in functions described in the following sections, CockroachDB supports user-defined functions. For details, see User-Defined Functions.
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: pg_lsn[], elem: pg_lsn) → pg_lsn[] | Appends | Immutable |
array_append(array: refcursor[], elem: refcursor) → refcursor[] | 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: pg_lsn[], right: pg_lsn[]) → pg_lsn[] | Appends two arrays. | Immutable |
array_cat(left: refcursor[], right: refcursor[]) → refcursor[] | 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: bool[], elem: bool, start: int) → 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: bytes[], elem: bytes, start: int) → 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: date[], elem: date, start: int) → 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: decimal[], elem: decimal, start: int) → 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: float[], elem: float, start: int) → 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: inet[], elem: inet, start: int) → 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: int[], elem: int, start: 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: interval[], elem: interval, start: int) → 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: string[], elem: string, start: int) → 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: time[], elem: time, start: int) → 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: timestamp[], elem: timestamp, start: int) → 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: timestamptz[], elem: timestamptz, start: int) → 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: uuid[], elem: uuid, start: int) → 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: anyenum[], elem: anyenum, start: int) → 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: box2d[], elem: box2d, start: int) → 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: geography[], elem: geography, start: int) → 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: geometry[], elem: geometry, start: int) → 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: jsonb[], elem: jsonb, start: int) → 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: oid[], elem: oid, start: int) → int | Return the index of the first occurrence of | Immutable |
array_position(array: pg_lsn[], elem: pg_lsn) → int | Return the index of the first occurrence of | Immutable |
array_position(array: pg_lsn[], elem: pg_lsn, start: int) → int | Return the index of the first occurrence of | Immutable |
array_position(array: refcursor[], elem: refcursor) → int | Return the index of the first occurrence of | Immutable |
array_position(array: refcursor[], elem: refcursor, start: int) → 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: timetz[], elem: timetz, start: int) → 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: tuple[], elem: tuple, start: int) → 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_position(array: varbit[], elem: varbit, start: int) → int | Return the index of the first occurrence of | Immutable |
array_positions(array: bool[], elem: bool) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: bytes[], elem: bytes) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: date[], elem: date) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: decimal[], elem: decimal) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: float[], elem: float) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: inet[], elem: inet) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: int[], elem: int) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: interval[], elem: interval) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: string[], elem: string) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: time[], elem: time) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: timestamp[], elem: timestamp) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: timestamptz[], elem: timestamptz) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: uuid[], elem: uuid) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: anyenum[], elem: anyenum) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: box2d[], elem: box2d) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: geography[], elem: geography) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: geometry[], elem: geometry) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: jsonb[], elem: jsonb) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: oid[], elem: oid) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: pg_lsn[], elem: pg_lsn) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: refcursor[], elem: refcursor) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: timetz[], elem: timetz) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: tuple[], elem: tuple) → int[] | Returns an array of indexes of all occurrences of | Immutable |
array_positions(array: varbit[], elem: varbit) → int[] | Returns an 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: pg_lsn, array: pg_lsn[]) → pg_lsn[] | Prepends | Immutable |
array_prepend(elem: refcursor, array: refcursor[]) → refcursor[] | 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: pg_lsn[], elem: pg_lsn) → pg_lsn[] | Remove from | Immutable |
array_remove(array: refcursor[], elem: refcursor) → refcursor[] | 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: pg_lsn[], toreplace: pg_lsn, replacewith: pg_lsn) → pg_lsn[] | Replace all occurrences of | Immutable |
array_replace(array: refcursor[], toreplace: refcursor, replacewith: refcursor) → refcursor[] | 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 |
jsonb_array_to_string_array(input: jsonb) → string[] | Convert a JSONB array into a string array. | 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 |
---|---|---|
crypt(password: string, salt: string) → string | Generates a hash based on a password and salt. The hash algorithm and number of rounds if applicable are encoded in the salt. | Immutable |
decrypt(data: bytes, key: bytes, type: string) → bytes | Decrypt The cipher type must have the format
This function requires an enterprise license on a CCL distribution. | Immutable |
decrypt_iv(data: bytes, key: bytes, iv: bytes, type: string) → bytes | Decrypt The cipher type must have the format
This function requires an enterprise license on a CCL distribution. | Immutable |
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 | Immutable |
encrypt(data: bytes, key: bytes, type: string) → bytes | Encrypt The cipher type must have the format
This function requires an enterprise license on a CCL distribution. | Immutable |
encrypt_iv(data: bytes, key: bytes, iv: bytes, type: string) → bytes | Encrypt The cipher type must have the format
This function requires an enterprise license on a CCL distribution. | Immutable |
gen_random_bytes(count: int) → bytes | Returns | Volatile |
gen_salt(type: string) → string | Generates a salt for input into the | Volatile |
gen_salt(type: string, iter_count: int) → string | Generates a salt for input into the | Volatile |
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 | Immutable |
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. 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. 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. 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(precision: int) → 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(precision: int) → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 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 | Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. | Stable |
date_trunc(element: string, input: timestamptz, timezone: string) → timestamptz | Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. | 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 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: 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. 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 |
localtimestamp() → 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. This function is the preferred overload and will be evaluated by default. | Stable |
localtimestamp() → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. | Stable |
localtimestamp(precision: int) → 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 |
localtimestamp(precision: int) → 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. This function is the preferred overload and will be evaluated by default. | Stable |
localtimestamp(precision: int) → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. | Stable |
make_date(year: int, month: int, day: int) → date | Create date (formatted according to ISO 8601) from year, month, and day fields (negative years signify BC). | Immutable |
make_timestamp(year: int, month: int, day: int, hour: int, min: int, sec: float) → timestamp | Create timestamp (formatted according to ISO 8601) from year, month, day, hour, minute, and seconds fields (negative years signify BC). | Immutable |
make_timestamptz(year: int, month: int, day: int, hour: int, min: int, sec: float) → timestamptz | Create timestamp (formatted according to ISO 8601) with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If timezone is not specified, the current time zone is used. | Stable |
make_timestamptz(year: int, month: int, day: int, hour: int, min: int, sec: float, timezone: string) → timestamptz | Create timestamp (formatted according to ISO 8601) with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If timezone is not specified, the current time zone is used. | Stable |
now() → 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 |
now() → timestamp | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. | Stable |
now() → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. | Stable |
overlaps(s1: date, e1: date, s1: date, e2: date) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: date, e1: interval, s1: date, e2: interval) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: time, e1: interval, s1: time, e2: interval) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: time, e1: time, s1: time, e2: time) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: timestamp, e1: interval, s1: timestamp, e2: interval) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: timestamp, e1: timestamp, s1: timestamp, e2: timestamp) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: timestamptz, e1: interval, s1: timestamptz, e2: interval) → bool | Returns if two time periods (defined by their endpoints) overlap. | Stable |
overlaps(s1: timestamptz, e1: timestamptz, s1: timestamptz, e2: timestamptz) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: timetz, e1: interval, s1: timetz, e2: interval) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
overlaps(s1: timetz, e1: timetz, s1: timetz, e2: timetz) → bool | Returns if two time periods (defined by their endpoints) overlap. | Immutable |
statement_timestamp() → timestamp | Returns the start time of the current statement. | Stable |
statement_timestamp() → timestamptz | Returns the start time of the current statement. | Stable |
strftime(input: date, extract_format: string) → string | From | Immutable |
strftime(input: timestamp, extract_format: string) → string | From | Immutable |
strftime(input: timestamptz, extract_format: string) → string | From | Immutable |
strptime(input: string, format: string) → timestamptz | Returns | Immutable |
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(date: date, format: string) → string | Convert a timestamp with time zone to a string using the given format. | Stable |
to_char(interval: interval) → string | Convert an interval to a string assuming the Postgres IntervalStyle. | Immutable |
to_char(interval: interval, format: string) → string | Convert an interval to a string using the given format. | Stable |
to_char(timestamp: timestamp) → string | Convert an timestamp to a string assuming the ISO, MDY DateStyle. | Immutable |
to_char(timestamp: timestamp, format: string) → string | Convert an timestamp to a string using the given format. | Stable |
to_char(timestamptz: timestamptz, format: string) → string | Convert a timestamp with time zone to a string using the given format. | Stable |
to_timestamp(timestamp: float) → timestamptz | Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone. | Immutable |
transaction_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 |
transaction_timestamp() → timestamp | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. | Stable |
transaction_timestamp() → timestamptz | Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 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 e ^ | Immutable |
exp(val: float) → float | Calculates e ^ | 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 |
setseed(seed: float) → void | Sets the seed for subsequent random() calls in this session (value between -1.0 and 1.0, inclusive). There are no guarantees as to how this affects the seed of random() calls that appear in the same query as setseed(). | Volatile |
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: decimal, scale: int) → decimal | Truncate | Immutable |
trunc(val: float) → float | Truncates the decimal values of | Immutable |
Full Text Search functions
Function → Returns | Description | Volatility |
---|---|---|
phraseto_tsquery(config: string, text: string) → tsquery | Converts text to a tsquery, normalizing words according to the specified configuration. The <-> operator is inserted between each token in the input. | Immutable |
phraseto_tsquery(text: string) → tsquery | Converts text to a tsquery, normalizing words according to the default configuration. The <-> operator is inserted between each token in the input. | Stable |
plainto_tsquery(config: string, text: string) → tsquery | Converts text to a tsquery, normalizing words according to the specified configuration. The & operator is inserted between each token in the input. | Immutable |
plainto_tsquery(text: string) → tsquery | Converts text to a tsquery, normalizing words according to the default configuration. The & operator is inserted between each token in the input. | Stable |
to_tsquery(config: string, text: string) → tsquery | Converts the input text into a tsquery by normalizing each word in the input according to the specified configuration. The input must already be formatted like a tsquery, in other words, subsequent tokens must be connected by a tsquery operator (&, |, <->, !). | Immutable |
to_tsquery(text: string) → tsquery | Converts the input text into a tsquery by normalizing each word in the input according to the default configuration. The input must already be formatted like a tsquery, in other words, subsequent tokens must be connected by a tsquery operator (&, |, <->, !). | Stable |
to_tsvector(config: string, text: string) → tsvector | Converts text to a tsvector, normalizing words according to the specified configuration. Position information is included in the result. | Immutable |
to_tsvector(text: string) → tsvector | Converts text to a tsvector, normalizing words according to the default configuration. Position information is included in the result. | Stable |
ts_parse(parser_name: string, document: string) → tuple{int AS tokid, string AS token} | ts_parse parses the given document and returns a series of records, one for each token produced by parsing. Each record includes a tokid showing the assigned token type and a token which is the text of the token. | Stable |
ts_rank(vector: tsvector, query: tsquery) → float4 | Ranks vectors based on the frequency of their matching lexemes. | Immutable |
ts_rank(vector: tsvector, query: tsquery, normalization: int) → float4 | Ranks vectors based on the frequency of their matching lexemes. | Immutable |
ts_rank(weights: float[], vector: tsvector, query: tsquery) → float4 | Ranks vectors based on the frequency of their matching lexemes. | Immutable |
ts_rank(weights: float[], vector: tsvector, query: tsquery, normalization: int) → float4 | Ranks vectors based on the frequency of their matching lexemes. | Immutable |
Fuzzy String Matching functions
Function → Returns | Description | Volatility |
---|---|---|
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 |
metaphone(source: string, max_output_length: int) → string | Convert a string to its Metaphone code. Maximum input length is 255 characters | Immutable |
soundex(source: string) → string | Convert a string to its Soundex code. | 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 version 4 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 (bit-reversed) and the ID of the node executing the statement, which guarantees this combination is globally unique. The way it is generated is statistically likely to not have any ordering relative to previously generated values. | Volatile |
uuid_generate_v1() → uuid | Generates a version 1 UUID, and returns it as a value of UUID type. To avoid exposing the server’s real MAC address, this uses a random MAC address and a timestamp. Essentially, this is an alias for uuid_generate_v1mc. | Volatile |
uuid_generate_v1mc() → uuid | Generates a version 1 UUID, and returns it as a value of UUID type. This uses a random MAC address and a timestamp. | Volatile |
uuid_generate_v3(namespace: uuid, name: string) → uuid | Generates a version 3 UUID in the given namespace using the specified input name, with md5 as the hashing method. The namespace should be one of the special constants produced by the uuid_ns_*() functions. | Immutable |
uuid_generate_v4() → uuid | Generates a random version 4 UUID, and returns it as a value of UUID type. | Volatile |
uuid_generate_v5(namespace: uuid, name: string) → uuid | Generates a version 5 UUID in the given namespace using the specified input name. This is similar to a version 3 UUID, except it uses SHA-1 for hashing. | Immutable |
uuid_nil() → uuid | Returns a nil UUID constant. | Immutable |
uuid_ns_dns() → uuid | Returns a constant designating the DNS namespace for UUIDs. | Immutable |
uuid_ns_oid() → uuid | Returns a constant designating the ISO object identifier (OID) namespace for UUIDs. These are unrelated to the OID type used internally in the database. | Immutable |
uuid_ns_url() → uuid | Returns a constant designating the URL namespace for UUIDs. | Immutable |
uuid_ns_x500() → uuid | Returns a constant designating the X.500 distinguished name (DN) namespace for UUIDs. | Immutable |
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 |
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 |
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 |
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_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_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_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_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 |
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_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_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_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_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_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 |
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 |
---|---|---|
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 |
PGVector functions
Function → Returns | Description | Volatility |
---|---|---|
cosine_distance(v1: vector, v2: vector) → float | Returns the cosine distance between the two vectors. | Immutable |
inner_product(v1: vector, v2: vector) → float | Returns the inner product between the two vectors. | Immutable |
l1_distance(v1: vector, v2: vector) → float | Returns the Manhattan distance between the two vectors. | Immutable |
l2_distance(v1: vector, v2: vector) → float | Returns the Euclidean distance between the two vectors. | Immutable |
vector_dims(vector: vector) → int | Returns the number of the dimensions in the vector. | Immutable |
vector_norm(vector: vector) → float | Returns the Euclidean norm of the vector. | Immutable |
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 | |||||||||||||||||||||||||||||||
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_object_keys(input: jsonb) → string | Returns sorted set of keys in the outermost JSON object. | Immutable | |||||||||||||||||||||||||||||||
json_to_record(input: jsonb) → tuple | Builds an arbitrary record from a JSON object. | Stable | |||||||||||||||||||||||||||||||
json_to_recordset(input: jsonb) → tuple | Builds an arbitrary set of records from a JSON array of objects. | Stable | |||||||||||||||||||||||||||||||
jsonb_object_keys(input: jsonb) → string | Returns sorted set of keys in the outermost JSON object. | Immutable | |||||||||||||||||||||||||||||||
jsonb_to_record(input: jsonb) → tuple | Builds an arbitrary record from a JSON object. | Stable | |||||||||||||||||||||||||||||||
jsonb_to_recordset(input: jsonb) → tuple | Builds an arbitrary set of records from a JSON array of objects. | 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 | |||||||||||||||||||||||||||||||
pg_options_to_table(options: string[]) → tuple{string AS option_name, string AS option_value} | Converts the options array format to a table. | Stable | |||||||||||||||||||||||||||||||
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 | |||||||||||||||||||||||||||||||
workload_index_recs() → string | Returns set of index recommendations | Immutable | |||||||||||||||||||||||||||||||
workload_index_recs(timestamptz: timestamptz) → string | Returns set of index recommendations | 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. When 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. When 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. When 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. When 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 | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_lib_version() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_liblwgeom_version() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_libxml_version() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_proj_version() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_scripts_build_date() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_scripts_installed() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_scripts_released() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_version() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | Immutable |
postgis_wagyu_version() → string | Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. | 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:
| 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:
| 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 | 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:
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_asmvtgeom(geometry: geometry, bbox: box2d) → geometry | Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds. Uses 256 as the buffer size in tile coordinate space for geometry clipping. Uses 4096 as the tile extent size in tile coordinate space. The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry will be clipped can be transformed. | Immutable |
st_asmvtgeom(geometry: geometry, bbox: box2d, extent: int) → geometry | Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds. Uses 256 as the buffer size in tile coordinate space for geometry clipping. The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry will be clipped can be transformed. | Immutable |
st_asmvtgeom(geometry: geometry, bbox: box2d, extent: int, buffer: int) → geometry | Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds. The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry will be clipped can be transformed. | Immutable |
st_asmvtgeom(geometry: geometry, bbox: box2d, extent: int, buffer: int, clip: bool) → geometry | Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds if required. The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry can be transformed, and clipped if required. | 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 | 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_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_bdpolyfromtext(str: string, srid: int) → geometry | Returns a Polygon from multilinestring WKT with a SRID. If the input is not a multilinestring an error will be thrown. | 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:
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 | 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. | Immutable |
st_buffer(geometry: geometry, distance: float) → geometry | 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. | Immutable |
st_buffer(geometry: geometry, distance: float, buffer_style_params: string) → geometry | 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:
This function utilizes the GEOS module. | Immutable |
st_buffer(geometry: geometry, distance: float, quad_segs: int) → geometry | 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. | Immutable |
st_buffer(geometry: geometry, distance: int) → geometry | 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. | Immutable |
st_buffer(geometry_str: string, distance: decimal) → geometry | 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 variant will cast all geometry_str arguments into Geometry types. | Immutable |
st_buffer(geometry_str: string, distance: float) → geometry | 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 variant will cast all geometry_str arguments into Geometry types. | Immutable |
st_buffer(geometry_str: string, distance: float, buffer_style_params: string) → geometry | 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:
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 | 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 variant will cast all geometry_str arguments into Geometry types. | Immutable |
st_buffer(geometry_str: string, distance: int) → geometry | 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 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. When 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. When 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. When 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. When 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. When 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. When 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. When 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 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. 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_makeenvelope(xmin: float, ymin: float, xmax: float, ymax: float) → geometry | Creates a rectangular Polygon from the minimum and maximum values for X and Y with SRID 0. | Immutable |
st_makeenvelope(xmin: float, ymin: float, xmax: float, ymax: float, srid: int) → geometry | Creates a rectangular Polygon from the minimum and maximum values for X and Y with the given SRID. | 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 | 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 | 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:
| 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:
| 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_tileenvelope(tileZoom: int4, tileX: int4, tileY: int4) → geometry | Creates a rectangular Polygon giving the extent of a tile in the XYZ tile system. The tile is specifed by the zoom level Z and the XY index of the tile in the grid at that level. Can be used to define the tile bounds required by ST_AsMVTGeom to convert geometry into the MVT tile coordinate space. | Immutable |
st_tileenvelope(tileZoom: int4, tileX: int4, tileY: int4, bounds: geometry) → geometry | Creates a rectangular Polygon giving the extent of a tile in the XYZ tile system. The tile is specifed by the zoom level Z and the XY index of the tile in the grid at that level. Can be used to define the tile bounds required by ST_AsMVTGeom to convert geometry into the MVT tile coordinate space. | Immutable |
st_tileenvelope(tileZoom: int4, tileX: int4, tileY: int4, bounds: geometry, margin: float) → geometry | Creates a rectangular Polygon giving the extent of a tile in the XYZ tile system. The tile is specifed by the zoom level Z and the XY index of the tile in the grid at that level. Can be used to define the tile bounds required by ST_AsMVTGeom to convert geometry into the MVT tile coordinate space. | 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_xmax(box2d: box2d) → float | Returns the maximum X ordinate of a box2d. | Immutable |
st_xmax(geometry: geometry) → float | Returns the maximum X ordinate of a geometry. | Immutable |
st_xmin(box2d: box2d) → float | Returns the minimum X ordinate of a box2d. | Immutable |
st_xmin(geometry: geometry) → float | Returns the minimum X ordinate of a geometry. | Immutable |
st_y(geometry: geometry) → float | Returns the Y coordinate of a geometry if it is a Point. | Immutable |
st_ymax(box2d: box2d) → float | Returns the maximum Y ordinate of a box2d. | Immutable |
st_ymax(geometry: geometry) → float | Returns the maximum Y ordinate of a geometry. | Immutable |
st_ymin(box2d: box2d) → float | Returns the minimum Y ordinate of a box2d. | Immutable |
st_ymin(geometry: geometry) → float | Returns the minimum Y ordinate of a geometry. | 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 |
String and byte functions
Function → Returns | Description | Volatility | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ascii(val: string) → int | Returns the character code of the first character in | Immutable | |||||||||||||||||||||||||||||||
bit_count(val: bytes) → int | Calculates the number of bits set used to represent | Immutable | |||||||||||||||||||||||||||||||
bit_count(val: varbit) → int | Calculates the number of bits set used to represent | 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 | |||||||||||||||||||||||||||||||
bitmask_and(a: string, b: string) → varbit | Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_and(a: string, b: varbit) → varbit | Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_and(a: varbit, b: string) → varbit | Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_and(a: varbit, b: varbit) → varbit | Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_or(a: string, b: string) → varbit | Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_or(a: string, b: varbit) → varbit | Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_or(a: varbit, b: string) → varbit | Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_or(a: varbit, b: varbit) → varbit | Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_xor(a: string, b: string) → varbit | Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_xor(a: string, b: varbit) → varbit | Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_xor(a: varbit, b: string) → varbit | Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | Immutable | |||||||||||||||||||||||||||||||
bitmask_xor(a: varbit, b: varbit) → varbit | Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths. | 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 | |||||||||||||||||||||||||||||||
decode(text: string, format: string) → bytes | Decodes | Immutable | |||||||||||||||||||||||||||||||
decompress(data: bytes, codec: string) → bytes | Decompress | Immutable | |||||||||||||||||||||||||||||||
difference(source: string, target: string) → int | Convert two strings to their Soundex codes and report the number of matching code positions. | Immutable | |||||||||||||||||||||||||||||||
encode(data: bytes, format: string) → string | Encodes | Immutable | |||||||||||||||||||||||||||||||
format(string, anyelement...) → string | Interprets the first argument as a format string similar to C sprintf and interpolates the remaining arguments. | Stable | |||||||||||||||||||||||||||||||
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_ident(qualified_identifier: string) → string[] | Splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. Extra characters after the last identifier are considered an error | Immutable | |||||||||||||||||||||||||||||||
parse_ident(qualified_identifier: string, strict: bool) → string[] | Splits | 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 | |||||||||||||||||||||||||||||||
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. Returns an error if run in a transaction with an isolation level weaker than SERIALIZABLE. | 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 |
to_regclass(text: string) → regtype | Translates a textual relation name to its OID | Stable |
to_regnamespace(text: string) → regtype | Translates a textual schema name to its OID | Stable |
to_regproc(text: string) → regtype | Translates a textual function or procedure name to its OID | Stable |
to_regprocedure(text: string) → regtype | Translates a textual function or procedure name(with argument types) to its OID | Stable |
to_regrole(text: string) → regtype | Translates a textual role name to its OID | Stable |
to_regtype(text: string) → regtype | Translates a textual type name to its OID | Stable |
version() → string | Returns the node’s version of CockroachDB. | 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 |
Trigrams functions
Function → Returns | Description | Volatility |
---|---|---|
show_trgm(input: string) → string[] | Returns an array of all the trigrams in the given string. | Immutable |
similarity(left: string, right: string) → float | Returns a number that indicates how similar the two arguments are. The range of the result is zero (indicating that the two strings are completely dissimilar) to one (indicating that the two strings are identical). | Immutable |
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 |
---|---|---|
col_description(table_oid: oid, column_number: int) → string | Returns the comment for a table column, which is specified by the OID of its table and its column number. (obj_description cannot be used for table columns, since columns do not have OIDs of their own.) | Stable |
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 |
nameconcatoid(name: string, oid: oid) → name | Used in the information_schema to produce specific_name columns, which are supposed to be unique per schema. The result is the same as ($1::text || ‘_’ || $2::text)::name except that, if it would not fit in 63 characters, we make it do so by truncating the name input (not the oid). | Immutable |
obj_description(object_oid: oid) → string | Returns the comment for a database object specified by its OID alone. This is deprecated since there is no guarantee that OIDs are unique across different system catalogs; therefore, the wrong comment might be returned. | Stable |
obj_description(object_oid: oid, catalog_name: string) → string | Returns the comment for a database object specified by its OID and the name of the containing system catalog. For example, obj_description(123456, ‘pg_class’) would retrieve the comment for the table with OID 123456. | Stable |
oidvectortypes(vector: oidvector) → string | Generates a comma seperated string of type names from an oidvector. | Stable |
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_function_is_visible(oid: oid) → bool | Returns whether the function with the given OID belongs to one of the schemas on the search path. | Stable |
pg_get_function_arg_default(func_oid: oid, arg_num: int4) → string | Get textual representation of a function argument’s default value. The second argument of this function is the argument number among all arguments (i.e. proallargtypes, not proargtypes), starting with 1, because that’s how information_schema.sql uses it. Currently, this always returns NULL, since CockroachDB does not support default values. | Stable |
pg_get_function_arguments(func_oid: oid) → string | Returns the argument list (with defaults) necessary to identify a function, in the form it would need to appear in within CREATE FUNCTION. | Stable |
pg_get_function_identity_arguments(func_oid: oid) → string | Returns the argument list (without defaults) necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance. | Stable |
pg_get_function_result(func_oid: oid) → string | Returns the types of the result of the specified function. | Stable |
pg_get_functiondef(func_oid: oid) → string | For user-defined functions, returns the definition of the specified function. For builtin functions, returns the name of the function. | Stable |
pg_get_indexdef(index_oid: oid) → string | Gets the CREATE INDEX command for index | Stable |
pg_get_indexdef(index_oid: oid, column_no: int, pretty_bool: bool) → string | Gets the CREATE INDEX command for index, or definition of just one index column when given a non-zero column number | Stable |
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_get_viewdef(view_oid: oid) → string | Returns the CREATE statement for an existing view. | Stable |
pg_get_viewdef(view_oid: oid, pretty_bool: bool) → string | Returns the CREATE statement for an existing view. | 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_sequence_last_value(sequence_oid: oid) → int | Returns the last value generated by a sequence, or NULL if the sequence has not been used yet. | Volatile |
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 |
shobj_description(object_oid: oid, catalog_name: string) → string | Returns the comment for a shared database object specified by its OID and the name of the containing system catalog. This is just like obj_description except that it is used for retrieving comments on shared objects (e.g. databases). | Stable |
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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: oid[]) → oid[][] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: pg_lsn) → pg_lsn[] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: pg_lsn[]) → pg_lsn[][] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: refcursor) → refcursor[] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: refcursor[]) → refcursor[][] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: timetz) → timetz[] | 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: tuple[]) → tuple[][] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: varbit) → varbit[] | Aggregates the selected values into an array. | Immutable |
array_agg(arg1: varbit[]) → varbit[][] | Aggregates the selected values into an array. | Immutable |
array_cat_agg(arg1: bool[]) → bool[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: bytes[]) → bytes[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: date[]) → date[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: decimal[]) → decimal[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: float[]) → float[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: inet[]) → inet[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: int[]) → int[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: interval[]) → interval[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: string[]) → string[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: time[]) → time[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: timestamp[]) → timestamp[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: timestamptz[]) → timestamptz[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: uuid[]) → uuid[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: anyenum[]) → anyenum[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: box2d[]) → box2d[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: geography[]) → geography[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: geometry[]) → geometry[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: jsonb[]) → jsonb[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: oid[]) → oid[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: pg_lsn[]) → pg_lsn[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: refcursor[]) → refcursor[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: timetz[]) → timetz[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: tuple[]) → tuple[] | Unnests the selected arrays into elements that are then aggregated into a single array. | Immutable |
array_cat_agg(arg1: varbit[]) → varbit[] | Unnests the selected arrays into elements that are then aggregated into a single 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: pg_lsn) → pg_lsn | 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: pg_lsn) → pg_lsn | 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: pg_lsn) → pg_lsn | Returns | Immutable |
first_value(val: refcursor) → refcursor | 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 | Returns | Immutable |
lag(val: decimal, n: int) → decimal | Returns | Immutable |
lag(val: decimal, n: int, default: decimal) → decimal | Returns | Immutable |
lag(val: float) → float | Returns | Immutable |
lag(val: float, n: int) → float | Returns | Immutable |
lag(val: float, n: int, default: float) → float | Returns | Immutable |
lag(val: inet) → inet | Returns | Immutable |
lag(val: inet, n: int) → inet | Returns | Immutable |
lag(val: inet, n: int, default: inet) → inet | Returns | Immutable |
lag(val: int) → int | Returns | Immutable |
lag(val: int, n: int) → int | Returns | Immutable |
lag(val: int, n: int, default: int) → int | Returns | Immutable |
lag(val: interval) → interval | Returns | Immutable |
lag(val: interval, n: int) → interval | Returns | Immutable |
lag(val: interval, n: int, default: interval) → interval | Returns | Immutable |
lag(val: string) → string | Returns | Immutable |
lag(val: string, n: int) → string | Returns | Immutable |
lag(val: string, n: int, default: string) → string | Returns | Immutable |
lag(val: time) → time | Returns | Immutable |
lag(val: time, n: int) → time | Returns | Immutable |
lag(val: time, n: int, default: time) → time | Returns | Immutable |
lag(val: timestamp) → timestamp | Returns | Immutable |
lag(val: timestamp, n: int) → timestamp | Returns | Immutable |
lag(val: timestamp, n: int, default: timestamp) → timestamp | Returns | Immutable |
lag(val: timestamptz) → timestamptz | Returns | Immutable |
lag(val: timestamptz, n: int) → timestamptz | Returns | Immutable |
lag(val: timestamptz, n: int, default: timestamptz) → timestamptz | Returns | Immutable |
lag(val: uuid) → uuid | Returns | Immutable |
lag(val: uuid, n: int) → uuid | Returns | Immutable |
lag(val: uuid, n: int, default: uuid) → uuid | Returns | Immutable |
lag(val: box2d) → box2d | Returns | Immutable |
lag(val: box2d, n: int) → box2d | Returns | Immutable |
lag(val: box2d, n: int, default: box2d) → box2d | Returns | Immutable |
lag(val: geography) → geography | Returns | Immutable |
lag(val: geography, n: int) → geography | Returns | Immutable |
lag(val: geography, n: int, default: geography) → geography | Returns | Immutable |
lag(val: geometry) → geometry | Returns | Immutable |
lag(val: geometry, n: int) → geometry | Returns | Immutable |
lag(val: geometry, n: int, default: geometry) → geometry | Returns | Immutable |
lag(val: jsonb) → jsonb | Returns | Immutable |
lag(val: jsonb, n: int) → jsonb | Returns | Immutable |
lag(val: jsonb, n: int, default: jsonb) → jsonb | Returns | Immutable |
lag(val: oid) → oid | Returns | Immutable |
lag(val: oid, n: int) → oid | Returns | Immutable |
lag(val: oid, n: int, default: oid) → oid | Returns | Immutable |
lag(val: pg_lsn) → pg_lsn | Returns | Immutable |
lag(val: pg_lsn, n: int) → pg_lsn | Returns | Immutable |
lag(val: pg_lsn, n: int, default: pg_lsn) → pg_lsn | Returns | Immutable |
lag(val: refcursor) → refcursor | Returns | Immutable |
lag(val: refcursor, n: int) → refcursor | Returns | Immutable |
lag(val: refcursor, n: int, default: refcursor) → refcursor | Returns | Immutable |
lag(val: timetz) → timetz | Returns | Immutable |
lag(val: timetz, n: int) → timetz | Returns | Immutable |
lag(val: timetz, n: int, default: timetz) → timetz | Returns | Immutable |
lag(val: varbit) → varbit | Returns | Immutable |
lag(val: varbit, n: int) → varbit | Returns | Immutable |
lag(val: varbit, n: int, default: varbit) → varbit | Returns | 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: pg_lsn) → pg_lsn | Returns | Immutable |
last_value(val: refcursor) → refcursor | 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 | Returns | Immutable |
lead(val: decimal, n: int) → decimal | Returns | Immutable |
lead(val: decimal, n: int, default: decimal) → decimal | Returns | Immutable |
lead(val: float) → float | Returns | Immutable |
lead(val: float, n: int) → float | Returns | Immutable |
lead(val: float, n: int, default: float) → float | Returns | Immutable |
lead(val: inet) → inet | Returns | Immutable |
lead(val: inet, n: int) → inet | Returns | Immutable |
lead(val: inet, n: int, default: inet) → inet | Returns | Immutable |
lead(val: int) → int | Returns | Immutable |
lead(val: int, n: int) → int | Returns | Immutable |
lead(val: int, n: int, default: int) → int | Returns | Immutable |
lead(val: interval) → interval | Returns | Immutable |
lead(val: interval, n: int) → interval | Returns | Immutable |
lead(val: interval, n: int, default: interval) → interval | Returns | Immutable |
lead(val: string) → string | Returns | Immutable |
lead(val: string, n: int) → string | Returns | Immutable |
lead(val: string, n: int, default: string) → string | Returns | Immutable |
lead(val: time) → time | Returns | Immutable |
lead(val: time, n: int) → time | Returns | Immutable |
lead(val: time, n: int, default: time) → time | Returns | Immutable |
lead(val: timestamp) → timestamp | Returns | Immutable |
lead(val: timestamp, n: int) → timestamp | Returns | Immutable |
lead(val: timestamp, n: int, default: timestamp) → timestamp | Returns | Immutable |
lead(val: timestamptz) → timestamptz | Returns | Immutable |
lead(val: timestamptz, n: int) → timestamptz | Returns | Immutable |
lead(val: timestamptz, n: int, default: timestamptz) → timestamptz | Returns | Immutable |
lead(val: uuid) → uuid | Returns | Immutable |
lead(val: uuid, n: int) → uuid | Returns | Immutable |
lead(val: uuid, n: int, default: uuid) → uuid | Returns | Immutable |
lead(val: box2d) → box2d | Returns | Immutable |
lead(val: box2d, n: int) → box2d | Returns | Immutable |
lead(val: box2d, n: int, default: box2d) → box2d | Returns | Immutable |
lead(val: geography) → geography | Returns | Immutable |
lead(val: geography, n: int) → geography | Returns | Immutable |
lead(val: geography, n: int, default: geography) → geography | Returns | Immutable |
lead(val: geometry) → geometry | Returns | Immutable |
lead(val: geometry, n: int) → geometry | Returns | Immutable |
lead(val: geometry, n: int, default: geometry) → geometry | Returns | Immutable |
lead(val: jsonb) → jsonb | Returns | Immutable |
lead(val: jsonb, n: int) → jsonb | Returns | Immutable |
lead(val: jsonb, n: int, default: jsonb) → jsonb | Returns | Immutable |
lead(val: oid) → oid | Returns | Immutable |
lead(val: oid, n: int) → oid | Returns | Immutable |
lead(val: oid, n: int, default: oid) → oid | Returns | Immutable |
lead(val: pg_lsn) → pg_lsn | Returns | Immutable |
lead(val: pg_lsn, n: int) → pg_lsn | Returns | Immutable |
lead(val: pg_lsn, n: int, default: pg_lsn) → pg_lsn | Returns | Immutable |
lead(val: refcursor) → refcursor | Returns | Immutable |
lead(val: refcursor, n: int) → refcursor | Returns | Immutable |
lead(val: refcursor, n: int, default: refcursor) → refcursor | Returns | Immutable |
lead(val: timetz) → timetz | Returns | Immutable |
lead(val: timetz, n: int) → timetz | Returns | Immutable |
lead(val: timetz, n: int, default: timetz) → timetz | Returns | Immutable |
lead(val: varbit) → varbit | Returns | Immutable |
lead(val: varbit, n: int) → varbit | Returns | Immutable |
lead(val: varbit, n: int, default: varbit) → varbit | Returns | 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 | Returns | Immutable |
nth_value(val: float, n: int) → float | Returns | Immutable |
nth_value(val: inet, n: int) → inet | Returns | Immutable |
nth_value(val: int, n: int) → int | Returns | Immutable |
nth_value(val: interval, n: int) → interval | Returns | Immutable |
nth_value(val: string, n: int) → string | Returns | Immutable |
nth_value(val: time, n: int) → time | Returns | Immutable |
nth_value(val: timestamp, n: int) → timestamp | Returns | Immutable |
nth_value(val: timestamptz, n: int) → timestamptz | Returns | Immutable |
nth_value(val: uuid, n: int) → uuid | Returns | Immutable |
nth_value(val: box2d, n: int) → box2d | Returns | Immutable |
nth_value(val: geography, n: int) → geography | Returns | Immutable |
nth_value(val: geometry, n: int) → geometry | Returns | Immutable |
nth_value(val: jsonb, n: int) → jsonb | Returns | Immutable |
nth_value(val: oid, n: int) → oid | Returns | Immutable |
nth_value(val: pg_lsn, n: int) → pg_lsn | Returns | Immutable |
nth_value(val: refcursor, n: int) → refcursor | Returns | Immutable |
nth_value(val: timetz, n: int) → timetz | Returns | Immutable |
nth_value(val: varbit, n: int) → varbit | Returns | 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, or text similarity when accessing a STRING column. For more information, see Trigram Indexes. |
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 or array field contains the right JSONB or array field. | binary | |
>@ |
Tests whether the left JSONB or array field is contained by the right JSONB or array 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 |
string % string | bool |
& | 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 | decimal |
decimal * int | decimal |
decimal * interval | interval |
float * float | float |
float * interval | interval |
int * decimal | decimal |
int * int | int |
int * interval | interval |
interval * decimal | interval |
interval * float | interval |
interval * int | interval |
vector * vector | vector |
+ | 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 |
decimal + pg_lsn | pg_lsn |
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 |
pg_lsn + decimal | pg_lsn |
time + date | timestamp |
time + interval | time |
timestamp + interval | timestamp |
timestamptz + interval | timestamptz |
timetz + date | timestamptz |
timetz + interval | timetz |
vector + vector | vector |
- | 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 |
pg_lsn - decimal | pg_lsn |
pg_lsn - pg_lsn | decimal |
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 |
vector - vector | vector |
-> | 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 |
vector <#> vector | float |
<-> | Return |
vector <-> vector | float |
<< | Return |
inet << inet | bool |
int << int | int |
varbit << int | varbit |
<=> | Return |
vector <=> vector | float |
<@ | 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 |
@@ | Return |
tsquery @@ tsvector | bool |
tsvector @@ tsquery | bool |
ILIKE | Return |
string ILIKE string | bool |
IN | Return |
anyenum IN tuple | bool |
bool IN tuple | bool |
box2d IN tuple | bool |
bpchar 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 |
pg_lsn IN tuple | bool |
refcursor 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 |
vector 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 |
bpchar IS NOT DISTINCT FROM bpchar | 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 |
pg_lsn IS NOT DISTINCT FROM pg_lsn | bool |
refcursor IS NOT DISTINCT FROM refcursor | 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 |
tsquery IS NOT DISTINCT FROM tsquery | bool |
tsvector IS NOT DISTINCT FROM tsvector | 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 |
vector IS NOT DISTINCT FROM vector | 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 |