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

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

Special syntax forms

The following syntax forms are recognized for compatibility with 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()

Conditional and function-like operators

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

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

Built-in functions

Array functions

Function → ReturnsDescription
array_append(array: bool[], elem: bool) → bool[]

Appends elem to array, returning the result.

array_append(array: bytes[], elem: bytes) → bytes[]

Appends elem to array, returning the result.

array_append(array: date[], elem: date) → date[]

Appends elem to array, returning the result.

array_append(array: decimal[], elem: decimal) → decimal[]

Appends elem to array, returning the result.

array_append(array: float[], elem: float) → float[]

Appends elem to array, returning the result.

array_append(array: inet[], elem: inet) → inet[]

Appends elem to array, returning the result.

array_append(array: int[], elem: int) → int[]

Appends elem to array, returning the result.

array_append(array: interval[], elem: interval) → interval[]

Appends elem to array, returning the result.

array_append(array: string[], elem: string) → string[]

Appends elem to array, returning the result.

array_append(array: time[], elem: time) → time[]

Appends elem to array, returning the result.

array_append(array: timestamp[], elem: timestamp) → timestamp[]

Appends elem to array, returning the result.

array_append(array: timestamptz[], elem: timestamptz) → timestamptz[]

Appends elem to array, returning the result.

array_append(array: uuid[], elem: uuid) → uuid[]

Appends elem to array, returning the result.

array_append(array: box2d[], elem: box2d) → box2d[]

Appends elem to array, returning the result.

array_append(array: geography[], elem: geography) → geography[]

Appends elem to array, returning the result.

array_append(array: geometry[], elem: geometry) → geometry[]

Appends elem to array, returning the result.

array_append(array: oid[], elem: oid) → oid[]

Appends elem to array, returning the result.

array_append(array: timetz[], elem: timetz) → timetz[]

Appends elem to array, returning the result.

array_append(array: varbit[], elem: varbit) → varbit[]

Appends elem to array, returning the result.

array_cat(left: bool[], right: bool[]) → bool[]

Appends two arrays.

array_cat(left: bytes[], right: bytes[]) → bytes[]

Appends two arrays.

array_cat(left: date[], right: date[]) → date[]

Appends two arrays.

array_cat(left: decimal[], right: decimal[]) → decimal[]

Appends two arrays.

array_cat(left: float[], right: float[]) → float[]

Appends two arrays.

array_cat(left: inet[], right: inet[]) → inet[]

Appends two arrays.

array_cat(left: int[], right: int[]) → int[]

Appends two arrays.

array_cat(left: interval[], right: interval[]) → interval[]

Appends two arrays.

array_cat(left: string[], right: string[]) → string[]

Appends two arrays.

array_cat(left: time[], right: time[]) → time[]

Appends two arrays.

array_cat(left: timestamp[], right: timestamp[]) → timestamp[]

Appends two arrays.

array_cat(left: timestamptz[], right: timestamptz[]) → timestamptz[]

Appends two arrays.

array_cat(left: uuid[], right: uuid[]) → uuid[]

Appends two arrays.

array_cat(left: box2d[], right: box2d[]) → box2d[]

Appends two arrays.

array_cat(left: geography[], right: geography[]) → geography[]

Appends two arrays.

array_cat(left: geometry[], right: geometry[]) → geometry[]

Appends two arrays.

array_cat(left: oid[], right: oid[]) → oid[]

Appends two arrays.

array_cat(left: timetz[], right: timetz[]) → timetz[]

Appends two arrays.

array_cat(left: varbit[], right: varbit[]) → varbit[]

Appends two arrays.

array_length(input: anyelement[], array_dimension: int) → int

Calculates the length of input on the provided array_dimension. However, because CockroachDB doesn’t yet support multi-dimensional arrays, the only supported array_dimension is 1.

array_lower(input: anyelement[], array_dimension: int) → int

Calculates the minimum value of input on the provided array_dimension. However, because CockroachDB doesn’t yet support multi-dimensional arrays, the only supported array_dimension is 1.

array_position(array: bool[], elem: bool) → int

Return the index of the first occurrence of elem in array.

array_position(array: bytes[], elem: bytes) → int

Return the index of the first occurrence of elem in array.

array_position(array: date[], elem: date) → int

Return the index of the first occurrence of elem in array.

array_position(array: decimal[], elem: decimal) → int

Return the index of the first occurrence of elem in array.

array_position(array: float[], elem: float) → int

Return the index of the first occurrence of elem in array.

array_position(array: inet[], elem: inet) → int

Return the index of the first occurrence of elem in array.

array_position(array: int[], elem: int) → int

Return the index of the first occurrence of elem in array.

array_position(array: interval[], elem: interval) → int

Return the index of the first occurrence of elem in array.

array_position(array: string[], elem: string) → int

Return the index of the first occurrence of elem in array.

array_position(array: time[], elem: time) → int

Return the index of the first occurrence of elem in array.

array_position(array: timestamp[], elem: timestamp) → int

Return the index of the first occurrence of elem in array.

array_position(array: timestamptz[], elem: timestamptz) → int

Return the index of the first occurrence of elem in array.

array_position(array: uuid[], elem: uuid) → int

Return the index of the first occurrence of elem in array.

array_position(array: box2d[], elem: box2d) → int

Return the index of the first occurrence of elem in array.

array_position(array: geography[], elem: geography) → int

Return the index of the first occurrence of elem in array.

array_position(array: geometry[], elem: geometry) → int

Return the index of the first occurrence of elem in array.

array_position(array: oid[], elem: oid) → int

Return the index of the first occurrence of elem in array.

array_position(array: timetz[], elem: timetz) → int

Return the index of the first occurrence of elem in array.

array_position(array: varbit[], elem: varbit) → int

Return the index of the first occurrence of elem in array.

array_positions(array: bool[], elem: bool) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: bytes[], elem: bytes) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: date[], elem: date) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: decimal[], elem: decimal) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: float[], elem: float) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: inet[], elem: inet) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: int[], elem: int) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: interval[], elem: interval) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: string[], elem: string) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: time[], elem: time) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: timestamp[], elem: timestamp) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: timestamptz[], elem: timestamptz) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: uuid[], elem: uuid) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: box2d[], elem: box2d) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: geography[], elem: geography) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: geometry[], elem: geometry) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: oid[], elem: oid) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: timetz[], elem: timetz) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_positions(array: varbit[], elem: varbit) → int[]

Returns and array of indexes of all occurrences of elem in array.

array_prepend(elem: bool, array: bool[]) → bool[]

Prepends elem to array, returning the result.

array_prepend(elem: bytes, array: bytes[]) → bytes[]

Prepends elem to array, returning the result.

array_prepend(elem: date, array: date[]) → date[]

Prepends elem to array, returning the result.

array_prepend(elem: decimal, array: decimal[]) → decimal[]

Prepends elem to array, returning the result.

array_prepend(elem: float, array: float[]) → float[]

Prepends elem to array, returning the result.

array_prepend(elem: inet, array: inet[]) → inet[]

Prepends elem to array, returning the result.

array_prepend(elem: int, array: int[]) → int[]

Prepends elem to array, returning the result.

array_prepend(elem: interval, array: interval[]) → interval[]

Prepends elem to array, returning the result.

array_prepend(elem: string, array: string[]) → string[]

Prepends elem to array, returning the result.

array_prepend(elem: time, array: time[]) → time[]

Prepends elem to array, returning the result.

array_prepend(elem: timestamp, array: timestamp[]) → timestamp[]

Prepends elem to array, returning the result.

array_prepend(elem: timestamptz, array: timestamptz[]) → timestamptz[]

Prepends elem to array, returning the result.

array_prepend(elem: uuid, array: uuid[]) → uuid[]

Prepends elem to array, returning the result.

array_prepend(elem: box2d, array: box2d[]) → box2d[]

Prepends elem to array, returning the result.

array_prepend(elem: geography, array: geography[]) → geography[]

Prepends elem to array, returning the result.

array_prepend(elem: geometry, array: geometry[]) → geometry[]

Prepends elem to array, returning the result.

array_prepend(elem: oid, array: oid[]) → oid[]

Prepends elem to array, returning the result.

array_prepend(elem: timetz, array: timetz[]) → timetz[]

Prepends elem to array, returning the result.

array_prepend(elem: varbit, array: varbit[]) → varbit[]

Prepends elem to array, returning the result.

array_remove(array: bool[], elem: bool) → bool[]

Remove from array all elements equal to elem.

array_remove(array: bytes[], elem: bytes) → bytes[]

Remove from array all elements equal to elem.

array_remove(array: date[], elem: date) → date[]

Remove from array all elements equal to elem.

array_remove(array: decimal[], elem: decimal) → decimal[]

Remove from array all elements equal to elem.

array_remove(array: float[], elem: float) → float[]

Remove from array all elements equal to elem.

array_remove(array: inet[], elem: inet) → inet[]

Remove from array all elements equal to elem.

array_remove(array: int[], elem: int) → int[]

Remove from array all elements equal to elem.

array_remove(array: interval[], elem: interval) → interval[]

Remove from array all elements equal to elem.

array_remove(array: string[], elem: string) → string[]

Remove from array all elements equal to elem.

array_remove(array: time[], elem: time) → time[]

Remove from array all elements equal to elem.

array_remove(array: timestamp[], elem: timestamp) → timestamp[]

Remove from array all elements equal to elem.

array_remove(array: timestamptz[], elem: timestamptz) → timestamptz[]

Remove from array all elements equal to elem.

array_remove(array: uuid[], elem: uuid) → uuid[]

Remove from array all elements equal to elem.

array_remove(array: box2d[], elem: box2d) → box2d[]

Remove from array all elements equal to elem.

array_remove(array: geography[], elem: geography) → geography[]

Remove from array all elements equal to elem.

array_remove(array: geometry[], elem: geometry) → geometry[]

Remove from array all elements equal to elem.

array_remove(array: oid[], elem: oid) → oid[]

Remove from array all elements equal to elem.

array_remove(array: timetz[], elem: timetz) → timetz[]

Remove from array all elements equal to elem.

array_remove(array: varbit[], elem: varbit) → varbit[]

Remove from array all elements equal to elem.

array_replace(array: bool[], toreplace: bool, replacewith: bool) → bool[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: bytes[], toreplace: bytes, replacewith: bytes) → bytes[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: date[], toreplace: date, replacewith: date) → date[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) → decimal[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: float[], toreplace: float, replacewith: float) → float[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: inet[], toreplace: inet, replacewith: inet) → inet[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: int[], toreplace: int, replacewith: int) → int[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: interval[], toreplace: interval, replacewith: interval) → interval[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: string[], toreplace: string, replacewith: string) → string[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: time[], toreplace: time, replacewith: time) → time[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp) → timestamp[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) → timestamptz[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) → uuid[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: box2d[], toreplace: box2d, replacewith: box2d) → box2d[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: geography[], toreplace: geography, replacewith: geography) → geography[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: geometry[], toreplace: geometry, replacewith: geometry) → geometry[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: oid[], toreplace: oid, replacewith: oid) → oid[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: timetz[], toreplace: timetz, replacewith: timetz) → timetz[]

Replace all occurrences of toreplace in array with replacewith.

array_replace(array: varbit[], toreplace: varbit, replacewith: varbit) → varbit[]

Replace all occurrences of toreplace in array with replacewith.

array_to_string(input: anyelement[], delim: string) → string

Join an array into a string with a delimiter.

array_to_string(input: anyelement[], delimiter: string, null: string) → string

Join an array into a string with a delimiter, replacing NULLs with a null string.

array_upper(input: anyelement[], array_dimension: int) → int

Calculates the maximum value of input on the provided array_dimension. However, because CockroachDB doesn’t yet support multi-dimensional arrays, the only supported array_dimension is 1.

string_to_array(str: string, delimiter: string) → string[]

Split a string into components on a delimiter.

string_to_array(str: string, delimiter: string, null: string) → string[]

Split a string into components on a delimiter with a specified string to consider NULL.

BOOL functions

Function → ReturnsDescription
ilike_escape(unescaped: string, pattern: string, escape: string) → bool

Matches case insensetively unescaped with pattern using ‘escape’ as an escape token.

inet_contained_by_or_equals(val: inet, container: inet) → bool

Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored.

inet_contains_or_equals(container: inet, val: inet) → bool

Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored.

inet_same_family(val: inet, val: inet) → bool

Checks if two IP addresses are of the same IP family.

like_escape(unescaped: string, pattern: string, escape: string) → bool

Matches unescaped with pattern using ‘escape’ as an escape token.

not_ilike_escape(unescaped: string, pattern: string, escape: string) → bool

Checks whether unescaped not matches case insensetively with pattern using ‘escape’ as an escape token.

not_like_escape(unescaped: string, pattern: string, escape: string) → bool

Checks whether unescaped not matches with pattern using ‘escape’ as an escape token.

not_similar_to_escape(unescaped: string, pattern: string, escape: string) → bool

Checks whether unescaped not matches with pattern using ‘escape’ as an escape token.

similar_to_escape(unescaped: string, pattern: string, escape: string) → bool

Matches unescaped with pattern using ‘escape’ as an escape token.

Comparison functions

Function → ReturnsDescription
greatest(anyelement...) → anyelement

Returns the element with the greatest value.

least(anyelement...) → anyelement

Returns the element with the lowest value.

num_nonnulls(anyelement...) → int

Returns the number of nonnull arguments.

num_nulls(anyelement...) → int

Returns the number of null arguments.

Date and time functions

Function → ReturnsDescription
age(end: timestamptz, begin: timestamptz) → interval

Calculates the interval between begin and end.

age(val: timestamptz) → interval

Calculates the interval between val and the current time.

clock_timestamp() → timestamp

Returns the current system time on one of the cluster nodes.

clock_timestamp() → timestamptz

Returns the current system time on one of the cluster nodes.

current_date() → date

Returns the date of the current transaction.

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

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.

current_timestamp() → timestamp

Returns the time of the current transaction.

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

current_timestamp() → timestamptz

Returns the time of the current transaction.

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

This function is the preferred overload and will be evaluated by default.

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.

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.

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.

date_trunc(element: string, input: date) → timestamptz

Truncates input to precision element. Sets all fields that are less significant than element to zero (or one, for day and month)

Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond.

date_trunc(element: string, input: time) → interval

Truncates input to precision element. Sets all fields that are less significant than element to zero.

Compatible elements: hour, minute, second, millisecond, microsecond.

date_trunc(element: string, input: timestamp) → timestamp

Truncates input to precision element. Sets all fields that are less significant than element to zero (or one, for day and month)

Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond.

date_trunc(element: string, input: timestamptz) → timestamptz

Truncates input to precision element. Sets all fields that are less significant than element to zero (or one, for day and month)

Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond.

experimental_follower_read_timestamp() → timestamptz

Same as follower_read_timestamp. This name is deprecated.

experimental_strftime(input: date, extract_format: string) → string

From input, extracts and formats the time as identified in extract_format using standard strftime notation (though not all formatting is supported).

experimental_strftime(input: timestamp, extract_format: string) → string

From input, extracts and formats the time as identified in extract_format using standard strftime notation (though not all formatting is supported).

experimental_strftime(input: timestamptz, extract_format: string) → string

From input, extracts and formats the time as identified in extract_format using standard strftime notation (though not all formatting is supported).

experimental_strptime(input: string, format: string) → timestamptz

Returns input as a timestamptz using format (which uses standard strptime formatting).

extract(element: string, input: date) → float

Extracts element from input.

Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch

extract(element: string, input: interval) → float

Extracts element from input.

Compatible elements: millennium, century, decade, year, month, day, hour, minute, second, millisecond, microsecond, epoch

extract(element: string, input: time) → float

Extracts element from input.

Compatible elements: hour, minute, second, millisecond, microsecond, epoch

extract(element: string, input: timestamp) → float

Extracts element from input.

Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch

extract(element: string, input: timestamptz) → float

Extracts element from input.

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

extract(element: string, input: timetz) → float

Extracts element from input.

Compatible elements: hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute

extract_duration(element: string, input: interval) → int

Extracts element from input. Compatible elements: hour, minute, second, millisecond, microsecond. This is deprecated in favor of extract which supports duration.

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 without an error.

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.

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.

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.

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.

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.

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.

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.

now() → timestamp

Returns the time of the current transaction.

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

now() → timestamptz

Returns the time of the current transaction.

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

This function is the preferred overload and will be evaluated by default.

statement_timestamp() → timestamp

Returns the start time of the current statement.

statement_timestamp() → timestamptz

Returns the start time of the current statement.

timeofday() → string

Returns the current system time on one of the cluster nodes as a string.

timezone(timezone: string, time: time) → timetz

Treat given time without time zone as located in the specified time zone.

timezone(timezone: string, timestamp: timestamp) → timestamptz

Treat given time stamp without time zone as located in the specified time zone.

timezone(timezone: string, timestamptz: timestamptz) → timestamp

Convert given time stamp with time zone to the new time zone, with no time zone designation.

timezone(timezone: string, timestamptz_string: string) → timestamp

Convert given time stamp with time zone to the new time zone, with no time zone designation.

timezone(timezone: string, timetz: timetz) → timetz

Convert given time with time zone to the new time zone.

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.

transaction_timestamp() → timestamp

Returns the time of the current transaction.

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

transaction_timestamp() → timestamptz

Returns the time of the current transaction.

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

This function is the preferred overload and will be evaluated by default.

Enum functions

Function → ReturnsDescription
enum_first(val: anyenum) → anyelement

Returns the first value of the input enum type.

enum_last(val: anyenum) → anyelement

Returns the last value of the input enum type.

enum_range(lower: anyenum, upper: anyenum) → anyelement

Returns all values of the input enum in an ordered array between the two arguments (inclusive).

enum_range(val: anyenum) → anyelement

Returns all values of the input enum in an ordered array.

FLOAT functions

Function → ReturnsDescription
abs(val: decimal) → decimal

Calculates the absolute value of val.

abs(val: float) → float

Calculates the absolute value of val.

abs(val: int) → int

Calculates the absolute value of val.

acos(val: float) → float

Calculates the inverse cosine of val.

acosd(val: float) → float

Calculates the inverse cosine of val with the result in degrees

acosh(val: float) → float

Calculates the inverse hyperbolic cosine of val.

asin(val: float) → float

Calculates the inverse sine of val.

asind(val: float) → float

Calculates the inverse sine of val with the result in degrees.

asinh(val: float) → float

Calculates the inverse hyperbolic sine of val.

atan(val: float) → float

Calculates the inverse tangent of val.

atan2(x: float, y: float) → float

Calculates the inverse tangent of x/y.

atan2d(x: float, y: float) → float

Calculates the inverse tangent of x/y with the result in degrees

atand(val: float) → float

Calculates the inverse tangent of val with the result in degrees.

atanh(val: float) → float

Calculates the inverse hyperbolic tangent of val.

cbrt(val: decimal) → decimal

Calculates the cube root (∛) of val.

cbrt(val: float) → float

Calculates the cube root (∛) of val.

ceil(val: decimal) → decimal

Calculates the smallest integer not smaller than val.

ceil(val: float) → float

Calculates the smallest integer not smaller than val.

ceil(val: int) → float

Calculates the smallest integer not smaller than val.

ceiling(val: decimal) → decimal

Calculates the smallest integer not smaller than val.

ceiling(val: float) → float

Calculates the smallest integer not smaller than val.

ceiling(val: int) → float

Calculates the smallest integer not smaller than val.

cos(val: float) → float

Calculates the cosine of val.

cosd(val: float) → float

Calculates the cosine of val where val is in degrees.

cosh(val: float) → float

Calculates the hyperbolic cosine of val.

cot(val: float) → float

Calculates the cotangent of val.

cotd(val: float) → float

Calculates the cotangent of val where val is in degrees.

degrees(val: float) → float

Converts val as a radian value to a degree value.

div(x: decimal, y: decimal) → decimal

Calculates the integer quotient of x/y.

div(x: float, y: float) → float

Calculates the integer quotient of x/y.

div(x: int, y: int) → int

Calculates the integer quotient of x/y.

exp(val: decimal) → decimal

Calculates e ^ val.

exp(val: float) → float

Calculates e ^ val.

floor(val: decimal) → decimal

Calculates the largest integer not greater than val.

floor(val: float) → float

Calculates the largest integer not greater than val.

floor(val: int) → float

Calculates the largest integer not greater than val.

isnan(val: decimal) → bool

Returns true if val is NaN, false otherwise.

isnan(val: float) → bool

Returns true if val is NaN, false otherwise.

ln(val: decimal) → decimal

Calculates the natural log of val.

ln(val: float) → float

Calculates the natural log of val.

log(b: decimal, x: decimal) → decimal

Calculates the base b log of val.

log(b: float, x: float) → float

Calculates the base b log of val.

log(val: decimal) → decimal

Calculates the base 10 log of val.

log(val: float) → float

Calculates the base 10 log of val.

mod(x: decimal, y: decimal) → decimal

Calculates x%y.

mod(x: float, y: float) → float

Calculates x%y.

mod(x: int, y: int) → int

Calculates x%y.

pi() → float

Returns the value for pi (3.141592653589793).

pow(x: decimal, y: decimal) → decimal

Calculates x^y.

pow(x: float, y: float) → float

Calculates x^y.

pow(x: int, y: int) → int

Calculates x^y.

power(x: decimal, y: decimal) → decimal

Calculates x^y.

power(x: float, y: float) → float

Calculates x^y.

power(x: int, y: int) → int

Calculates x^y.

radians(val: float) → float

Converts val as a degree value to a radians value.

random() → float

Returns a random float between 0 and 1.

round(input: decimal, decimal_accuracy: int) → decimal

Keeps decimal_accuracy number of figures to the right of the zero position in input using half away from zero rounding. If decimal_accuracy is not in the range -2^31…(2^31-1), the results are undefined.

round(input: float, decimal_accuracy: int) → float

Keeps decimal_accuracy number of figures to the right of the zero position in input using half to even (banker’s) rounding.

round(val: decimal) → decimal

Rounds val to the nearest integer, half away from zero: round(+/-2.4) = +/-2, round(+/-2.5) = +/-3.

round(val: float) → float

Rounds val to the nearest integer using half to even (banker’s) rounding.

sign(val: decimal) → decimal

Determines the sign of val: 1 for positive; 0 for 0 values; -1 for negative.

sign(val: float) → float

Determines the sign of val: 1 for positive; 0 for 0 values; -1 for negative.

sign(val: int) → int

Determines the sign of val: 1 for positive; 0 for 0 values; -1 for negative.

sin(val: float) → float

Calculates the sine of val.

sind(val: float) → float

Calculates the sine of val where val is in degrees.

sinh(val: float) → float

Calculates the hyperbolic sine of val.

sqrt(val: decimal) → decimal

Calculates the square root of val.

sqrt(val: float) → float

Calculates the square root of val.

tan(val: float) → float

Calculates the tangent of val.

tand(val: float) → float

Calculates the tangent of val where val is in degrees.

tanh(val: float) → float

Calculates the hyperbolic tangent of val.

trunc(val: decimal) → decimal

Truncates the decimal values of val.

trunc(val: float) → float

Truncates the decimal values of val.

ID generation functions

Function → ReturnsDescription
experimental_uuid_v4() → bytes

Returns a UUID.

gen_random_uuid() → uuid

Generates a random UUID and returns it as a value of UUID type.

unique_rowid() → int

Returns a unique ID used by CockroachDB to generate unique row IDs if a Primary Key isn’t defined for the table. The value is a combination of the insert timestamp and the ID of the node executing the statement, which guarantees this combination is globally unique. However, there can be gaps and the order is not completely guaranteed.

uuid_v4() → bytes

Returns a UUID.

INET functions

Function → ReturnsDescription
abbrev(val: inet) → string

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

For example, abbrev('192.168.1.2/24') returns '192.168.1.2/24'

broadcast(val: inet) → inet

Gets the broadcast address for the network address represented by the value.

For example, broadcast('192.168.1.2/24') returns '192.168.1.255/24'

family(val: inet) → int

Extracts the IP family of the value; 4 for IPv4, 6 for IPv6.

For example, family('::1') returns 6

host(val: inet) → string

Extracts the address part of the combined address/prefixlen value as text.

For example, host('192.168.1.2/16') returns '192.168.1.2'

hostmask(val: inet) → inet

Creates an IP host mask corresponding to the prefix length in the value.

For example, hostmask('192.168.1.2/16') returns '0.0.255.255'

masklen(val: inet) → int

Retrieves the prefix length stored in the value.

For example, masklen('192.168.1.2/16') returns 16

netmask(val: inet) → inet

Creates an IP network mask corresponding to the prefix length in the value.

For example, netmask('192.168.1.2/16') returns '255.255.0.0'

set_masklen(val: inet, prefixlen: int) → inet

Sets the prefix length of val to prefixlen.

For example, set_masklen('192.168.1.2', 16) returns '192.168.1.2/16'.

text(val: inet) → string

Converts the IP address and prefix length to text.

INT functions

Function → ReturnsDescription
crc32c(bytes...) → int

Calculates the CRC-32 hash using the Castagnoli polynomial.

crc32c(string...) → int

Calculates the CRC-32 hash using the Castagnoli polynomial.

crc32ieee(bytes...) → int

Calculates the CRC-32 hash using the IEEE polynomial.

crc32ieee(string...) → int

Calculates the CRC-32 hash using the IEEE polynomial.

fnv32(bytes...) → int

Calculates the 32-bit FNV-1 hash value of a set of values.

fnv32(string...) → int

Calculates the 32-bit FNV-1 hash value of a set of values.

fnv32a(bytes...) → int

Calculates the 32-bit FNV-1a hash value of a set of values.

fnv32a(string...) → int

Calculates the 32-bit FNV-1a hash value of a set of values.

fnv64(bytes...) → int

Calculates the 64-bit FNV-1 hash value of a set of values.

fnv64(string...) → int

Calculates the 64-bit FNV-1 hash value of a set of values.

fnv64a(bytes...) → int

Calculates the 64-bit FNV-1a hash value of a set of values.

fnv64a(string...) → int

Calculates the 64-bit FNV-1a hash value of a set of values.

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.

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.

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

JSONB functions

Function → ReturnsDescription
array_to_json(array: anyelement[]) → jsonb

Returns the array as JSON or JSONB.

array_to_json(array: anyelement[], pretty_bool: bool) → jsonb

Returns the array as JSON or JSONB.

crdb_internal.json_to_pb(pbname: string, json: jsonb) → bytes

Convert JSONB data to protocol message bytes

crdb_internal.pb_to_json(pbname: string, data: bytes) → jsonb

Converts protocol message to its JSONB representation.

json_array_length(json: jsonb) → int

Returns the number of elements in the outermost JSON or JSONB array.

json_build_array(anyelement...) → jsonb

Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list.

json_build_object(anyelement...) → jsonb

Builds a JSON object out of a variadic argument list.

json_extract_path(jsonb, string...) → jsonb

Returns the JSON value pointed to by the variadic arguments.

json_object(keys: string[], values: string[]) → jsonb

This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.

json_object(texts: string[]) → jsonb

Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs.

json_remove_path(val: jsonb, path: string[]) → jsonb

Remove the specified path from the JSON object.

json_set(val: jsonb, path: string[], to: jsonb) → jsonb

Returns the JSON value pointed to by the variadic arguments.

json_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb

Returns the JSON value pointed to by the variadic arguments. If create_missing is false, new keys will not be inserted to objects and values will not be prepended or appended to arrays.

json_strip_nulls(from_json: jsonb) → jsonb

Returns from_json with all object fields that have null values omitted. Other null values are untouched.

json_typeof(val: jsonb) → string

Returns the type of the outermost JSON value as a text string.

jsonb_array_length(json: jsonb) → int

Returns the number of elements in the outermost JSON or JSONB array.

jsonb_build_array(anyelement...) → jsonb

Builds a possibly-heterogeneously-typed JSON or JSONB array out of a variadic argument list.

jsonb_build_object(anyelement...) → jsonb

Builds a JSON object out of a variadic argument list.

jsonb_extract_path(jsonb, string...) → jsonb

Returns the JSON value pointed to by the variadic arguments.

jsonb_insert(target: jsonb, path: string[], new_val: jsonb) → jsonb

Returns the JSON value pointed to by the variadic arguments. new_val will be inserted before path target.

jsonb_insert(target: jsonb, path: string[], new_val: jsonb, insert_after: bool) → jsonb

Returns the JSON value pointed to by the variadic arguments. If insert_after is true (default is false), new_val will be inserted after path target.

jsonb_object(keys: string[], values: string[]) → jsonb

This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the one-argument form.

jsonb_object(texts: string[]) → jsonb

Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs.

jsonb_pretty(val: jsonb) → string

Returns the given JSON value as a STRING indented and with newlines.

jsonb_set(val: jsonb, path: string[], to: jsonb) → jsonb

Returns the JSON value pointed to by the variadic arguments.

jsonb_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb

Returns the JSON value pointed to by the variadic arguments. If create_missing is false, new keys will not be inserted to objects and values will not be prepended or appended to arrays.

jsonb_strip_nulls(from_json: jsonb) → jsonb

Returns from_json with all object fields that have null values omitted. Other null values are untouched.

jsonb_typeof(val: jsonb) → string

Returns the type of the outermost JSON value as a text string.

to_json(val: anyelement) → jsonb

Returns the value as JSON or JSONB.

to_jsonb(val: anyelement) → jsonb

Returns the value as JSON or JSONB.

Multi-tenancy functions

Function → ReturnsDescription
crdb_internal.sql_liveness_is_alive(session_id: bytes) → bool

Checks is given sqlliveness session id is not expired

STRING[] functions

Function → ReturnsDescription
regexp_split_to_array(string: string, pattern: string) → string[]

Split string using a POSIX regular expression as the delimiter.

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:

Flag Description
c Case-sensitive matching
g Global matching (match each substring instead of only the first)
i Case-insensitive matching
m or n Newline-sensitive (see below)
p Partial newline-sensitive matching (see below)
s Newline-insensitive (default)
w Inverse partial newline-sensitive matching (see below)
Mode . and [^...] match newlines ^ and $ match line boundaries
s yes no
w yes yes
p no no
m/n no yes

Sequence functions

Function → ReturnsDescription
currval(sequence_name: string) → int

Returns the latest value obtained with nextval for this sequence in this session.

lastval() → int

Return value most recently obtained with nextval in this session.

nextval(sequence_name: string) → int

Advances the given sequence and returns its new value.

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.

setval(sequence_name: string, value: int) → int

Set the given sequence’s current value. The next call to nextval will return value + Increment

setval(sequence_name: string, value: int, is_called: bool) → int

Set the given sequence’s current value. If is_called is false, the next call to nextval will return value; otherwise value + Increment.

Set-returning functions

Function → ReturnsDescription
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)

crdb_internal.testing_callback(name: string) → int

For internal CRDB testing only. The function calls a callback identified by name registered with the server by the test.

crdb_internal.unary_table() → tuple

Produces a virtual table containing a single row with no values.

This function is used only by CockroachDB’s developers for testing purposes.

generate_series(start: int, end: int) → int

Produces a virtual table containing the integer values from start to end, inclusive.

generate_series(start: int, end: int, step: int) → int

Produces a virtual table containing the integer values from start to end, inclusive, by increment of step.

generate_series(start: timestamp, end: timestamp, step: interval) → timestamp

Produces a virtual table containing the timestamp values from start to end, inclusive, by increment of step.

generate_subscripts(array: anyelement[]) → int

Returns a series comprising the given array’s subscripts.

generate_subscripts(array: anyelement[], dim: int) → int

Returns a series comprising the given array’s subscripts.

generate_subscripts(array: anyelement[], dim: int, reverse: bool) → int

Returns a series comprising the given array’s subscripts.

When reverse is true, the series is returned in reverse order.

information_schema._pg_expandarray(input: anyelement[]) → anyelement

Returns the input array as a set of rows with an index

json_array_elements(input: jsonb) → jsonb

Expands a JSON array to a set of JSON values.

json_array_elements_text(input: jsonb) → string

Expands a JSON array to a set of text values.

json_each(input: jsonb) → tuple{string AS key, jsonb AS value}

Expands the outermost JSON or JSONB object into a set of key/value pairs.

json_each_text(input: jsonb) → tuple{string AS key, string AS value}

Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.

json_object_keys(input: jsonb) → string

Returns sorted set of keys in the outermost JSON object.

jsonb_array_elements(input: jsonb) → jsonb

Expands a JSON array to a set of JSON values.

jsonb_array_elements_text(input: jsonb) → string

Expands a JSON array to a set of text values.

jsonb_each(input: jsonb) → tuple{string AS key, jsonb AS value}

Expands the outermost JSON or JSONB object into a set of key/value pairs.

jsonb_each_text(input: jsonb) → tuple{string AS key, string AS value}

Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.

jsonb_object_keys(input: jsonb) → string

Returns sorted set of keys in the outermost JSON object.

pg_get_keywords() → tuple{string AS word, string AS catcode, string AS catdesc}

Produces a virtual table containing the keywords known to the SQL parser.

regexp_split_to_table(string: string, pattern: string) → string

Split string using a POSIX regular expression as the delimiter.

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:

Flag Description
c Case-sensitive matching
g Global matching (match each substring instead of only the first)
i Case-insensitive matching
m or n Newline-sensitive (see below)
p Partial newline-sensitive matching (see below)
s Newline-insensitive (default)
w Inverse partial newline-sensitive matching (see below)
Mode . and [^...] match newlines ^ and $ match line boundaries
s yes no
w yes yes
p no no
m/n no yes

unnest(anyelement[], anyelement[], anyelement[]...) → tuple

Returns the input arrays as a set of rows

unnest(input: anyelement[]) → anyelement

Returns the input array as a set of rows

Spatial functions

Function → ReturnsDescription
_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.

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

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

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

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

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

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

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

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

_st_dwithin(geography_a: geography, geography_b: geography, distance: float) → bool

Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

_st_dwithin(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool

Returns true if any of geography_a is within distance meters of geography_b, inclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

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

_st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float) → bool

Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

_st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool

Returns true if any of geography_a is within distance meters of geography_b, exclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

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

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

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

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

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

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

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

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.

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.

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.

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.

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.

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.

geometrytype(geometry: geometry) → string

Returns the type of geometry as a string.

This function utilizes the GEOS module.

geomfromewkb(val: bytes) → geometry

Returns the Geometry from an EWKB representation.

geomfromewkt(val: string) → geometry

Returns the Geometry from an EWKT representation.

postgis_addbbox(geometry: geometry) → geometry

Compatibility placeholder function with PostGIS. This does not perform any operation on the Geometry.

postgis_dropbbox(geometry: geometry) → geometry

Compatibility placeholder function with PostGIS. This does not perform any operation on the Geometry.

postgis_extensions_upgrade() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_full_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_geos_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_getbbox(geometry: geometry) → box2d

Returns a box2d encapsulating the given Geometry.

postgis_hasbbox(geometry: geometry) → bool

Returns whether a given Geometry has a bounding box. False for points and empty geometries; always true otherwise.

postgis_lib_build_date() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_lib_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_liblwgeom_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_libxml_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_proj_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_scripts_build_date() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_scripts_installed() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_scripts_released() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

postgis_wagyu_version() → string

Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits.

st_addpoint(line_string: geometry, point: geometry) → geometry

Adds a Point to the end of a LineString.

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

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
| d e y_off | | y | \ 0 0 1 / \ 0 /

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.

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.

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.

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.

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.

st_area(geometry: geometry) → float

Returns the area of the given geometry.

This function utilizes the GEOS module.

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.

st_area2d(geometry: geometry) → float

Returns the area of the given geometry.

This function utilizes the GEOS module.

st_asbinary(geography: geography) → bytes

Returns the WKB representation of a given Geography.

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 - xdr for big endian and ndr for little endian.

st_asbinary(geometry: geometry) → bytes

Returns the WKB representation of a given Geometry.

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 - xdr for big endian and ndr for little endian.

st_asewkb(geography: geography) → bytes

Returns the EWKB representation of a given Geography.

st_asewkb(geometry: geometry) → bytes

Returns the EWKB representation of a given Geometry.

st_asewkt(geography: geography) → string

Returns the EWKT representation of a given Geography. A default of 15 decimal digits is used.

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 .. Use -1 to print as many digits as required to rebuild the same number.

st_asewkt(geometry: geometry) → string

Returns the EWKT representation of a given Geometry. A maximum of 15 decimal digits is used.

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 .. Use -1 to print as many digits as required to rebuild the same number.

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.

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 .. Use -1 to print as many digits as required to rebuild the same number.

This variant will cast all geometry_str arguments into Geometry types.

st_asgeojson(geography: geography) → string

Returns the GeoJSON representation of a given Geography. Coordinates have a maximum of 9 decimal digits.

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.

st_asgeojson(geography: geography, max_decimal_digits: int, options: int) → string

Returns the GeoJSON representation of a given Geography with max_decimal_digits output for each coordinate value.

Options is a flag that can be bitmasked. The options are:

  • 0: no option (default for Geography)
  • 1: GeoJSON BBOX
  • 2: GeoJSON Short CRS (e.g EPSG:4326)
  • 4: GeoJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)
  • 8: GeoJSON Short CRS if not EPSG:4326
st_asgeojson(geometry: geometry) → string

Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits.

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.

st_asgeojson(geometry: geometry, max_decimal_digits: int, options: int) → string

Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value.

Options is a flag that can be bitmasked. The options are:

  • 0: no option
  • 1: GeoJSON BBOX
  • 2: GeoJSON Short CRS (e.g EPSG:4326)
  • 4: GeoJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)
  • 8: GeoJSON Short CRS if not EPSG:4326 (default for Geometry)
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.

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.

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:

  • 0: no option
  • 1: GeoJSON BBOX
  • 2: GeoJSON Short CRS (e.g EPSG:4326)
  • 4: GeoJSON Long CRS (e.g urn:ogc:def:crs:EPSG::4326)
  • 8: GeoJSON Short CRS if not EPSG:4326 (default for Geometry)

This variant will cast all geometry_str arguments into Geometry types.

st_asgeojson(row: tuple) → string

Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits.

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.

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.

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.

st_ashexewkb(geography: geography) → string

Returns the EWKB representation in hex of a given Geography.

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 - xdr for big endian and ndr for little endian.

st_ashexewkb(geometry: geometry) → string

Returns the EWKB representation in hex of a given Geometry.

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 - xdr for big endian and ndr for little endian.

st_ashexwkb(geography: geography) → string

Returns the WKB representation in hex of a given Geography.

st_ashexwkb(geometry: geometry) → string

Returns the WKB representation in hex of a given Geometry.

st_askml(geography: geography) → string

Returns the KML representation of a given Geography.

st_askml(geometry: geometry) → string

Returns the KML representation of a given Geometry.

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.

st_astext(geography: geography) → string

Returns the WKT representation of a given Geography. A default of 15 decimal digits is used.

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 .. Use -1 to print as many digits as required to rebuild the same number.

st_astext(geometry: geometry) → string

Returns the WKT representation of a given Geometry. A maximum of 15 decimal digits is used.

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 .. Use -1 to print as many digits as required to rebuild the same number.

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.

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 .. Use -1 to print as many digits as required to rebuild the same number.

This variant will cast all geometry_str arguments into Geometry types.

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.

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.

st_boundary(geometry: geometry) → geometry

Returns the closure of the combinatorial boundary of this Geometry.

This function utilizes the GEOS module.

st_box2dfromgeohash(geohash: string) → box2d

Return a Box2D from a GeoHash string with max precision.

st_box2dfromgeohash(geohash: string, precision: int) → box2d

Return a Box2D from a GeoHash string with supplied precision.

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.

st_buffer(geography: geography, distance: float, buffer_style_params: string) → geography

Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry.

This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are:

  • quad_segs=<int>, default 8
  • endcap=<round|flat|butt|square>, default round
  • join=<round|mitre|miter|bevel>, default round
  • side=<both|left|right>, default both
  • mitre_limit=<float>, default 5.0

This function utilizes the GEOS module.

This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326.

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.

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.

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.

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:

  • quad_segs=<int>, default 8
  • endcap=<round|flat|butt|square>, default round
  • join=<round|mitre|miter|bevel>, default round
  • side=<both|left|right>, default both
  • mitre_limit=<float>, default 5.0

This function utilizes the GEOS module.

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.

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.

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.

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.

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:

  • quad_segs=<int>, default 8
  • endcap=<round|flat|butt|square>, default round
  • join=<round|mitre|miter|bevel>, default round
  • side=<both|left|right>, default both
  • mitre_limit=<float>, default 5.0

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

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.

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.

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.

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.

st_centroid(geometry: geometry) → geometry

Returns the centroid of the given geometry.

This function utilizes the GEOS module.

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.

st_clipbybox2d(geometry: geometry, box2d: box2d) → geometry

Clips the geometry to conform to the bounding box specified by box2d.

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.

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.

st_combinebbox(box2d: box2d, geometry: geometry) → box2d

Combines the current bounding box with the bounding box of the Geometry.

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.

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.

st_convexhull(geometry: geometry) → geometry

Returns a geometry that represents the Convex Hull of the given geometry.

This function utilizes the GEOS module.

st_coorddim(geometry: geometry) → int

Returns the number of coordinate dimensions of a given Geometry.

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.

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.

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.

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.

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.

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.

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.

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.

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.

st_difference(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the difference of two Geometries.

This function utilizes the GEOS module.

st_dimension(geometry: geometry) → int

Returns the number of topological dimensions of a given Geometry.

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.

st_distance(geography_a: geography, geography_b: geography) → float

Returns the distance in meters between geography_a and geography_b. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

This function utilizes the GeographicLib library for spheroid calculations.

st_distance(geography_a: geography, geography_b: geography, use_spheroid: bool) → float

Returns the distance in meters between geography_a and geography_b."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

st_distance(geometry_a: geometry, geometry_b: geometry) → float

Returns the distance between the given geometries.

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.

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.

st_distancespheroid(geometry_a: geometry, geometry_b: geometry) → float

Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a spheroid."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

st_dwithin(geography_a: geography, geography_b: geography, distance: float) → bool

Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant will attempt to utilize any available spatial index.

st_dwithin(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool

Returns true if any of geography_a is within distance meters of geography_b, inclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant will attempt to utilize any available spatial index.

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.

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.

st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float) → bool

Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant will attempt to utilize any available spatial index.

st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool

Returns true if any of geography_a is within distance meters of geography_b, exclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant will attempt to utilize any available spatial index.

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.

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.

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.

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.

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.

st_expand(box2d: box2d, delta: float) → box2d

Extends the box2d by delta units across all dimensions.

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.

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.

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.

st_exteriorring(geometry: geometry) → geometry

Returns the exterior ring of a Polygon as a LineString. Returns NULL if the shape is not a Polygon.

st_flipcoordinates(geometry: geometry) → geometry

Returns a new geometry with the X and Y axes flipped.

st_force2d(geometry: geometry) → geometry

Returns a Geometry which only contains X and Y coordinates.

st_forcecollection(geometry: geometry) → geometry

Converts the geometry into a GeometryCollection.

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.

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.

st_frechetdistance(geometry_a: geometry, geometry_b: geometry) → float

Returns the Frechet distance between the given geometries.

This function utilizes the GEOS module.

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

This function utilizes the GEOS module.

st_geogfromewkb(val: bytes) → geography

Returns the Geography from an EWKB representation.

st_geogfromewkt(val: string) → geography

Returns the Geography from an EWKT representation.

st_geogfromgeojson(val: string) → geography

Returns the Geography from an GeoJSON representation.

st_geogfromgeojson(val: jsonb) → geography

Returns the Geography from an GeoJSON representation.

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.

st_geogfromtext(val: string) → geography

Returns the Geography from a WKT or EWKT representation.

st_geogfromwkb(bytes: bytes, srid: int) → geography

Returns the Geography from a WKB (or EWKB) representation with the given SRID set.

st_geogfromwkb(val: bytes) → geography

Returns the Geography from a WKB (or EWKB) representation.

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.

st_geographyfromtext(val: string) → geography

Returns the Geography from a WKT or EWKT representation.

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.

st_geohash(geography: geography, precision: int) → string

Returns a GeoHash representation of the geography with the supplied precision.

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.

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.

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.

st_geomcollfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not GeometryCollection, NULL is returned.

st_geomcollfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not GeometryCollection, NULL is returned.

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.

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.

st_geometryfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation.

st_geometryn(geometry: geometry, n: int) → geometry

Returns the n-th Geometry (1-indexed). Returns NULL if out of bounds.

st_geometrytype(geometry: geometry) → string

Returns the type of geometry as a string prefixed with ST_.

This function utilizes the GEOS module.

st_geomfromewkb(val: bytes) → geometry

Returns the Geometry from an EWKB representation.

st_geomfromewkt(val: string) → geometry

Returns the Geometry from an EWKT representation.

st_geomfromgeohash(geohash: string) → geometry

Return a POLYGON Geometry from a GeoHash string with max precision.

st_geomfromgeohash(geohash: string, precision: int) → geometry

Return a POLYGON Geometry from a GeoHash string with supplied precision.

st_geomfromgeojson(val: string) → geometry

Returns the Geometry from an GeoJSON representation.

st_geomfromgeojson(val: jsonb) → geometry

Returns the Geometry from an GeoJSON representation.

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.

st_geomfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation.

st_geomfromwkb(bytes: bytes, srid: int) → geometry

Returns the Geometry from a WKB (or EWKB) representation with the given SRID set.

st_geomfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation.

st_hausdorffdistance(geometry_a: geometry, geometry_b: geometry) → float

Returns the Hausdorff distance between the given geometries.

This function utilizes the GEOS module.

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.

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.

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.

st_intersection(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the point intersections of the given geometries.

This function utilizes the GEOS module.

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.

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.

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.

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.

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.

st_iscollection(geometry: geometry) → bool

Returns whether the geometry is of a collection type (including multi-types).

st_isempty(geometry: geometry) → bool

Returns whether the geometry is empty.

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.

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.

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.

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.

st_isvalid(geometry: geometry) → bool

Returns whether the geometry is valid as defined by the OGC spec.

This function utilizes the GEOS module.

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.

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.

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.

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.

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.

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.

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.

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.

st_linefrommultipoint(geometry: geometry) → geometry

Creates a LineString from a MultiPoint geometry.

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.

st_linefromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not LineString, NULL is returned.

st_linefromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not LineString, NULL is returned.

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.

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.

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.

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.

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.

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.

st_linestringfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not LineString, NULL is returned.

st_linestringfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not LineString, NULL is returned.

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.

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.

st_makebox2d(geometry_a: geometry, geometry_b: geometry) → box2d

Creates a box2d from two points. Errors if arguments are not two non-empty points.

st_makepoint(x: float, y: float) → geometry

Returns a new Point with the given X and Y coordinates.

st_makepolygon(geometry: geometry) → geometry

Returns a new Polygon with the given outer LineString.

st_makepolygon(outer: geometry, interior: anyelement[]) → geometry

Returns a new Polygon with the given outer LineString and interior (hole) LineString(s).

st_makevalid(geometry: geometry) → geometry

Returns a valid form of the given geometry according to the OGC spec.

This function utilizes the GEOS module.

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.

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

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.

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.

st_mlinefromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned.

st_mlinefromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned.

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.

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.

st_mpointfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPoint, NULL is returned.

st_mpointfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPoint, NULL is returned.

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.

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.

st_mpolyfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned.

st_mpolyfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned.

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.

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.

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.

st_multilinefromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned.

st_multilinefromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned.

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.

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.

st_multilinestringfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned.

st_multilinestringfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned.

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.

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.

st_multipointfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPoint, NULL is returned.

st_multipointfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPoint, NULL is returned.

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.

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.

st_multipolyfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned.

st_multipolyfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned.

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.

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.

st_multipolygonfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned.

st_multipolygonfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned.

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.

st_ndims(geometry: geometry) → int

Returns the number of coordinate dimensions of a given Geometry.

st_normalize(geometry: geometry) → geometry

Returns the geometry in its normalized form.

This function utilizes the GEOS module.

st_npoints(geometry: geometry) → int

Returns the number of points in a given Geometry. Works for any shape type.

st_nrings(geometry: geometry) → int

Returns the number of rings in a Polygon Geometry. Returns 0 if the shape is not a Polygon.

st_numgeometries(geometry: geometry) → int

Returns the number of shapes inside a given Geometry.

st_numinteriorring(geometry: geometry) → int

Returns the number of interior rings in a Polygon Geometry. Returns NULL if the shape is not a Polygon.

st_numinteriorrings(geometry: geometry) → int

Returns the number of interior rings in a Polygon Geometry. Returns NULL if the shape is not a Polygon.

st_numpoints(geometry: geometry) → int

Returns the number of points in a LineString. Returns NULL if the Geometry is not a LineString.

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.

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.

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.

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.

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.

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.

st_point(x: float, y: float) → geometry

Returns a new Point with the given X and Y coordinates.

st_pointfromgeohash(geohash: string) → geometry

Return a POINT Geometry from a GeoHash string with max precision.

st_pointfromgeohash(geohash: string, precision: int) → geometry

Return a POINT Geometry from a GeoHash string with supplied precision.

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.

st_pointfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Point, NULL is returned.

st_pointfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Point, NULL is returned.

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.

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.

st_pointonsurface(geometry: geometry) → geometry

Returns a point that intersects with the given Geometry.

This function utilizes the GEOS module.

st_points(geometry: geometry) → geometry

Returns all coordinates in the given Geometry as a MultiPoint, including duplicates.

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.

st_polyfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Polygon, NULL is returned.

st_polyfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Polygon, NULL is returned.

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.

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.

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.

st_polygonfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Polygon, NULL is returned.

st_polygonfromwkb(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Polygon, NULL is returned.

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.

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.

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.

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.

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.

st_relatematch(intersection_matrix: string, pattern: string) → bool

Returns whether the given DE-9IM intersection matrix satisfies the given pattern.

st_removepoint(line_string: geometry, index: int) → geometry

Removes the Point at the given 0-based index and returns the modified LineString geometry.

st_removerepeatedpoints(geometry: geometry) → geometry

Returns a geometry with repeated points removed.

st_removerepeatedpoints(geometry: geometry, tolerance: float) → geometry

Returns a geometry with repeated points removed, within the given distance tolerance.

st_reverse(geometry: geometry) → geometry

Returns a modified geometry by reversing the order of its vertices.

st_rotate(g: geometry, angle_radians: float) → geometry

Returns a modified Geometry whose coordinates are rotated around the origin by a rotation angle.

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.

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.

st_s2covering(geography: geography) → geography

Returns a geography which represents the S2 covering used by the index using the default index configuration.

st_s2covering(geography: geography, settings: string) → geography

Returns a geography which represents the S2 covering used by the index using the index configuration specified by the settings parameter.

The settings parameter uses the same format as the parameters inside the WITH in CREATE INDEX … WITH (…), e.g. CREATE INDEX t_idx ON t USING GIST(geom) WITH (s2_max_level=15, s2_level_mod=3) can be tried using SELECT ST_S2Covering(geography, ‘s2_max_level=15,s2_level_mod=3’).

st_s2covering(geometry: geometry) → geometry

Returns a geometry which represents the S2 covering used by the index using the default index configuration.

st_s2covering(geometry: geometry, settings: string) → geometry

Returns a geometry which represents the S2 covering used by the index using the index configuration specified by the settings parameter.

The settings parameter uses the same format as the parameters inside the WITH in CREATE INDEX … WITH (…), e.g. CREATE INDEX t_idx ON t USING GIST(geom) WITH (s2_max_level=15, s2_level_mod=3) can be tried using SELECT ST_S2Covering(geometry, ‘s2_max_level=15,s2_level_mod=3’).

st_scale(g: geometry, factor: geometry) → geometry

Returns a modified Geometry scaled by taking in a Geometry as the factor.

st_scale(g: geometry, factor: geometry, origin: geometry) → geometry

Returns a modified Geometry scaled by the Geometry factor relative to a false origin.

st_scale(geometry: geometry, x_factor: float, y_factor: float) → geometry

Returns a modified Geometry scaled by the given factors.

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.

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.

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.

st_setsrid(geography: geography, srid: int) → geography

Sets a Geography to a new SRID without transforming the coordinates.

st_setsrid(geometry: geometry, srid: int) → geometry

Sets a Geometry to a new SRID without transforming the coordinates.

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.

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.

st_simplify(geometry: geometry, tolerance: float) → geometry

Simplifies the given geometry using the Douglas-Peucker algorithm.

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.

st_simplifypreservetopology(geometry: geometry, tolerance: float) → geometry

Simplifies the given geometry using the Douglas-Peucker algorithm, avoiding the creation of invalid geometries.

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

st_snaptogrid(geometry: geometry, size: float) → geometry

Snap a geometry to a grid of the given size.

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.

st_srid(geography: geography) → int

Returns the Spatial Reference Identifier (SRID) for the ST_Geography as defined in spatial_ref_sys table.

st_srid(geometry: geometry) → int

Returns the Spatial Reference Identifier (SRID) for the ST_Geometry as defined in spatial_ref_sys table.

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.

st_summary(geography: geography) → string

Returns a text summary of the contents of the geography.

Flags shown square brackets after the geometry type have the following meaning:

  • M: has M coordinate
  • Z: has Z coordinate
  • B: has a cached bounding box
  • G: is geography
  • S: has spatial reference system
st_summary(geometry: geometry) → string

Returns a text summary of the contents of the geometry.

Flags shown square brackets after the geometry type have the following meaning:

  • M: has M coordinate
  • Z: has Z coordinate
  • B: has a cached bounding box
  • G: is geography
  • S: has spatial reference system
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.

st_symdifference(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the symmetric difference of both geometries.

This function utilizes the GEOS module.

st_symmetricdifference(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the symmetric difference of both geometries.

This function utilizes the GEOS module.

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.

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.

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.

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.

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.

st_translate(g: geometry, delta_x: float, delta_y: float) → geometry

Returns a modified Geometry translated by the given deltas.

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.

st_wkbtosql(val: bytes) → geometry

Returns the Geometry from a WKB (or EWKB) representation.

st_wkttosql(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation.

st_x(geometry: geometry) → float

Returns the X coordinate of a geometry if it is a Point.

st_y(geometry: geometry) → float

Returns the Y coordinate of a geometry if it is a Point.

String and byte functions

Function → ReturnsDescription
ascii(val: string) → int

Returns the character code of the first character in val. Despite the name, the function supports Unicode too.

bit_length(val: bytes) → int

Calculates the number of bits used to represent val.

bit_length(val: string) → int

Calculates the number of bits used to represent val.

bit_length(val: varbit) → int

Calculates the number of bits used to represent val.

btrim(input: string, trim_chars: string) → string

Removes any characters included in trim_chars from the beginning or end of input (applies recursively).

For example, btrim('doggie', 'eod') returns ggi.

btrim(val: string) → string

Removes all spaces from the beginning and end of val.

char_length(val: bytes) → int

Calculates the number of bytes in val.

char_length(val: string) → int

Calculates the number of characters in val.

character_length(val: bytes) → int

Calculates the number of bytes in val.

character_length(val: string) → int

Calculates the number of characters in val.

chr(val: int) → string

Returns the character with the code given in val. Inverse function of ascii().

concat(string...) → string

Concatenates a comma-separated list of strings.

concat_ws(string...) → string

Uses the first argument as a separator between the concatenation of the subsequent arguments.

For example concat_ws('!','wow','great') returns wow!great.

convert_from(str: bytes, enc: string) → string

Decode the bytes in str into a string using encoding enc. Supports encodings ‘UTF8’ and ‘LATIN1’.

convert_to(str: string, enc: string) → bytes

Encode the string str as a byte array using encoding enc. Supports encodings ‘UTF8’ and ‘LATIN1’.

decode(text: string, format: string) → bytes

Decodes data using format (hex / escape / base64).

encode(data: bytes, format: string) → string

Encodes data using format (hex / escape / base64).

from_ip(val: bytes) → string

Converts the byte string representation of an IP to its character string representation.

from_uuid(val: bytes) → string

Converts the byte string representation of a UUID to its character string representation.

get_bit(bit_string: varbit, index: int) → int

Extracts a bit at given index in the bit array.

get_bit(byte_string: bytes, index: int) → int

Extracts a bit at given index in the byte array.

initcap(val: string) → string

Capitalizes the first letter of val.

left(input: bytes, return_set: int) → bytes

Returns the first return_set bytes from input.

left(input: string, return_set: int) → string

Returns the first return_set characters from input.

length(val: bytes) → int

Calculates the number of bytes in val.

length(val: string) → int

Calculates the number of characters in val.

length(val: varbit) → int

Calculates the number of bits in val.

lower(val: string) → string

Converts all characters in val to their lower-case equivalents.

lpad(string: string, length: int) → string

Pads string to length by adding ’ ’ to the left of string.If string is longer than length it is truncated.

lpad(string: string, length: int, fill: string) → string

Pads string by adding fill to the left of string to make it length. If string is longer than length it is truncated.

ltrim(input: string, trim_chars: string) → string

Removes any characters included in trim_chars from the beginning (left-hand side) of input (applies recursively).

For example, ltrim('doggie', 'od') returns ggie.

ltrim(val: string) → string

Removes all spaces from the beginning (left-hand side) of val.

md5(bytes...) → string

Calculates the MD5 hash value of a set of values.

md5(string...) → string

Calculates the MD5 hash value of a set of values.

octet_length(val: bytes) → int

Calculates the number of bytes used to represent val.

octet_length(val: string) → int

Calculates the number of bytes used to represent val.

octet_length(val: varbit) → int

Calculates the number of bits used to represent val.

overlay(input: string, overlay_val: string, start_pos: int) → string

Replaces characters in input with overlay_val starting at start_pos (begins at 1).

For example, overlay('doggie', 'CAT', 2) returns dCATie.

overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string

Deletes the characters in input between start_pos and end_pos (count starts at 1), and then insert overlay_val at start_pos.

pg_collation_for(str: anyelement) → string

Returns the collation of the argument

quote_ident(val: string) → string

Return val suitably quoted to serve as identifier in a SQL statement.

quote_literal(val: string) → string

Return val suitably quoted to serve as string literal in a SQL statement.

quote_literal(val: anyelement) → string

Coerce val to a string and then quote it as a literal.

quote_nullable(val: string) → string

Coerce val to a string and then quote it as a literal. If val is NULL, returns ‘NULL’.

quote_nullable(val: anyelement) → string

Coerce val to a string and then quote it as a literal. If val is NULL, returns ‘NULL’.

regexp_extract(input: string, regex: string) → string

Returns the first match for the Regular Expression regex in input.

regexp_replace(input: string, regex: string, replace: string) → string

Replaces matches for the Regular Expression regex in input with the Regular Expression replace.

regexp_replace(input: string, regex: string, replace: string, flags: string) → string

Replaces matches for the regular expression regex in input with the regular expression replace using flags.

CockroachDB supports the following flags:

Flag Description
c Case-sensitive matching
g Global matching (match each substring instead of only the first)
i Case-insensitive matching
m or n Newline-sensitive (see below)
p Partial newline-sensitive matching (see below)
s Newline-insensitive (default)
w Inverse partial newline-sensitive matching (see below)
Mode . and [^...] match newlines ^ and $ match line boundaries
s yes no
w yes yes
p no no
m/n no yes

repeat(input: string, repeat_counter: int) → string

Concatenates input repeat_counter number of times.

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

replace(input: string, find: string, replace: string) → string

Replaces all occurrences of find with replace in input

reverse(val: string) → string

Reverses the order of the string’s characters.

right(input: bytes, return_set: int) → bytes

Returns the last return_set bytes from input.

right(input: string, return_set: int) → string

Returns the last return_set characters from input.

rpad(string: string, length: int) → string

Pads string to length by adding ’ ’ to the right of string. If string is longer than length it is truncated.

rpad(string: string, length: int, fill: string) → string

Pads string to length by adding fill to the right of string. If string is longer than length it is truncated.

rtrim(input: string, trim_chars: string) → string

Removes any characters included in trim_chars from the end (right-hand side) of input (applies recursively).

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

rtrim(val: string) → string

Removes all spaces from the end (right-hand side) of val.

set_bit(bit_string: varbit, index: int, to_set: int) → varbit

Updates a bit at given index in the bit array.

set_bit(byte_string: bytes, index: int, to_set: int) → bytes

Updates a bit at given index in the byte array.

sha1(bytes...) → string

Calculates the SHA1 hash value of a set of values.

sha1(string...) → string

Calculates the SHA1 hash value of a set of values.

sha256(bytes...) → string

Calculates the SHA256 hash value of a set of values.

sha256(string...) → string

Calculates the SHA256 hash value of a set of values.

sha512(bytes...) → string

Calculates the SHA512 hash value of a set of values.

sha512(string...) → string

Calculates the SHA512 hash value of a set of values.

split_part(input: string, delimiter: string, return_index_pos: int) → string

Splits input on delimiter and return the value in the return_index_pos position (starting at 1).

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

strpos(input: bytes, find: bytes) → int

Calculates the position where the byte subarray find begins in input.

strpos(input: string, find: string) → int

Calculates the position where the string find begins in input.

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

strpos(input: varbit, find: varbit) → int

Calculates the position where the bit subarray find begins in input.

substr(input: bytes, start_pos: int) → bytes

Returns a byte subarray of input starting at start_pos (count starts at 1).

substr(input: bytes, start_pos: int, length: int) → bytes

Returns a byte subarray of input starting at start_pos (count starts at 1) and including up to length characters.

substr(input: string, regex: string) → string

Returns a substring of input that matches the regular expression regex.

substr(input: string, regex: string, escape_char: string) → string

Returns a substring of input that matches the regular expression regex using escape_char as your escape character instead of </code>.

substr(input: string, start_pos: int) → string

Returns a substring of input starting at start_pos (count starts at 1).

substr(input: string, start_pos: int, length: int) → string

Returns a substring of input starting at start_pos (count starts at 1) and including up to length characters.

substr(input: varbit, start_pos: int) → varbit

Returns a bit subarray of input starting at start_pos (count starts at 1).

substr(input: varbit, start_pos: int, length: int) → varbit

Returns a bit subarray of input starting at start_pos (count starts at 1) and including up to length characters.

substring(input: bytes, start_pos: int) → bytes

Returns a byte subarray of input starting at start_pos (count starts at 1).

substring(input: bytes, start_pos: int, length: int) → bytes

Returns a byte subarray of input starting at start_pos (count starts at 1) and including up to length characters.

substring(input: string, regex: string) → string

Returns a substring of input that matches the regular expression regex.

substring(input: string, regex: string, escape_char: string) → string

Returns a substring of input that matches the regular expression regex using escape_char as your escape character instead of </code>.

substring(input: string, start_pos: int) → string

Returns a substring of input starting at start_pos (count starts at 1).

substring(input: string, start_pos: int, length: int) → string

Returns a substring of input starting at start_pos (count starts at 1) and including up to length characters.

substring(input: varbit, start_pos: int) → varbit

Returns a bit subarray of input starting at start_pos (count starts at 1).

substring(input: varbit, start_pos: int, length: int) → varbit

Returns a bit subarray of input starting at start_pos (count starts at 1) and including up to length characters.

to_english(val: int) → string

This function enunciates the value of its argument using English cardinals.

to_hex(val: bytes) → string

Converts val to its hexadecimal representation.

to_hex(val: int) → string

Converts val to its hexadecimal representation.

to_hex(val: string) → string

Converts val to its hexadecimal representation.

to_ip(val: string) → bytes

Converts the character string representation of an IP to its byte string representation.

to_uuid(val: string) → bytes

Converts the character string representation of a UUID to its byte string representation.

translate(input: string, find: string, replace: string) → string

In input, replaces the first character from find with the first character in replace; repeat for each character in find.

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

upper(val: string) → string

Converts all characters in val to their to their upper-case equivalents.

System info functions

Function → ReturnsDescription
cluster_logical_timestamp() → decimal

Returns the logical time of the current transaction.

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

Note that uses of this function disable server-side optimizations and may increase either contention or retry errors, or both.

crdb_internal.approximate_timestamp(timestamp: decimal) → timestamp

Converts the crdb_internal_mvcc_timestamp column into an approximate timestamp.

crdb_internal.check_consistency(stats_only: bool, start_key: bytes, end_key: bytes) → tuple{int AS range_id, bytes AS start_key, string AS start_key_pretty, string AS status, string AS detail}

Runs a consistency check on ranges touching the specified key range. an empty start or end key is treated as the minimum and maximum possible, respectively. stats_only should only be set to false when targeting a small number of ranges to avoid overloading the cluster. Each returned row contains the range ID, the status (a roachpb.CheckConsistencyResponse_Status), and verbose detail.

Example usage: SELECT * FROM crdb_internal.check_consistency(true, ‘\x02’, ‘\x04’)

crdb_internal.cluster_id() → uuid

Returns the cluster ID.

crdb_internal.cluster_name() → string

Returns the cluster name.

crdb_internal.completed_migrations() → string[]

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.encode_key(table_id: int, index_id: int, row_tuple: anyelement) → bytes

Generate the key for a row on a particular table and index.

crdb_internal.force_assertion_error(msg: string) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.force_error(errorCode: string, msg: string) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.force_log_fatal(msg: string) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.force_panic(msg: string) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.force_retry(val: interval) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.get_database_id(name: string) → int
crdb_internal.get_namespace_id(parent_id: int, name: string) → int
crdb_internal.get_zone_config(namespace_id: int) → bytes
crdb_internal.has_role_option(option: string) → bool

Returns whether the current user has the specified role option

crdb_internal.is_admin() → bool

Retrieves the current user’s admin status.

crdb_internal.lease_holder(key: bytes) → int

This function is used to fetch the leaseholder corresponding to a request key

crdb_internal.locality_value(key: string) → string

Returns the value of the specified locality key.

crdb_internal.no_constant_folding(input: anyelement) → anyelement

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.node_executable_version() → string

Returns the version of CockroachDB this node is running.

crdb_internal.node_id() → int

Returns the node ID.

crdb_internal.notice(msg: string) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.notice(severity: string, msg: string) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.num_geo_inverted_index_entries(table_id: int, index_id: int, val: geography) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.num_geo_inverted_index_entries(table_id: int, index_id: int, val: geometry) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.num_inverted_index_entries(val: anyelement[]) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.num_inverted_index_entries(val: jsonb) → int

This function is used only by CockroachDB’s developers for testing purposes.

crdb_internal.pretty_key(raw_key: bytes, skip_fields: int) → string

This function triggers a transaction retry, which can be useful for testing purposes.

crdb_internal.range_stats(key: bytes) → jsonb

This function is used to retrieve range statistics information as a JSON object.

crdb_internal.round_decimal_values(val: decimal, scale: int) → decimal

This function is used internally to round decimal values during mutations.

crdb_internal.round_decimal_values(val: decimal[], scale: int) → decimal[]

This function is used internally to round decimal array values during mutations.

crdb_internal.set_vmodule(vmodule_string: string) → int

Set the equivalent of the --vmodule flag on the gateway node processing this request; it affords control over the logging verbosity of different files. Example syntax: crdb_internal.set_vmodule('recordio=2,file=1,gfs*=3'). Reset with: crdb_internal.set_vmodule(''). Raising the verbosity can severely affect performance.

current_database() → string

Returns the current database.

current_schema() → string

Returns the current schema.

current_schemas(include_pg_catalog: bool) → string[]

Returns the valid schemas in the search path.

current_user() → string

Returns the current user. This function is provided for compatibility with PostgreSQL.

version() → string

Returns the node’s version of CockroachDB.

TIMETZ functions

Function → ReturnsDescription
current_time() → time

Returns the current transaction’s time with no time zone.

current_time() → timetz

Returns the current transaction’s time with time zone.

This function is the preferred overload and will be evaluated by default.

current_time(precision: int) → time

Returns the current transaction’s time with no time zone.

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.

localtime() → time

Returns the current transaction’s time with no time zone.

This function is the preferred overload and will be evaluated by default.

localtime() → timetz

Returns the current transaction’s time with time zone.

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.

localtime(precision: int) → timetz

Returns the current transaction’s time with time zone.

TUPLE functions

Function → ReturnsDescription
row_to_json(row: tuple) → jsonb

Returns the row as a JSON object.

Compatibility functions

Function → ReturnsDescription
format_type(type_oid: oid, typemod: int) → string

Returns the SQL name of a data type that is identified by its type OID and possibly a type modifier. Currently, the type modifier is ignored.

getdatabaseencoding() → string

Returns the current encoding name used by the database.

has_any_column_privilege(table: string, privilege: string) → bool

Returns whether or not the current user has privileges for any column of table.

has_any_column_privilege(table: oid, privilege: string) → bool

Returns whether or not the current user has privileges for any column of table.

has_any_column_privilege(user: string, table: string, privilege: string) → bool

Returns whether or not the user has privileges for any column of table.

has_any_column_privilege(user: string, table: oid, privilege: string) → bool

Returns whether or not the user has privileges for any column of table.

has_any_column_privilege(user: oid, table: string, privilege: string) → bool

Returns whether or not the user has privileges for any column of table.

has_any_column_privilege(user: oid, table: oid, privilege: string) → bool

Returns whether or not the user has privileges for any column of table.

has_column_privilege(table: string, column: int, privilege: string) → bool

Returns whether or not the current user has privileges for column.

has_column_privilege(table: string, column: string, privilege: string) → bool

Returns whether or not the current user has privileges for column.

has_column_privilege(table: oid, column: int, privilege: string) → bool

Returns whether or not the current user has privileges for column.

has_column_privilege(table: oid, column: string, privilege: string) → bool

Returns whether or not the current user has privileges for column.

has_column_privilege(user: string, table: string, column: int, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: string, table: string, column: string, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: string, table: oid, column: int, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: string, table: oid, column: string, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: oid, table: string, column: int, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: oid, table: string, column: string, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: oid, table: oid, column: int, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_column_privilege(user: oid, table: oid, column: string, privilege: string) → bool

Returns whether or not the user has privileges for column.

has_database_privilege(database: string, privilege: string) → bool

Returns whether or not the current user has privileges for database.

has_database_privilege(database: oid, privilege: string) → bool

Returns whether or not the current user has privileges for database.

has_database_privilege(user: string, database: string, privilege: string) → bool

Returns whether or not the user has privileges for database.

has_database_privilege(user: string, database: oid, privilege: string) → bool

Returns whether or not the user has privileges for database.

has_database_privilege(user: oid, database: string, privilege: string) → bool

Returns whether or not the user has privileges for database.

has_database_privilege(user: oid, database: oid, privilege: string) → bool

Returns whether or not the user has privileges for database.

has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool

Returns whether or not the current user has privileges for foreign-data wrapper.

has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool

Returns whether or not the current user has privileges for foreign-data wrapper.

has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

has_function_privilege(function: string, privilege: string) → bool

Returns whether or not the current user has privileges for function.

has_function_privilege(function: oid, privilege: string) → bool

Returns whether or not the current user has privileges for function.

has_function_privilege(user: string, function: string, privilege: string) → bool

Returns whether or not the user has privileges for function.

has_function_privilege(user: string, function: oid, privilege: string) → bool

Returns whether or not the user has privileges for function.

has_function_privilege(user: oid, function: string, privilege: string) → bool

Returns whether or not the user has privileges for function.

has_function_privilege(user: oid, function: oid, privilege: string) → bool

Returns whether or not the user has privileges for function.

has_language_privilege(language: string, privilege: string) → bool

Returns whether or not the current user has privileges for language.

has_language_privilege(language: oid, privilege: string) → bool

Returns whether or not the current user has privileges for language.

has_language_privilege(user: string, language: string, privilege: string) → bool

Returns whether or not the user has privileges for language.

has_language_privilege(user: string, language: oid, privilege: string) → bool

Returns whether or not the user has privileges for language.

has_language_privilege(user: oid, language: string, privilege: string) → bool

Returns whether or not the user has privileges for language.

has_language_privilege(user: oid, language: oid, privilege: string) → bool

Returns whether or not the user has privileges for language.

has_schema_privilege(schema: string, privilege: string) → bool

Returns whether or not the current user has privileges for schema.

has_schema_privilege(schema: oid, privilege: string) → bool

Returns whether or not the current user has privileges for schema.

has_schema_privilege(user: string, schema: string, privilege: string) → bool

Returns whether or not the user has privileges for schema.

has_schema_privilege(user: string, schema: oid, privilege: string) → bool

Returns whether or not the user has privileges for schema.

has_schema_privilege(user: oid, schema: string, privilege: string) → bool

Returns whether or not the user has privileges for schema.

has_schema_privilege(user: oid, schema: oid, privilege: string) → bool

Returns whether or not the user has privileges for schema.

has_sequence_privilege(sequence: string, privilege: string) → bool

Returns whether or not the current user has privileges for sequence.

has_sequence_privilege(sequence: oid, privilege: string) → bool

Returns whether or not the current user has privileges for sequence.

has_sequence_privilege(user: string, sequence: string, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

has_server_privilege(server: string, privilege: string) → bool

Returns whether or not the current user has privileges for foreign server.

has_server_privilege(server: oid, privilege: string) → bool

Returns whether or not the current user has privileges for foreign server.

has_server_privilege(user: string, server: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

has_server_privilege(user: string, server: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

has_server_privilege(user: oid, server: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

has_server_privilege(user: oid, server: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

has_table_privilege(table: string, privilege: string) → bool

Returns whether or not the current user has privileges for table.

has_table_privilege(table: oid, privilege: string) → bool

Returns whether or not the current user has privileges for table.

has_table_privilege(user: string, table: string, privilege: string) → bool

Returns whether or not the user has privileges for table.

has_table_privilege(user: string, table: oid, privilege: string) → bool

Returns whether or not the user has privileges for table.

has_table_privilege(user: oid, table: string, privilege: string) → bool

Returns whether or not the user has privileges for table.

has_table_privilege(user: oid, table: oid, privilege: string) → bool

Returns whether or not the user has privileges for table.

has_tablespace_privilege(tablespace: string, privilege: string) → bool

Returns whether or not the current user has privileges for tablespace.

has_tablespace_privilege(tablespace: oid, privilege: string) → bool

Returns whether or not the current user has privileges for tablespace.

has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

has_type_privilege(type: string, privilege: string) → bool

Returns whether or not the current user has privileges for type.

has_type_privilege(type: oid, privilege: string) → bool

Returns whether or not the current user has privileges for type.

has_type_privilege(user: string, type: string, privilege: string) → bool

Returns whether or not the user has privileges for type.

has_type_privilege(user: string, type: oid, privilege: string) → bool

Returns whether or not the user has privileges for type.

has_type_privilege(user: oid, type: string, privilege: string) → bool

Returns whether or not the user has privileges for type.

has_type_privilege(user: oid, type: oid, privilege: string) → bool

Returns whether or not the user has privileges for type.

oid(int: int) → oid

Converts an integer to an OID.

pg_sleep(seconds: float) → bool

pg_sleep makes the current session’s process sleep until seconds seconds have elapsed. seconds is a value of type double precision, so fractional-second delays can be specified.

Aggregate functions

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

Note:

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 → ReturnsDescription
array_agg(arg1: bool) → bool[]

Aggregates the selected values into an array.

array_agg(arg1: bytes) → bytes[]

Aggregates the selected values into an array.

array_agg(arg1: date) → date[]

Aggregates the selected values into an array.

array_agg(arg1: decimal) → decimal[]

Aggregates the selected values into an array.

array_agg(arg1: float) → float[]

Aggregates the selected values into an array.

array_agg(arg1: inet) → inet[]

Aggregates the selected values into an array.

array_agg(arg1: int) → int[]

Aggregates the selected values into an array.

array_agg(arg1: interval) → interval[]

Aggregates the selected values into an array.

array_agg(arg1: string) → string[]

Aggregates the selected values into an array.

array_agg(arg1: time) → time[]

Aggregates the selected values into an array.

array_agg(arg1: timestamp) → timestamp[]

Aggregates the selected values into an array.

array_agg(arg1: timestamptz) → timestamptz[]

Aggregates the selected values into an array.

array_agg(arg1: uuid) → uuid[]

Aggregates the selected values into an array.

array_agg(arg1: box2d) → box2d[]

Aggregates the selected values into an array.

array_agg(arg1: geography) → geography[]

Aggregates the selected values into an array.

array_agg(arg1: geometry) → geometry[]

Aggregates the selected values into an array.

array_agg(arg1: oid) → oid[]

Aggregates the selected values into an array.

array_agg(arg1: timetz) → timetz[]

Aggregates the selected values into an array.

array_agg(arg1: varbit) → varbit[]

Aggregates the selected values into an array.

avg(arg1: decimal) → decimal

Calculates the average of the selected values.

avg(arg1: float) → float

Calculates the average of the selected values.

avg(arg1: int) → decimal

Calculates the average of the selected values.

avg(arg1: interval) → interval

Calculates the average of the selected values.

bit_and(arg1: int) → int

Calculates the bitwise AND of all non-null input values, or null if none.

bit_and(arg1: varbit) → varbit

Calculates the bitwise AND of all non-null input values, or null if none.

bit_or(arg1: int) → int

Calculates the bitwise OR of all non-null input values, or null if none.

bit_or(arg1: varbit) → varbit

Calculates the bitwise OR of all non-null input values, or null if none.

bool_and(arg1: bool) → bool

Calculates the boolean value of ANDing all selected values.

bool_or(arg1: bool) → bool

Calculates the boolean value of ORing all selected values.

concat_agg(arg1: bytes) → bytes

Concatenates all selected values.

concat_agg(arg1: string) → string

Concatenates all selected values.

corr(arg1: float, arg2: float) → float

Calculates the correlation coefficient of the selected values.

corr(arg1: float, arg2: int) → float

Calculates the correlation coefficient of the selected values.

corr(arg1: int, arg2: float) → float

Calculates the correlation coefficient of the selected values.

corr(arg1: int, arg2: int) → float

Calculates the correlation coefficient of the selected values.

count(arg1: anyelement) → int

Calculates the number of selected elements.

count_rows() → int

Calculates the number of rows.

every(arg1: bool) → bool

Calculates the boolean value of ANDing all selected values.

json_agg(arg1: anyelement) → jsonb

Aggregates values as a JSON or JSONB array.

json_object_agg(arg1: string, arg2: anyelement) → jsonb

Aggregates values as a JSON or JSONB object.

jsonb_agg(arg1: anyelement) → jsonb

Aggregates values as a JSON or JSONB array.

jsonb_object_agg(arg1: string, arg2: anyelement) → jsonb

Aggregates values as a JSON or JSONB object.

max(arg1: bool) → bool

Identifies the maximum selected value.

max(arg1: bytes) → bytes

Identifies the maximum selected value.

max(arg1: date) → date

Identifies the maximum selected value.

max(arg1: decimal) → decimal

Identifies the maximum selected value.

max(arg1: float) → float

Identifies the maximum selected value.

max(arg1: inet) → inet

Identifies the maximum selected value.

max(arg1: int) → int

Identifies the maximum selected value.

max(arg1: interval) → interval

Identifies the maximum selected value.

max(arg1: string) → string

Identifies the maximum selected value.

max(arg1: time) → time

Identifies the maximum selected value.

max(arg1: timestamp) → timestamp

Identifies the maximum selected value.

max(arg1: timestamptz) → timestamptz

Identifies the maximum selected value.

max(arg1: uuid) → uuid

Identifies the maximum selected value.

max(arg1: anyenum) → anyelement

Identifies the maximum selected value.

max(arg1: box2d) → box2d

Identifies the maximum selected value.

max(arg1: collatedstring{*}) → anyelement

Identifies the maximum selected value.

max(arg1: geography) → geography

Identifies the maximum selected value.

max(arg1: geometry) → geometry

Identifies the maximum selected value.

max(arg1: jsonb) → jsonb

Identifies the maximum selected value.

max(arg1: oid) → oid

Identifies the maximum selected value.

max(arg1: timetz) → timetz

Identifies the maximum selected value.

max(arg1: varbit) → varbit

Identifies the maximum selected value.

min(arg1: bool) → bool

Identifies the minimum selected value.

min(arg1: bytes) → bytes

Identifies the minimum selected value.

min(arg1: date) → date

Identifies the minimum selected value.

min(arg1: decimal) → decimal

Identifies the minimum selected value.

min(arg1: float) → float

Identifies the minimum selected value.

min(arg1: inet) → inet

Identifies the minimum selected value.

min(arg1: int) → int

Identifies the minimum selected value.

min(arg1: interval) → interval

Identifies the minimum selected value.

min(arg1: string) → string

Identifies the minimum selected value.

min(arg1: time) → time

Identifies the minimum selected value.

min(arg1: timestamp) → timestamp

Identifies the minimum selected value.

min(arg1: timestamptz) → timestamptz

Identifies the minimum selected value.

min(arg1: uuid) → uuid

Identifies the minimum selected value.

min(arg1: anyenum) → anyelement

Identifies the minimum selected value.

min(arg1: box2d) → box2d

Identifies the minimum selected value.

min(arg1: collatedstring{*}) → anyelement

Identifies the minimum selected value.

min(arg1: geography) → geography

Identifies the minimum selected value.

min(arg1: geometry) → geometry

Identifies the minimum selected value.

min(arg1: jsonb) → jsonb

Identifies the minimum selected value.

min(arg1: oid) → oid

Identifies the minimum selected value.

min(arg1: timetz) → timetz

Identifies the minimum selected value.

min(arg1: varbit) → varbit

Identifies the minimum selected value.

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.

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.

percentile_cont(arg1: float[]) → float[]

Continuous percentile: returns floats corresponding to the specified fractions in the ordering, interpolating between adjacent input floats if needed.

percentile_cont(arg1: float[]) → interval[]

Continuous percentile: returns intervals corresponding to the specified fractions in the ordering, interpolating between adjacent input intervals if needed.

percentile_disc(arg1: float) → anyelement

Discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction.

percentile_disc(arg1: float[]) → anyelement

Discrete percentile: returns input values whose position in the ordering equals or exceeds the specified fractions.

sqrdiff(arg1: decimal) → decimal

Calculates the sum of squared differences from the mean of the selected values.

sqrdiff(arg1: float) → float

Calculates the sum of squared differences from the mean of the selected values.

sqrdiff(arg1: int) → decimal

Calculates the sum of squared differences from the mean of the selected values.

st_collect(arg1: geometry) → geometry

Collects geometries into a GeometryCollection or multi-type as appropriate.

st_extent(arg1: geometry) → box2d

Forms a Box2D that encapsulates all provided geometries.

st_makeline(arg1: geometry) → geometry

Forms a LineString from Point, MultiPoint or LineStrings. Other shapes will be ignored.

st_memcollect(arg1: geometry) → geometry

Collects geometries into a GeometryCollection or multi-type as appropriate.

st_memunion(arg1: geometry) → geometry

Applies a spatial union to the geometries provided.

st_union(arg1: geometry) → geometry

Applies a spatial union to the geometries provided.

stddev(arg1: decimal) → decimal

Calculates the standard deviation of the selected values.

stddev(arg1: float) → float

Calculates the standard deviation of the selected values.

stddev(arg1: int) → decimal

Calculates the standard deviation of the selected values.

stddev_pop(arg1: decimal) → decimal

Calculates the population standard deviation of the selected values.

stddev_pop(arg1: float) → float

Calculates the population standard deviation of the selected values.

stddev_pop(arg1: int) → decimal

Calculates the population standard deviation of the selected values.

stddev_samp(arg1: decimal) → decimal

Calculates the standard deviation of the selected values.

stddev_samp(arg1: float) → float

Calculates the standard deviation of the selected values.

stddev_samp(arg1: int) → decimal

Calculates the standard deviation of the selected values.

string_agg(arg1: bytes, arg2: bytes) → bytes

Concatenates all selected values using the provided delimiter.

string_agg(arg1: string, arg2: string) → string

Concatenates all selected values using the provided delimiter.

sum(arg1: decimal) → decimal

Calculates the sum of the selected values.

sum(arg1: float) → float

Calculates the sum of the selected values.

sum(arg1: int) → decimal

Calculates the sum of the selected values.

sum(arg1: interval) → interval

Calculates the sum of the selected values.

sum_int(arg1: int) → int

Calculates the sum of the selected values.

var_pop(arg1: decimal) → decimal

Calculates the population variance of the selected values.

var_pop(arg1: float) → float

Calculates the population variance of the selected values.

var_pop(arg1: int) → decimal

Calculates the population variance of the selected values.

var_samp(arg1: decimal) → decimal

Calculates the variance of the selected values.

var_samp(arg1: float) → float

Calculates the variance of the selected values.

var_samp(arg1: int) → decimal

Calculates the variance of the selected values.

variance(arg1: decimal) → decimal

Calculates the variance of the selected values.

variance(arg1: float) → float

Calculates the variance of the selected values.

variance(arg1: int) → decimal

Calculates the variance of the selected values.

xor_agg(arg1: bytes) → bytes

Calculates the bitwise XOR of the selected values.

xor_agg(arg1: int) → int

Calculates the bitwise XOR of the selected values.

Window functions

Function → ReturnsDescription
cume_dist() → float

Calculates the relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).

dense_rank() → int

Calculates the rank of the current row without gaps; this function counts peer groups.

first_value(val: bool) → bool

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: bytes) → bytes

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: date) → date

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: decimal) → decimal

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: float) → float

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: inet) → inet

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: int) → int

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: interval) → interval

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: string) → string

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: time) → time

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: timestamp) → timestamp

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: timestamptz) → timestamptz

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: uuid) → uuid

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: box2d) → box2d

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: geography) → geography

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: geometry) → geometry

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: jsonb) → jsonb

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: oid) → oid

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: timetz) → timetz

Returns val evaluated at the row that is the first row of the window frame.

first_value(val: varbit) → varbit

Returns val evaluated at the row that is the first row of the window frame.

lag(val: bool) → bool

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: bool, n: int) → bool

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: bool, n: int, default: bool) → bool

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: bytes) → bytes

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: bytes, n: int) → bytes

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: bytes, n: int, default: bytes) → bytes

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: date) → date

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: date, n: int) → date

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: date, n: int, default: date) → date

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: decimal) → decimal

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: decimal, n: int) → decimal

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: decimal, n: int, default: decimal) → decimal

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: float) → float

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: float, n: int) → float

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: float, n: int, default: float) → float

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: inet) → inet

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: inet, n: int) → inet

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: inet, n: int, default: inet) → inet

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: int) → int

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: int, n: int) → int

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: int, n: int, default: int) → int

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: interval) → interval

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: interval, n: int) → interval

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: interval, n: int, default: interval) → interval

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: string) → string

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: string, n: int) → string

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: string, n: int, default: string) → string

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: time) → time

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: time, n: int) → time

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: time, n: int, default: time) → time

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: timestamp) → timestamp

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: timestamp, n: int) → timestamp

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: timestamp, n: int, default: timestamp) → timestamp

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: timestamptz) → timestamptz

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: timestamptz, n: int) → timestamptz

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: timestamptz, n: int, default: timestamptz) → timestamptz

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: uuid) → uuid

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: uuid, n: int) → uuid

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: uuid, n: int, default: uuid) → uuid

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: box2d) → box2d

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: box2d, n: int) → box2d

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: box2d, n: int, default: box2d) → box2d

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: geography) → geography

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: geography, n: int) → geography

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: geography, n: int, default: geography) → geography

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: geometry) → geometry

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: geometry, n: int) → geometry

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: geometry, n: int, default: geometry) → geometry

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: jsonb) → jsonb

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: jsonb, n: int) → jsonb

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: jsonb, n: int, default: jsonb) → jsonb

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: oid) → oid

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: oid, n: int) → oid

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: oid, n: int, default: oid) → oid

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: timetz) → timetz

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: timetz, n: int) → timetz

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: timetz, n: int, default: timetz) → timetz

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lag(val: varbit) → varbit

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

lag(val: varbit, n: int) → varbit

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lag(val: varbit, n: int, default: varbit) → varbit

Returns val evaluated at the row that is n rows before the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

last_value(val: bool) → bool

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: bytes) → bytes

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: date) → date

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: decimal) → decimal

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: float) → float

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: inet) → inet

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: int) → int

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: interval) → interval

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: string) → string

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: time) → time

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: timestamp) → timestamp

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: timestamptz) → timestamptz

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: uuid) → uuid

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: box2d) → box2d

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: geography) → geography

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: geometry) → geometry

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: jsonb) → jsonb

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: oid) → oid

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: timetz) → timetz

Returns val evaluated at the row that is the last row of the window frame.

last_value(val: varbit) → varbit

Returns val evaluated at the row that is the last row of the window frame.

lead(val: bool) → bool

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: bool, n: int) → bool

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: bool, n: int, default: bool) → bool

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: bytes) → bytes

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: bytes, n: int) → bytes

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: bytes, n: int, default: bytes) → bytes

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: date) → date

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: date, n: int) → date

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: date, n: int, default: date) → date

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: decimal) → decimal

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: decimal, n: int) → decimal

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: decimal, n: int, default: decimal) → decimal

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: float) → float

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: float, n: int) → float

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: float, n: int, default: float) → float

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: inet) → inet

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: inet, n: int) → inet

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: inet, n: int, default: inet) → inet

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: int) → int

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: int, n: int) → int

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: int, n: int, default: int) → int

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: interval) → interval

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: interval, n: int) → interval

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: interval, n: int, default: interval) → interval

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: string) → string

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: string, n: int) → string

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: string, n: int, default: string) → string

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: time) → time

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: time, n: int) → time

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: time, n: int, default: time) → time

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: timestamp) → timestamp

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: timestamp, n: int) → timestamp

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: timestamp, n: int, default: timestamp) → timestamp

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: timestamptz) → timestamptz

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: timestamptz, n: int) → timestamptz

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: timestamptz, n: int, default: timestamptz) → timestamptz

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: uuid) → uuid

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: uuid, n: int) → uuid

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: uuid, n: int, default: uuid) → uuid

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: box2d) → box2d

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: box2d, n: int) → box2d

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: box2d, n: int, default: box2d) → box2d

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: geography) → geography

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: geography, n: int) → geography

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: geography, n: int, default: geography) → geography

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: geometry) → geometry

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: geometry, n: int) → geometry

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: geometry, n: int, default: geometry) → geometry

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: jsonb) → jsonb

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: jsonb, n: int) → jsonb

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: jsonb, n: int, default: jsonb) → jsonb

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: oid) → oid

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: oid, n: int) → oid

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: oid, n: int, default: oid) → oid

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: timetz) → timetz

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: timetz, n: int) → timetz

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: timetz, n: int, default: timetz) → timetz

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

lead(val: varbit) → varbit

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

lead(val: varbit, n: int) → varbit

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such row, instead returns null. n is evaluated with respect to the current row.

lead(val: varbit, n: int, default: varbit) → varbit

Returns val evaluated at the row that is n rows after the current row within its partition; if there is no such, row, instead returns default (which must be of the same type as val). Both n and default are evaluated with respect to the current row.

nth_value(val: bool, n: int) → bool

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: bytes, n: int) → bytes

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: date, n: int) → date

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: decimal, n: int) → decimal

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: float, n: int) → float

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: inet, n: int) → inet

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: int, n: int) → int

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: interval, n: int) → interval

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: string, n: int) → string

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: time, n: int) → time

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: timestamp, n: int) → timestamp

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: timestamptz, n: int) → timestamptz

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: uuid, n: int) → uuid

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: box2d, n: int) → box2d

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: geography, n: int) → geography

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: geometry, n: int) → geometry

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: jsonb, n: int) → jsonb

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: oid, n: int) → oid

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: timetz, n: int) → timetz

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

nth_value(val: varbit, n: int) → varbit

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

ntile(n: int) → int

Calculates an integer ranging from 1 to n, dividing the partition as equally as possible.

percent_rank() → float

Calculates the relative rank of the current row: (rank - 1) / (total rows - 1).

rank() → int

Calculates the rank of the current row with gaps; same as row_number of its first peer.

row_number() → int

Calculates the number of the current row within its partition, counting from 1.

Operators

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

Order of Precedence Operator Name Operator Arity
1 . Member field access operator binary
2 :: Type cast binary
3 - Unary minus unary (prefix)
~ Bitwise not unary (prefix)
4 ^ Exponentiation binary
5 * Multiplication binary
/ Division binary
// Floor division binary
% Modulo binary
6 + Addition binary
- Subtraction binary
7 << Bitwise left-shift binary
>> Bitwise right-shift binary
8 & Bitwise AND binary
9 # Bitwise XOR binary
10 | Bitwise OR binary
11 || Concatenation binary
< ANY, SOME, ALL Multi-valued "less than" comparison binary
> ANY, SOME, ALL Multi-valued "greater than" comparison binary
= ANY, SOME, ALL Multi-valued "equal" comparison binary
<= ANY, SOME, ALL Multi-valued "less than or equal" comparison binary
>= ANY, SOME, ALL Multi-valued "greater than or equal" comparison binary
<> ANY / != ANY, <> SOME / != SOME, <> ALL / != ALL Multi-valued "not equal" comparison binary
[NOT] LIKE ANY, [NOT] LIKE SOME, [NOT] LIKE ALL Multi-valued LIKE comparison binary
[NOT] ILIKE ANY, [NOT] ILIKE SOME, [NOT] ILIKE ALL Multi-valued ILIKE comparison binary
12 [NOT] BETWEEN Value is [not] within the range specified binary
[NOT] BETWEEN SYMMETRIC Like [NOT] BETWEEN, but in non-sorted order. For example, whereas a BETWEEN b AND c means b <= a <= c, a BETWEEN SYMMETRIC b AND c means (b <= a <= c) OR (c <= a <= b). binary
[NOT] IN Value is [not] in the set of values specified binary
[NOT] LIKE Matches [or not] LIKE expression, case sensitive binary
[NOT] ILIKE Matches [or not] LIKE expression, case insensitive binary
[NOT] SIMILAR Matches [or not] SIMILAR TO regular expression binary
~ Matches regular expression, case sensitive binary
!~ Does not match regular expression, case sensitive binary
~* Matches regular expression, case insensitive binary
!~* Does not match regular expression, case insensitive binary
13 = Equal binary
< Less than binary
> Greater than binary
<= Less than or equal to binary
>= Greater than or equal to binary
!=, <> Not equal binary
14 IS [DISTINCT FROM] Equal, considering NULL as value binary
IS NOT [DISTINCT FROM] a IS NOT b equivalent to NOT (a IS b) binary
ISNULL, IS UNKNOWN , NOTNULL, IS NOT UNKNOWN Equivalent to IS NULL / IS NOT NULL unary (postfix)
IS NAN, IS NOT NAN Comparison with the floating-point NaN value unary (postfix)
IS OF(...) Type predicate unary (postfix)
15 NOT Logical NOT unary
16 AND Logical AND binary
17 OR Logical OR binary

Supported operations

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


Yes No