Functions and Operators

On this page Carat arrow pointing down

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

Function volatility

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

Type Description Examples
Volatile The function can modify the state of the database and is not guaranteed to return the same results given the same arguments in any context. random, crdb_internal.force_error, nextval, now
Stable The function is guaranteed to return the same results given the same arguments whenever it is evaluated within the same statement. The optimizer can optimize multiple calls of the function to a single call. current_timestamp, current_date
Immutable The function does not depend on configuration settings and is guaranteed to return the same results given the same arguments in any context. The optimizer can pre-evaluate the function when a query calls it with constant arguments. log, from_json
Leakproof The function does not depend on configuration settings and is guaranteed to return the same results given the same arguments in any context. In addition, no information about the arguments is conveyed except via the return value. Any function that might throw an error depending on the values of its arguments is not leakproof. Leakproof is strictly stronger than Immutable. Integer comparison

Conditional and function-like operators

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

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

User-defined functions

In addition to the built-in functions described in the following sections, CockroachDB supports user-defined functions. For details, see User-Defined Functions.

Built-in functions

Array functions

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

Immutable
array_append(array: pg_lsn[], elem: pg_lsn) → pg_lsn[]

Appends elem to array, returning the result.

Immutable
array_append(array: refcursor[], elem: refcursor) → refcursor[]

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends elem to array, returning the result.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

Immutable
array_cat(left: anyenum[], right: anyenum[]) → anyenum[]

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

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

Appends two arrays.

Immutable
array_cat(left: jsonb[], right: jsonb[]) → jsonb[]

Appends two arrays.

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

Appends two arrays.

Immutable
array_cat(left: pg_lsn[], right: pg_lsn[]) → pg_lsn[]

Appends two arrays.

Immutable
array_cat(left: refcursor[], right: refcursor[]) → refcursor[]

Appends two arrays.

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

Appends two arrays.

Immutable
array_cat(left: tuple[], right: tuple[]) → tuple[]

Appends two arrays.

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

Appends two arrays.

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

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

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

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

Immutable
array_position(array: anyenum[], elem: anyenum, start: int) → int

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

Immutable
array_position(array: jsonb[], elem: jsonb, start: int) → int

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

Immutable
array_position(array: pg_lsn[], elem: pg_lsn, start: int) → int

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

Immutable
array_position(array: refcursor[], elem: refcursor, start: int) → int

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

Immutable
array_position(array: tuple[], elem: tuple, start: int) → int

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

Return the index of the first occurrence of elem in array, with the search begins at start index.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

Immutable
array_prepend(elem: pg_lsn, array: pg_lsn[]) → pg_lsn[]

Prepends elem to array, returning the result.

Immutable
array_prepend(elem: refcursor, array: refcursor[]) → refcursor[]

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Prepends elem to array, returning the result.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

Immutable
array_remove(array: anyenum[], elem: anyenum) → anyenum[]

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

Immutable
array_remove(array: jsonb[], elem: jsonb) → jsonb[]

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

Immutable
array_remove(array: pg_lsn[], elem: pg_lsn) → pg_lsn[]

Remove from array all elements equal to elem.

Immutable
array_remove(array: refcursor[], elem: refcursor) → refcursor[]

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

Immutable
array_remove(array: tuple[], elem: tuple) → tuple[]

Remove from array all elements equal to elem.

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

Remove from array all elements equal to elem.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

Immutable
array_replace(array: anyenum[], toreplace: anyenum, replacewith: anyenum) → anyenum[]

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

Immutable
array_replace(array: jsonb[], toreplace: jsonb, replacewith: jsonb) → jsonb[]

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

Immutable
array_replace(array: pg_lsn[], toreplace: pg_lsn, replacewith: pg_lsn) → pg_lsn[]

Replace all occurrences of toreplace in array with replacewith.

Immutable
array_replace(array: refcursor[], toreplace: refcursor, replacewith: refcursor) → refcursor[]

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

Immutable
array_replace(array: tuple[], toreplace: tuple, replacewith: tuple) → tuple[]

Replace all occurrences of toreplace in array with replacewith.

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

Replace all occurrences of toreplace in array with replacewith.

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

Join an array into a string with a delimiter.

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

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

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

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

Immutable
cardinality(input: anyelement[]) → int

Calculates the number of elements contained in input

Immutable
jsonb_array_to_string_array(input: jsonb) → string[]

Convert a JSONB array into a string array.

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

Split a string into components on a delimiter.

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

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

Immutable

BOOL functions

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

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

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

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

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

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

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

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

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

Matches unescaped with pattern using escape as an escape token.

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

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

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

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

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

Immutable

Comparison functions

Function → ReturnsDescriptionVolatility
greatest(anyelement...) → anyelement

Returns the element with the greatest value.

Immutable
least(anyelement...) → anyelement

Returns the element with the lowest value.

Immutable
num_nonnulls(anyelement...) → int

Returns the number of nonnull arguments.

Immutable
num_nulls(anyelement...) → int

Returns the number of null arguments.

Immutable

Cryptographic functions

Function → ReturnsDescriptionVolatility
crypt(password: string, salt: string) → string

Generates a hash based on a password and salt. The hash algorithm and number of rounds if applicable are encoded in the salt.

Immutable
decrypt(data: bytes, key: bytes, type: string) → bytes

Decrypt data with key using the cipher method specified by type.

The cipher type must have the format <algorithm>[-<mode>][/pad:<padding>] where:

  • <algorithm> is aes
  • <mode> is cbc (default)
  • <padding> is pkcs (default) or none

This function requires an enterprise license on a CCL distribution.

Immutable
decrypt_iv(data: bytes, key: bytes, iv: bytes, type: string) → bytes

Decrypt data with key using the cipher method specified by type. If the mode is CBC, the provided iv will be used. Otherwise, it will be ignored.

The cipher type must have the format <algorithm>[-<mode>][/pad:<padding>] where:

  • <algorithm> is aes
  • <mode> is cbc (default)
  • <padding> is pkcs (default) or none

This function requires an enterprise license on a CCL distribution.

Immutable
digest(data: bytes, type: string) → bytes

Computes a binary hash of the given data. type is the algorithm to use (md5, sha1, sha224, sha256, sha384, or sha512).

Immutable
digest(data: string, type: string) → bytes

Computes a binary hash of the given data. type is the algorithm to use (md5, sha1, sha224, sha256, sha384, or sha512).

Immutable
encrypt(data: bytes, key: bytes, type: string) → bytes

Encrypt data with key using the cipher method specified by type.

The cipher type must have the format <algorithm>[-<mode>][/pad:<padding>] where:

  • <algorithm> is aes
  • <mode> is cbc (default)
  • <padding> is pkcs (default) or none

This function requires an enterprise license on a CCL distribution.

Immutable
encrypt_iv(data: bytes, key: bytes, iv: bytes, type: string) → bytes

Encrypt data with key using the cipher method specified by type. If the mode is CBC, the provided iv will be used. Otherwise, it will be ignored.

The cipher type must have the format <algorithm>[-<mode>][/pad:<padding>] where:

  • <algorithm> is aes
  • <mode> is cbc (default)
  • <padding> is pkcs (default) or none

This function requires an enterprise license on a CCL distribution.

Immutable
gen_random_bytes(count: int) → bytes

Returns count cryptographically strong random bytes. At most 1024 bytes can be extracted at a time.

Volatile
gen_salt(type: string) → string

Generates a salt for input into the crypt function using the default number of rounds.

Volatile
gen_salt(type: string, iter_count: int) → string

Generates a salt for input into the crypt function using iter_count number of rounds.

Volatile
hmac(data: bytes, key: bytes, type: string) → bytes

Calculates hashed MAC for data with key key. type is the same as in digest().

Immutable
hmac(data: string, key: string, type: string) → bytes

Calculates hashed MAC for data with key key. type is the same as in digest().

Immutable

DECIMAL functions

Function → ReturnsDescriptionVolatility
hlc_to_timestamp(hlc: decimal) → timestamptz

Returns a TimestampTZ representation of a CockroachDB HLC in decimal form.

Note that a TimestampTZ has less precision than a CockroachDB HLC. It is intended as a convenience function to display HLCs in a print-friendly form. Use the decimal value if you rely on the HLC for accuracy.

Immutable

Date and time functions

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

Calculates the interval between begin and end, normalized into years, months and days.

Note this may not be an accurate time span since years and months are normalized from days, and years and months are out of context. To avoid normalizing days into months and years, use the timestamptz subtraction operator.

Immutable
age(val: timestamptz) → interval

Calculates the interval between val and the current time, normalized into years, months and days.

Note this may not be an accurate time span since years and months are normalized from days, and years and months are out of context. To avoid normalizing days into months and years, use now() - timestamptz.

Stable
clock_timestamp() → timestamp

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

Volatile
clock_timestamp() → timestamptz

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

Volatile
current_date() → date

Returns the date of the current transaction.

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

Stable
current_timestamp() → date

Returns the time of the current transaction.

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

Stable
current_timestamp() → timestamp

Returns the time of the current transaction.

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

Stable
current_timestamp() → timestamptz

Returns the time of the current transaction.

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

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

Stable
current_timestamp(precision: int) → date

Returns the time of the current transaction.

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

Stable
current_timestamp(precision: int) → timestamp

Returns the time of the current transaction.

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

Stable
current_timestamp(precision: int) → timestamptz

Returns the time of the current transaction.

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

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

Stable
date_part(element: string, input: date) → float

Extracts element from input.

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

Immutable
date_part(element: string, input: interval) → float

Extracts element from input.

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

Immutable
date_part(element: string, input: time) → float

Extracts element from input.

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

Immutable
date_part(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

Immutable
date_part(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

Stable
date_part(element: string, input: timetz) → float

Extracts element from input.

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

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

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

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.

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

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

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

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

Truncates input to precision element in the specified timezone. 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.

Stable
experimental_follower_read_timestamp() → timestamptz

Same as follower_read_timestamp. This name is deprecated.

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

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

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

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

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

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

Immutable
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

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

Extracts element from input.

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

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

Extracts element from input.

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

Immutable
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

Immutable
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

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

Extracts element from input.

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

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

Immutable
follower_read_timestamp() → timestamptz

Returns a timestamp which is very likely to be safe to perform against a follower replica.

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

Note that this function requires an enterprise license on a CCL distribution to return a result that is less likely the closest replica. It is otherwise hardcoded as -4.8s from the statement time, which may not result in reading from the nearest replica.

Volatile
localtimestamp() → date

Returns the time of the current transaction.

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

Stable
localtimestamp() → timestamp

Returns the time of the current transaction.

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

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

Stable
localtimestamp() → timestamptz

Returns the time of the current transaction.

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

Stable
localtimestamp(precision: int) → date

Returns the time of the current transaction.

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

Stable
localtimestamp(precision: int) → timestamp

Returns the time of the current transaction.

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

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

Stable
localtimestamp(precision: int) → timestamptz

Returns the time of the current transaction.

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

Stable
make_date(year: int, month: int, day: int) → date

Create date (formatted according to ISO 8601) from year, month, and day fields (negative years signify BC).

Immutable
make_timestamp(year: int, month: int, day: int, hour: int, min: int, sec: float) → timestamp

Create timestamp (formatted according to ISO 8601) from year, month, day, hour, minute, and seconds fields (negative years signify BC).

Immutable
make_timestamptz(year: int, month: int, day: int, hour: int, min: int, sec: float) → timestamptz

Create timestamp (formatted according to ISO 8601) with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If timezone is not specified, the current time zone is used.

Stable
make_timestamptz(year: int, month: int, day: int, hour: int, min: int, sec: float, timezone: string) → timestamptz

Create timestamp (formatted according to ISO 8601) with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If timezone is not specified, the current time zone is used.

Stable
now() → date

Returns the time of the current transaction.

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

Stable
now() → timestamp

Returns the time of the current transaction.

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

Stable
now() → timestamptz

Returns the time of the current transaction.

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

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

Stable
overlaps(s1: date, e1: date, s1: date, e2: date) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: date, e1: interval, s1: date, e2: interval) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: time, e1: interval, s1: time, e2: interval) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: time, e1: time, s1: time, e2: time) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: timestamp, e1: interval, s1: timestamp, e2: interval) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: timestamp, e1: timestamp, s1: timestamp, e2: timestamp) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: timestamptz, e1: interval, s1: timestamptz, e2: interval) → bool

Returns if two time periods (defined by their endpoints) overlap.

Stable
overlaps(s1: timestamptz, e1: timestamptz, s1: timestamptz, e2: timestamptz) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: timetz, e1: interval, s1: timetz, e2: interval) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
overlaps(s1: timetz, e1: timetz, s1: timetz, e2: timetz) → bool

Returns if two time periods (defined by their endpoints) overlap.

Immutable
statement_timestamp() → timestamp

Returns the start time of the current statement.

Stable
statement_timestamp() → timestamptz

Returns the start time of the current statement.

Stable
strftime(input: date, extract_format: string) → string

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

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

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

Immutable
strptime(input: string, format: string) → timestamptz

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

Immutable
timeofday() → string

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

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

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

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

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

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

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

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

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

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

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

Stable
to_char(date: date) → string

Convert an date to a string assuming the ISO, MDY DateStyle.

Immutable
to_char(date: date, format: string) → string

Convert a timestamp with time zone to a string using the given format.

Stable
to_char(interval: interval) → string

Convert an interval to a string assuming the Postgres IntervalStyle.

Immutable
to_char(interval: interval, format: string) → string

Convert an interval to a string using the given format.

Stable
to_char(timestamp: timestamp) → string

Convert an timestamp to a string assuming the ISO, MDY DateStyle.

Immutable
to_char(timestamp: timestamp, format: string) → string

Convert an timestamp to a string using the given format.

Stable
to_char(timestamptz: timestamptz, format: string) → string

Convert a timestamp with time zone to a string using the given format.

Stable
to_timestamp(timestamp: float) → timestamptz

Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone.

Immutable
transaction_timestamp() → date

Returns the time of the current transaction.

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

Stable
transaction_timestamp() → timestamp

Returns the time of the current transaction.

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

Stable
transaction_timestamp() → timestamptz

Returns the time of the current transaction.

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

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

Stable
with_max_staleness(max_staleness: interval) → timestamptz

When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp within the staleness bound that allows execution of the reads at the nearest available replica without blocking.

Note this function requires an enterprise license on a CCL distribution.

Volatile
with_max_staleness(max_staleness: interval, nearest_only: bool) → timestamptz

When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp within the staleness bound that allows execution of the reads at the nearest available replica without blocking.

If nearest_only is set to true, reads that cannot be served using the nearest available replica will error.

Note this function requires an enterprise license on a CCL distribution.

Volatile
with_min_timestamp(min_timestamp: timestamptz) → timestamptz

When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp before the min_timestamp that allows execution of the reads at the nearest available replica without blocking.

Note this function requires an enterprise license on a CCL distribution.

Volatile
with_min_timestamp(min_timestamp: timestamptz, nearest_only: bool) → timestamptz

When used in the AS OF SYSTEM TIME clause of an single-statement, read-only transaction, CockroachDB chooses the newest timestamp before the min_timestamp that allows execution of the reads at the nearest available replica without blocking.

If nearest_only is set to true, reads that cannot be served using the nearest available replica will error.

Note this function requires an enterprise license on a CCL distribution.

Volatile

Enum functions

Function → ReturnsDescriptionVolatility
enum_first(val: anyenum) → anyenum

Returns the first value of the input enum type.

Stable
enum_last(val: anyenum) → anyenum

Returns the last value of the input enum type.

Stable
enum_range(lower: anyenum, upper: anyenum) → anyenum[]

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

Stable
enum_range(val: anyenum) → anyenum[]

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

Stable

FLOAT functions

Function → ReturnsDescriptionVolatility
abs(val: decimal) → decimal

Calculates the absolute value of val.

Immutable
abs(val: float) → float

Calculates the absolute value of val.

Immutable
abs(val: int) → int

Calculates the absolute value of val.

Immutable
acos(val: float) → float

Calculates the inverse cosine of val.

Immutable
acosd(val: float) → float

Calculates the inverse cosine of val with the result in degrees

Immutable
acosh(val: float) → float

Calculates the inverse hyperbolic cosine of val.

Immutable
asin(val: float) → float

Calculates the inverse sine of val.

Immutable
asind(val: float) → float

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

Immutable
asinh(val: float) → float

Calculates the inverse hyperbolic sine of val.

Immutable
atan(val: float) → float

Calculates the inverse tangent of val.

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

Calculates the inverse tangent of x/y.

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

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

Immutable
atand(val: float) → float

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

Immutable
atanh(val: float) → float

Calculates the inverse hyperbolic tangent of val.

Immutable
cbrt(val: decimal) → decimal

Calculates the cube root (∛) of val.

Immutable
cbrt(val: float) → float

Calculates the cube root (∛) of val.

Immutable
ceil(val: decimal) → decimal

Calculates the smallest integer not smaller than val.

Immutable
ceil(val: float) → float

Calculates the smallest integer not smaller than val.

Immutable
ceil(val: int) → float

Calculates the smallest integer not smaller than val.

Immutable
ceiling(val: decimal) → decimal

Calculates the smallest integer not smaller than val.

Immutable
ceiling(val: float) → float

Calculates the smallest integer not smaller than val.

Immutable
ceiling(val: int) → float

Calculates the smallest integer not smaller than val.

Immutable
cos(val: float) → float

Calculates the cosine of val.

Immutable
cosd(val: float) → float

Calculates the cosine of val where val is in degrees.

Immutable
cosh(val: float) → float

Calculates the hyperbolic cosine of val.

Immutable
cot(val: float) → float

Calculates the cotangent of val.

Immutable
cotd(val: float) → float

Calculates the cotangent of val where val is in degrees.

Immutable
degrees(val: float) → float

Converts val as a radian value to a degree value.

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

Calculates the integer quotient of x/y.

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

Calculates the integer quotient of x/y.

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

Calculates the integer quotient of x/y.

Immutable
exp(val: decimal) → decimal

Calculates e ^ val.

Immutable
exp(val: float) → float

Calculates e ^ val.

Immutable
floor(val: decimal) → decimal

Calculates the largest integer not greater than val.

Immutable
floor(val: float) → float

Calculates the largest integer not greater than val.

Immutable
floor(val: int) → float

Calculates the largest integer not greater than val.

Immutable
isnan(val: decimal) → bool

Returns true if val is NaN, false otherwise.

Immutable
isnan(val: float) → bool

Returns true if val is NaN, false otherwise.

Immutable
ln(val: decimal) → decimal

Calculates the natural log of val.

Immutable
ln(val: float) → float

Calculates the natural log of val.

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

Calculates the base b log of val.

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

Calculates the base b log of val.

Immutable
log(val: decimal) → decimal

Calculates the base 10 log of val.

Immutable
log(val: float) → float

Calculates the base 10 log of val.

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

Calculates x%y.

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

Calculates x%y.

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

Calculates x%y.

Immutable
pi() → float

Returns the value for pi (3.141592653589793).

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

Calculates x^y.

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

Calculates x^y.

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

Calculates x^y.

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

Calculates x^y.

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

Calculates x^y.

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

Calculates x^y.

Immutable
radians(val: float) → float

Converts val as a degree value to a radians value.

Immutable
random() → float

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

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

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

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

Immutable
round(val: decimal) → decimal

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

Immutable
round(val: float) → float

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

Immutable
setseed(seed: float) → void

Sets the seed for subsequent random() calls in this session (value between -1.0 and 1.0, inclusive). There are no guarantees as to how this affects the seed of random() calls that appear in the same query as setseed().

Volatile
sign(val: decimal) → decimal

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

Immutable
sign(val: float) → float

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

Immutable
sign(val: int) → int

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

Immutable
sin(val: float) → float

Calculates the sine of val.

Immutable
sind(val: float) → float

Calculates the sine of val where val is in degrees.

Immutable
sinh(val: float) → float

Calculates the hyperbolic sine of val.

Immutable
sqrt(val: decimal) → decimal

Calculates the square root of val.

Immutable
sqrt(val: float) → float

Calculates the square root of val.

Immutable
tan(val: float) → float

Calculates the tangent of val.

Immutable
tand(val: float) → float

Calculates the tangent of val where val is in degrees.

Immutable
tanh(val: float) → float

Calculates the hyperbolic tangent of val.

Immutable
trunc(val: decimal) → decimal

Truncates the decimal values of val.

Immutable
trunc(val: decimal, scale: int) → decimal

Truncate val to scale decimal places

Immutable
trunc(val: float) → float

Truncates the decimal values of val.

Immutable

Full Text Search functions

Function → ReturnsDescriptionVolatility
phraseto_tsquery(config: string, text: string) → tsquery

Converts text to a tsquery, normalizing words according to the specified configuration. The <-> operator is inserted between each token in the input.

Immutable
phraseto_tsquery(text: string) → tsquery

Converts text to a tsquery, normalizing words according to the default configuration. The <-> operator is inserted between each token in the input.

Stable
plainto_tsquery(config: string, text: string) → tsquery

Converts text to a tsquery, normalizing words according to the specified configuration. The & operator is inserted between each token in the input.

Immutable
plainto_tsquery(text: string) → tsquery

Converts text to a tsquery, normalizing words according to the default configuration. The & operator is inserted between each token in the input.

Stable
to_tsquery(config: string, text: string) → tsquery

Converts the input text into a tsquery by normalizing each word in the input according to the specified configuration. The input must already be formatted like a tsquery, in other words, subsequent tokens must be connected by a tsquery operator (&, |, <->, !).

Immutable
to_tsquery(text: string) → tsquery

Converts the input text into a tsquery by normalizing each word in the input according to the default configuration. The input must already be formatted like a tsquery, in other words, subsequent tokens must be connected by a tsquery operator (&, |, <->, !).

Stable
to_tsvector(config: string, text: string) → tsvector

Converts text to a tsvector, normalizing words according to the specified configuration. Position information is included in the result.

Immutable
to_tsvector(text: string) → tsvector

Converts text to a tsvector, normalizing words according to the default configuration. Position information is included in the result.

Stable
ts_parse(parser_name: string, document: string) → tuple{int AS tokid, string AS token}

ts_parse parses the given document and returns a series of records, one for each token produced by parsing. Each record includes a tokid showing the assigned token type and a token which is the text of the token.

Stable
ts_rank(vector: tsvector, query: tsquery) → float4

Ranks vectors based on the frequency of their matching lexemes.

Immutable
ts_rank(vector: tsvector, query: tsquery, normalization: int) → float4

Ranks vectors based on the frequency of their matching lexemes.

Immutable
ts_rank(weights: float[], vector: tsvector, query: tsquery) → float4

Ranks vectors based on the frequency of their matching lexemes.

Immutable
ts_rank(weights: float[], vector: tsvector, query: tsquery, normalization: int) → float4

Ranks vectors based on the frequency of their matching lexemes.

Immutable

Fuzzy String Matching functions

Function → ReturnsDescriptionVolatility
levenshtein(source: string, target: string) → int

Calculates the Levenshtein distance between two strings. Maximum input length is 255 characters.

Immutable
levenshtein(source: string, target: string, ins_cost: int, del_cost: int, sub_cost: int) → int

Calculates the Levenshtein distance between two strings. The cost parameters specify how much to charge for each edit operation. Maximum input length is 255 characters.

Immutable
metaphone(source: string, max_output_length: int) → string

Convert a string to its Metaphone code. Maximum input length is 255 characters

Immutable
soundex(source: string) → string

Convert a string to its Soundex code.

Immutable

ID generation functions

Function → ReturnsDescriptionVolatility
experimental_uuid_v4() → bytes

Returns a UUID.

Volatile
gen_random_ulid() → uuid

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

Volatile
gen_random_uuid() → uuid

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

Volatile
unique_rowid() → int

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

Volatile
unordered_unique_rowid() → int

Returns a unique ID. The value is a combination of the insert timestamp (bit-reversed) and the ID of the node executing the statement, which guarantees this combination is globally unique. The way it is generated is statistically likely to not have any ordering relative to previously generated values.

Volatile
uuid_generate_v1() → uuid

Generates a version 1 UUID, and returns it as a value of UUID type. To avoid exposing the server’s real MAC address, this uses a random MAC address and a timestamp. Essentially, this is an alias for uuid_generate_v1mc.

Volatile
uuid_generate_v1mc() → uuid

Generates a version 1 UUID, and returns it as a value of UUID type. This uses a random MAC address and a timestamp.

Volatile
uuid_generate_v3(namespace: uuid, name: string) → uuid

Generates a version 3 UUID in the given namespace using the specified input name, with md5 as the hashing method. The namespace should be one of the special constants produced by the uuid_ns_*() functions.

Immutable
uuid_generate_v4() → uuid

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

Volatile
uuid_generate_v5(namespace: uuid, name: string) → uuid

Generates a version 5 UUID in the given namespace using the specified input name. This is similar to a version 3 UUID, except it uses SHA-1 for hashing.

Immutable
uuid_nil() → uuid

Returns a nil UUID constant.

Immutable
uuid_ns_dns() → uuid

Returns a constant designating the DNS namespace for UUIDs.

Immutable
uuid_ns_oid() → uuid

Returns a constant designating the ISO object identifier (OID) namespace for UUIDs. These are unrelated to the OID type used internally in the database.

Immutable
uuid_ns_url() → uuid

Returns a constant designating the URL namespace for UUIDs.

Immutable
uuid_ns_x500() → uuid

Returns a constant designating the X.500 distinguished name (DN) namespace for UUIDs.

Immutable
uuid_v4() → bytes

Returns a UUID.

Volatile

INET functions

Function → ReturnsDescriptionVolatility
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'

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

Immutable
family(val: inet) → int

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

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

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

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

Immutable
masklen(val: inet) → int

Retrieves the prefix length stored in the value.

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

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

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

Immutable

INT functions

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

Calculates the CRC-32 hash using the Castagnoli polynomial.

Leakproof
crc32c(string...) → int

Calculates the CRC-32 hash using the Castagnoli polynomial.

Leakproof
crc32ieee(bytes...) → int

Calculates the CRC-32 hash using the IEEE polynomial.

Leakproof
crc32ieee(string...) → int

Calculates the CRC-32 hash using the IEEE polynomial.

Leakproof
fnv32(bytes...) → int

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

Leakproof
fnv32(string...) → int

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

Leakproof
fnv32a(bytes...) → int

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

Leakproof
fnv32a(string...) → int

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

Leakproof
fnv64(bytes...) → int

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

Leakproof
fnv64(string...) → int

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

Leakproof
fnv64a(bytes...) → int

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

Leakproof
fnv64a(string...) → int

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

Leakproof
width_bucket(operand: decimal, b1: decimal, b2: decimal, count: int) → int

return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2.

Immutable
width_bucket(operand: int, b1: int, b2: int, count: int) → int

return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range b1 to b2.

Immutable
width_bucket(operand: anyelement, thresholds: anyelement[]) → int

return the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained

Immutable

JSONB functions

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

Returns the array as JSON or JSONB.

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

Returns the array as JSON or JSONB.

Stable
json_array_elements(input: jsonb) → jsonb

Expands a JSON array to a set of JSON values.

Immutable
json_array_elements_text(input: jsonb) → string

Expands a JSON array to a set of text values.

Immutable
json_array_length(json: jsonb) → int

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

Immutable
json_build_array(anyelement...) → jsonb

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

Stable
json_build_object(anyelement...) → jsonb

Builds a JSON object out of a variadic argument list.

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

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

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

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

Immutable
json_extract_path(jsonb, string...) → jsonb

Returns the JSON value pointed to by the variadic arguments.

Immutable
json_extract_path_text(jsonb, string...) → string

Returns the JSON value as text pointed to by the variadic arguments.

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

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

Immutable
json_object(texts: string[]) → jsonb

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

Immutable
json_populate_record(base: anyelement, from_json: jsonb) → anyelement

Expands the object in from_json to a row whose columns match the record type defined by base.

Stable
json_populate_recordset(base: anyelement, from_json: jsonb) → anyelement

Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base

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

Remove the specified path from the JSON object.

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

Returns the JSON value pointed to by the variadic arguments.

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

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

Immutable
json_strip_nulls(from_json: jsonb) → jsonb

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

Immutable
json_typeof(val: jsonb) → string

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

Immutable
json_valid(string: string) → bool

Returns whether the given string is a valid JSON or not

Immutable
jsonb_array_elements(input: jsonb) → jsonb

Expands a JSON array to a set of JSON values.

Immutable
jsonb_array_elements_text(input: jsonb) → string

Expands a JSON array to a set of text values.

Immutable
jsonb_array_length(json: jsonb) → int

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

Immutable
jsonb_build_array(anyelement...) → jsonb

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

Stable
jsonb_build_object(anyelement...) → jsonb

Builds a JSON object out of a variadic argument list.

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

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

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

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

Immutable
jsonb_exists_any(json: jsonb, array: string[]) → bool

Returns whether any of the strings in the text array exist as top-level keys or array elements

Immutable
jsonb_extract_path(jsonb, string...) → jsonb

Returns the JSON value pointed to by the variadic arguments.

Immutable
jsonb_extract_path_text(jsonb, string...) → string

Returns the JSON value as text pointed to by the variadic arguments.

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

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

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

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

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

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

Immutable
jsonb_object(texts: string[]) → jsonb

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

Immutable
jsonb_populate_record(base: anyelement, from_json: jsonb) → anyelement

Expands the object in from_json to a row whose columns match the record type defined by base.

Stable
jsonb_populate_recordset(base: anyelement, from_json: jsonb) → anyelement

Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base

Stable
jsonb_pretty(val: jsonb) → string

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

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

Returns the JSON value pointed to by the variadic arguments.

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

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

Immutable
jsonb_strip_nulls(from_json: jsonb) → jsonb

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

Immutable
jsonb_typeof(val: jsonb) → string

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

Immutable
row_to_json(row: tuple) → jsonb

Returns the row as a JSON object.

Stable
to_json(val: anyelement) → jsonb

Returns the value as JSON or JSONB.

Stable
to_jsonb(val: anyelement) → jsonb

Returns the value as JSON or JSONB.

Stable

Multi-region functions

Function → ReturnsDescriptionVolatility
default_to_database_primary_region(val: string) → string

Returns the given region if the region has been added to the current database. Otherwise, this will return the primary region of the current database. This will error if the current database is not a multi-region database.

Stable
gateway_region() → string

Returns the region of the connection’s current node as defined by the locality flag on node startup. Returns an error if no region is set.

Stable
rehome_row() → string

Returns the region of the connection’s current node as defined by the locality flag on node startup. Returns an error if no region is set.

Stable

PGVector functions

Function → ReturnsDescriptionVolatility
cosine_distance(v1: vector, v2: vector) → float

Returns the cosine distance between the two vectors.

Immutable
inner_product(v1: vector, v2: vector) → float

Returns the inner product between the two vectors.

Immutable
l1_distance(v1: vector, v2: vector) → float

Returns the Manhattan distance between the two vectors.

Immutable
l2_distance(v1: vector, v2: vector) → float

Returns the Euclidean distance between the two vectors.

Immutable
vector_dims(vector: vector) → int

Returns the number of the dimensions in the vector.

Immutable
vector_norm(vector: vector) → float

Returns the Euclidean norm of the vector.

Immutable

STRING[] functions

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

Split string using a POSIX regular expression as the delimiter.

Immutable
regexp_split_to_array(string: string, pattern: string, flags: string) → string[]

Split string using a POSIX regular expression as the delimiter with flags.

CockroachDB supports the following flags:

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

Immutable

Sequence functions

Function → ReturnsDescriptionVolatility
currval(sequence_name: string) → int

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

Volatile
currval(sequence_name: regclass) → int

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

Volatile
lastval() → int

Return value most recently obtained with nextval in this session.

Volatile
nextval(sequence_name: string) → int

Advances the given sequence and returns its new value.

Volatile
nextval(sequence_name: regclass) → int

Advances the given sequence and returns its new value.

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

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

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

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

Volatile
setval(sequence_name: regclass, value: int) → int

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

Volatile
setval(sequence_name: regclass, value: int, is_called: bool) → int

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

Volatile

Set-returning functions

Function → ReturnsDescriptionVolatility
aclexplode(aclitems: string[]) → tuple{oid AS grantor, oid AS grantee, string AS privilege_type, bool AS is_grantable}

Produces a virtual table containing aclitem stuff (returns no rows as this feature is unsupported in CockroachDB)

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

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

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

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

Immutable
generate_series(start: timestamptz, end: timestamptz, step: interval) → timestamptz

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

Immutable
generate_subscripts(array: anyelement[]) → int

Returns a series comprising the given array’s subscripts.

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

Returns a series comprising the given array’s subscripts.

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

Returns a series comprising the given array’s subscripts.

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

Immutable
information_schema._pg_expandarray(input: anyelement[]) → tuple{anyelement AS x, int AS n}

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

Immutable
json_object_keys(input: jsonb) → string

Returns sorted set of keys in the outermost JSON object.

Immutable
json_to_record(input: jsonb) → tuple

Builds an arbitrary record from a JSON object.

Stable
json_to_recordset(input: jsonb) → tuple

Builds an arbitrary set of records from a JSON array of objects.

Stable
jsonb_object_keys(input: jsonb) → string

Returns sorted set of keys in the outermost JSON object.

Immutable
jsonb_to_record(input: jsonb) → tuple

Builds an arbitrary record from a JSON object.

Stable
jsonb_to_recordset(input: jsonb) → tuple

Builds an arbitrary set of records from a JSON array of objects.

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

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

Immutable
pg_options_to_table(options: string[]) → tuple{string AS option_name, string AS option_value}

Converts the options array format to a table.

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

Split string using a POSIX regular expression as the delimiter.

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

Split string using a POSIX regular expression as the delimiter with flags.

CockroachDB supports the following flags:

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

Immutable
unnest(anyelement[], anyelement[], anyelement[]...) → tuple{anyelement AS unnest, anyelement AS unnest, anyelement AS unnest}

Returns the input arrays as a set of rows

Immutable
unnest(input: anyelement[]) → anyelement

Returns the input array as a set of rows

Immutable
workload_index_recs() → string

Returns set of index recommendations

Immutable
workload_index_recs(timestamptz: timestamptz) → string

Returns set of index recommendations

Immutable

Spatial functions

Function → ReturnsDescriptionVolatility
_st_contains(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if no points of geometry_b lie in the exterior of geometry_a, and there is at least one point in the interior of geometry_b that lies in the interior of geometry_a.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_containsproperly(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_coveredby(geography_a: geography, geography_b: geography) → bool

Returns true if no point in geography_a is outside geography_b.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function variant does not utilize any spatial index.

Immutable
_st_coveredby(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if no point in geometry_a is outside geometry_b.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_covers(geography_a: geography, geography_b: geography) → bool

Returns true if no point in geography_b is outside geography_a.

This function utilizes the S2 library for spherical calculations.

This function variant does not utilize any spatial index.

Immutable
_st_covers(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if no point in geometry_b is outside geometry_a.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_crosses(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a has some - but not all - interior points in common with geometry_b.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_dfullywithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, inclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than or equal to distance units.

This function variant does not utilize any spatial index.

Immutable
_st_dfullywithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, exclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than distance units.

This function variant does not utilize any spatial index.

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

Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

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

Returns true if any of geography_a is within distance meters of geography_b, inclusive.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

Immutable
_st_dwithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if any of geometry_a is within distance units of geometry_b, inclusive.

This function variant does not utilize any spatial index.

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

Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

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

Returns true if any of geography_a is within distance meters of geography_b, exclusive.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

This function variant does not utilize any spatial index.

Immutable
_st_dwithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if any of geometry_a is within distance units of geometry_b, exclusive.

This function variant does not utilize any spatial index.

Immutable
_st_equals(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a is spatially equal to geometry_b, i.e. ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = true.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_intersects(geography_a: geography, geography_b: geography) → bool

Returns true if geography_a shares any portion of space with geography_b.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function variant does not utilize any spatial index.

Immutable
_st_intersects(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a shares any portion of space with geometry_b.

The calculations performed are have a precision of 1cm.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_overlaps(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a intersects but does not completely contain geometry_b, or vice versa. “Does not completely” implies ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = false.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_touches(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if the only points in common between geometry_a and geometry_b are on the boundary. Note points do not touch other points.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
_st_within(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a is completely inside geometry_b.

This function utilizes the GEOS module.

This function variant does not utilize any spatial index.

Immutable
addgeometrycolumn(catalog_name: string, schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int) → string

Adds a new geometry column to an existing table and returns metadata about the column created.

Volatile
addgeometrycolumn(catalog_name: string, schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string

Adds a new geometry column to an existing table and returns metadata about the column created.

Volatile
addgeometrycolumn(schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int) → string

Adds a new geometry column to an existing table and returns metadata about the column created.

Volatile
addgeometrycolumn(schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string

Adds a new geometry column to an existing table and returns metadata about the column created.

Volatile
addgeometrycolumn(table_name: string, column_name: string, srid: int, type: string, dimension: int) → string

Adds a new geometry column to an existing table and returns metadata about the column created.

Volatile
addgeometrycolumn(table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string

Adds a new geometry column to an existing table and returns metadata about the column created.

Volatile
geometrytype(geometry: geometry) → string

Returns the type of geometry as a string.

This function utilizes the GEOS module.

Immutable
geomfromewkb(val: bytes) → geometry

Returns the Geometry from an EWKB representation.

Immutable
geomfromewkt(val: string) → geometry

Returns the Geometry from an EWKT representation.

Immutable
postgis_addbbox(geometry: geometry) → geometry

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

Immutable
postgis_dropbbox(geometry: geometry) → geometry

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

Immutable
postgis_extensions_upgrade() → string

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

Immutable
postgis_full_version() → string

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

Immutable
postgis_geos_version() → string

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

Immutable
postgis_getbbox(geometry: geometry) → box2d

Returns a box2d encapsulating the given Geometry.

Immutable
postgis_hasbbox(geometry: geometry) → bool

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

Immutable
postgis_lib_build_date() → string

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

Immutable
postgis_lib_version() → string

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

Immutable
postgis_liblwgeom_version() → string

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

Immutable
postgis_libxml_version() → string

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

Immutable
postgis_proj_version() → string

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

Immutable
postgis_scripts_build_date() → string

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

Immutable
postgis_scripts_installed() → string

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

Immutable
postgis_scripts_released() → string

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

Immutable
postgis_version() → string

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

Immutable
postgis_wagyu_version() → string

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

Immutable
st_addmeasure(geometry: geometry, start: float, end: float) → geometry

Returns a copy of a LineString or MultiLineString with measure coordinates linearly interpolated between the specified start and end values. Any existing M coordinates will be overwritten.

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

Adds a Point to the end of a LineString.

Immutable
st_addpoint(line_string: geometry, point: geometry, index: int) → geometry

Adds a Point to a LineString at the given 0-based index (-1 to append).

Immutable
st_affine(geometry: geometry, a: float, b: float, c: float, d: float, e: float, f: float, g: float, h: float, i: float, x_off: float, y_off: float, z_off: float) → geometry

Applies a 3D affine transformation to the given geometry.

The matrix transformation will be applied as follows for each coordinate: / a b c x_off \ / x
| d e f y_off | | y | | g h i z_off | | z | \ 0 0 0 1 / \ 0 /

Immutable
st_affine(geometry: geometry, a: float, b: float, d: float, e: float, x_off: float, y_off: float) → geometry

Applies a 2D affine transformation to the given geometry.

The matrix transformation will be applied as follows for each coordinate: / a b x_off \ / x
| d e y_off | | y | \ 0 0 1 / \ 0 /

Immutable
st_angle(line1: geometry, line2: geometry) → float

Returns the clockwise angle between two LINESTRING geometries, treating them as vectors between their start- and endpoints. Returns NULL if any vectors have 0 length.

Immutable
st_angle(point1: geometry, point2: geometry, point3: geometry) → float

Returns the clockwise angle between the vectors formed by point2,point1 and point2,point3. The arguments must be POINT geometries. Returns NULL if any vectors have 0 length.

Immutable
st_angle(point1: geometry, point2: geometry, point3: geometry, point4: geometry) → float

Returns the clockwise angle between the vectors formed by point1,point2 and point3,point4. The arguments must be POINT geometries. Returns NULL if any vectors have 0 length.

Immutable
st_area(geography: geography) → float

Returns the area of the given geography in meters^2. Uses a spheroid to perform the operation.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_area(geography: geography, use_spheroid: bool) → float

Returns the area of the given geography in meters^2.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_area(geometry: geometry) → float

Returns the area of the given geometry.

This function utilizes the GEOS module.

Immutable
st_area(geometry_str: string) → float

Returns the area of the given geometry.

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_area2d(geometry: geometry) → float

Returns the area of the given geometry.

This function utilizes the GEOS module.

Immutable
st_asbinary(geography: geography) → bytes

Returns the WKB representation of a given Geography.

Immutable
st_asbinary(geography: geography, xdr_or_ndr: string) → bytes

Returns the WKB representation of a given Geography. This variant has a second argument denoting the encoding - xdr for big endian and ndr for little endian.

Immutable
st_asbinary(geometry: geometry) → bytes

Returns the WKB representation of a given Geometry.

Immutable
st_asbinary(geometry: geometry, xdr_or_ndr: string) → bytes

Returns the WKB representation of a given Geometry. This variant has a second argument denoting the encoding - xdr for big endian and ndr for little endian.

Immutable
st_asencodedpolyline(geometry: geometry) → string

Returns the geometry as an Encoded Polyline. This format is used by Google Maps with precision=5 and by Open Source Routing Machine with precision=5 and 6. Preserves 5 decimal places.

Immutable
st_asencodedpolyline(geometry: geometry, precision: int4) → string

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

Immutable
st_asewkb(geography: geography) → bytes

Returns the EWKB representation of a given Geography.

Immutable
st_asewkb(geometry: geometry) → bytes

Returns the EWKB representation of a given Geometry.

Immutable
st_asewkt(geography: geography) → string

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

Immutable
st_asewkt(geography: geography, max_decimal_digits: int) → string

Returns the EWKT representation of a given Geography. The max_decimal_digits parameter controls the maximum decimal digits to print after the .. Use -1 to print as many digits as required to rebuild the same number.

Immutable
st_asewkt(geometry: geometry) → string

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

Immutable
st_asewkt(geometry: geometry, max_decimal_digits: int) → string

Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the .. Use -1 to print as many digits as required to rebuild the same number.

Immutable
st_asewkt(geometry_str: string) → string

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_asewkt(geometry_str: string, max_decimal_digits: int) → string

Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the .. 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.

Immutable
st_asgeojson(geography: geography) → string

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_asgeojson(geometry_str: string, max_decimal_digits: int) → string

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_asgeojson(geometry_str: string, max_decimal_digits: int, options: int) → string

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.

Immutable
st_asgeojson(row: tuple) → string

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

Immutable
st_asgeojson(row: tuple, geo_column: string) → string

Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. Coordinates have a maximum of 9 decimal digits.

Stable
st_asgeojson(row: tuple, geo_column: string, max_decimal_digits: int) → string

Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. max_decimal_digits will be output for each coordinate value.

Stable
st_asgeojson(row: tuple, geo_column: string, max_decimal_digits: int, pretty: bool) → string

Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. max_decimal_digits will be output for each coordinate value. Output will be pretty printed in JSON if pretty is true.

Stable
st_ashexewkb(geography: geography) → string

Returns the EWKB representation in hex of a given Geography.

Immutable
st_ashexewkb(geography: geography, xdr_or_ndr: string) → string

Returns the EWKB representation in hex of a given Geography. This variant has a second argument denoting the encoding - xdr for big endian and ndr for little endian.

Immutable
st_ashexewkb(geometry: geometry) → string

Returns the EWKB representation in hex of a given Geometry.

Immutable
st_ashexewkb(geometry: geometry, xdr_or_ndr: string) → string

Returns the EWKB representation in hex of a given Geometry. This variant has a second argument denoting the encoding - xdr for big endian and ndr for little endian.

Immutable
st_ashexwkb(geography: geography) → string

Returns the WKB representation in hex of a given Geography.

Immutable
st_ashexwkb(geometry: geometry) → string

Returns the WKB representation in hex of a given Geometry.

Immutable
st_askml(geography: geography) → string

Returns the KML representation of a given Geography.

Immutable
st_askml(geometry: geometry) → string

Returns the KML representation of a given Geometry.

Immutable
st_askml(geometry_str: string) → string

Returns the KML representation of a given Geometry.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_asmvtgeom(geometry: geometry, bbox: box2d) → geometry

Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds. Uses 256 as the buffer size in tile coordinate space for geometry clipping. Uses 4096 as the tile extent size in tile coordinate space.

The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry will be clipped can be transformed.

Immutable
st_asmvtgeom(geometry: geometry, bbox: box2d, extent: int) → geometry

Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds. Uses 256 as the buffer size in tile coordinate space for geometry clipping.

The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry will be clipped can be transformed.

Immutable
st_asmvtgeom(geometry: geometry, bbox: box2d, extent: int, buffer: int) → geometry

Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds.

The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry will be clipped can be transformed.

Immutable
st_asmvtgeom(geometry: geometry, bbox: box2d, extent: int, buffer: int, clip: bool) → geometry

Transforms a geometry into the coordinate space of a MVT (Mapbox Vector Tile) tile, clipping it to the tile bounds if required.

The geometry must be in the coordinate system of the target map. The function attempts to preserve geometry validity, and corrects it if needed. This may cause the result geometry to collapse to a lower dimension. The rectangular bounds of the tile in the target map coordinate space must be provided, so the geometry can be transformed, and clipped if required.

Immutable
st_astext(geography: geography) → string

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

Immutable
st_astext(geography: geography, max_decimal_digits: int) → string

Returns the WKT representation of a given Geography. The max_decimal_digits parameter controls the maximum decimal digits to print after the .. Use -1 to print as many digits as required to rebuild the same number.

Immutable
st_astext(geometry: geometry) → string

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

Immutable
st_astext(geometry: geometry, max_decimal_digits: int) → string

Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the .. Use -1 to print as many digits as required to rebuild the same number.

Immutable
st_astext(geometry_str: string) → string

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_astext(geometry_str: string, max_decimal_digits: int) → string

Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the .. 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.

Immutable
st_astwkb(geometry: geometry, precision_xy: int) → bytes

Returns the TWKB representation of a given geometry.

Immutable
st_astwkb(geometry: geometry, precision_xy: int, precision_z: int) → bytes

Returns the TWKB representation of a given geometry.

Immutable
st_astwkb(geometry: geometry, precision_xy: int, precision_z: int, precision_m: int) → bytes

Returns the TWKB representation of a given geometry.

Immutable
st_azimuth(geography_a: geography, geography_b: geography) → float

Returns the azimuth in radians of the segment defined by the given point geographies, or NULL if the two points are coincident. It is solved using the Inverse geodesic problem.

The azimuth is angle is referenced from north, and is positive clockwise: North = 0; East = π/2; South = π; West = 3π/2.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_azimuth(geometry_a: geometry, geometry_b: geometry) → float

Returns the azimuth in radians of the segment defined by the given point geometries, or NULL if the two points are coincident.

The azimuth is angle is referenced from north, and is positive clockwise: North = 0; East = π/2; South = π; West = 3π/2.

Immutable
st_bdpolyfromtext(str: string, srid: int) → geometry

Returns a Polygon from multilinestring WKT with a SRID. If the input is not a multilinestring an error will be thrown.

Immutable
st_boundary(geometry: geometry) → geometry

Returns the closure of the combinatorial boundary of this Geometry.

This function utilizes the GEOS module.

Immutable
st_box2dfromgeohash(geohash: string) → box2d

Return a Box2D from a GeoHash string with max precision.

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

Return a Box2D from a GeoHash string with supplied precision.

Immutable
st_buffer(geography: geography, distance: float) → geography

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

This function utilizes the GEOS module.

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

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

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

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

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

This function utilizes the GEOS module.

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

Immutable
st_buffer(geography: geography, distance: float, quad_segs: int) → geography

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

This variant approximates the circle into quad_seg segments per line (the default is 8).

This function utilizes the GEOS module.

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

Immutable
st_buffer(geometry: geometry, distance: decimal) → geometry

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

This function utilizes the GEOS module.

Immutable
st_buffer(geometry: geometry, distance: float) → geometry

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

This function utilizes the GEOS module.

Immutable
st_buffer(geometry: geometry, distance: float, buffer_style_params: string) → geometry

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

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

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

This function utilizes the GEOS module.

Immutable
st_buffer(geometry: geometry, distance: float, quad_segs: int) → geometry

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

This variant approximates the circle into quad_seg segments per line (the default is 8).

This function utilizes the GEOS module.

Immutable
st_buffer(geometry: geometry, distance: int) → geometry

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

This function utilizes the GEOS module.

Immutable
st_buffer(geometry_str: string, distance: decimal) → geometry

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

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_buffer(geometry_str: string, distance: float) → geometry

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

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_buffer(geometry_str: string, distance: float, buffer_style_params: string) → geometry

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

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

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

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_buffer(geometry_str: string, distance: float, quad_segs: int) → geometry

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

This variant approximates the circle into quad_seg segments per line (the default is 8).

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_buffer(geometry_str: string, distance: int) → geometry

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

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_centroid(geography: geography) → geography

Returns the centroid of given geography. Uses a spheroid to perform the operation.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_centroid(geography: geography, use_spheroid: bool) → geography

Returns the centroid of given geography.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_centroid(geometry: geometry) → geometry

Returns the centroid of the given geometry.

This function utilizes the GEOS module.

Immutable
st_centroid(geometry_str: string) → geometry

Returns the centroid of the given geometry.

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

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

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

Immutable
st_closestpoint(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the 2-dimensional point on geometry_a that is closest to geometry_b. This is the first point of the shortest line.

Immutable
st_collectionextract(geometry: geometry, type: int) → geometry

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

Immutable
st_collectionhomogenize(geometry: geometry) → geometry

Returns the “simplest” representation of a collection’s contents. Collections of a single type will be returned as an appopriate multitype, or a singleton if it only contains a single geometry.

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

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

Immutable
st_contains(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if no points of geometry_b lie in the exterior of geometry_a, and there is at least one point in the interior of geometry_b that lies in the interior of geometry_a.

This function utilizes the GEOS module.

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

Immutable
st_containsproperly(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a.

This function utilizes the GEOS module.

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

Immutable
st_convexhull(geometry: geometry) → geometry

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

This function utilizes the GEOS module.

Immutable
st_coorddim(geometry: geometry) → int

Returns the number of coordinate dimensions of a given Geometry.

Immutable
st_coveredby(geography_a: geography, geography_b: geography) → bool

Returns true if no point in geography_a is outside geography_b.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

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

Immutable
st_coveredby(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if no point in geometry_a is outside geometry_b.

This function utilizes the GEOS module.

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

Immutable
st_coveredby(geometry_a_str: string, geometry_b_str: string) → bool

Returns true if no point in geometry_a is outside geometry_b.

This function utilizes the GEOS module.

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_covers(geography_a: geography, geography_b: geography) → bool

Returns true if no point in geography_b is outside geography_a.

This function utilizes the S2 library for spherical calculations.

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

Immutable
st_covers(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if no point in geometry_b is outside geometry_a.

This function utilizes the GEOS module.

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

Immutable
st_covers(geometry_a_str: string, geometry_b_str: string) → bool

Returns true if no point in geometry_b is outside geometry_a.

This function utilizes the GEOS module.

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_crosses(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a has some - but not all - interior points in common with geometry_b.

This function utilizes the GEOS module.

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

Immutable
st_dfullywithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, inclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than or equal to distance units.

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

Immutable
st_dfullywithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, exclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than distance units.

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

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

Returns the difference of two Geometries.

This function utilizes the GEOS module.

Immutable
st_dimension(geometry: geometry) → int

Returns the number of topological dimensions of a given Geometry.

Immutable
st_disjoint(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a does not overlap, touch or is within geometry_b.

This function utilizes the GEOS module.

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

Returns the distance in meters between geography_a and geography_b. Uses a spheroid to perform the operation.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

This function utilizes the GeographicLib library for spheroid calculations.

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

Returns the distance in meters between geography_a and geography_b.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

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

Returns the distance between the given geometries.

Immutable
st_distance(geometry_a_str: string, geometry_b_str: string) → float

Returns the distance between the given geometries.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_distancesphere(geometry_a: geometry, geometry_b: geometry) → float

Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a sphere.

This function utilizes the S2 library for spherical calculations.

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

Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a spheroid.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

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

Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

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

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

Returns true if any of geography_a is within distance meters of geography_b, inclusive.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

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

Immutable
st_dwithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if any of geometry_a is within distance units of geometry_b, inclusive.

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

Immutable
st_dwithin(geometry_a_str: string, geometry_b_str: string, distance: float) → bool

Returns true if any of geometry_a is within distance units of geometry_b, inclusive.

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

This variant will cast all geometry_str arguments into Geometry types.

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

Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the GeographicLib library for spheroid calculations.

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

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

Returns true if any of geography_a is within distance meters of geography_b, exclusive.

When operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

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

Immutable
st_dwithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool

Returns true if any of geometry_a is within distance units of geometry_b, exclusive.

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

Immutable
st_dwithinexclusive(geometry_a_str: string, geometry_b_str: string, distance: float) → bool

Returns true if any of geometry_a is within distance units of geometry_b, exclusive.

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_endpoint(geometry: geometry) → geometry

Returns the last point of a geometry which has shape LineString. Returns NULL if the geometry is not a LineString.

Immutable
st_envelope(box2d: box2d) → geometry

Returns a bounding geometry for the given box.

Immutable
st_envelope(geometry: geometry) → geometry

Returns a bounding envelope for the given geometry.

For geometries which have a POINT or LINESTRING bounding box (i.e. is a single point or a horizontal or vertical line), a POINT or LINESTRING is returned. Otherwise, the returned POLYGON will be ordered Bottom Left, Top Left, Top Right, Bottom Right, Bottom Left.

Immutable
st_equals(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a is spatially equal to geometry_b, i.e. ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = true.

This function utilizes the GEOS module.

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

Immutable
st_estimatedextent(schema_name: string, table_name: string, geocolumn_name: string) → box2d

Returns the estimated extent of the geometries in the column of the given table. This currently always returns NULL.

Stable
st_estimatedextent(schema_name: string, table_name: string, geocolumn_name: string, parent_only: bool) → box2d

Returns the estimated extent of the geometries in the column of the given table. This currently always returns NULL.

The parent_only boolean is always ignored.

Stable
st_estimatedextent(table_name: string, geocolumn_name: string) → box2d

Returns the estimated extent of the geometries in the column of the given table. This currently always returns NULL.

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

Extends the box2d by delta units across all dimensions.

Immutable
st_expand(box2d: box2d, delta_x: float, delta_y: float) → box2d

Extends the box2d by delta_x units in the x dimension and delta_y units in the y dimension.

Immutable
st_expand(geometry: geometry, delta: float) → geometry

Extends the bounding box represented by the geometry by delta units across all dimensions, returning a Polygon representing the new bounding box.

Immutable
st_expand(geometry: geometry, delta_x: float, delta_y: float) → geometry

Extends the bounding box represented by the geometry by delta_x units in the x dimension and delta_y units in the y dimension, returning a Polygon representing the new bounding box.

Immutable
st_exteriorring(geometry: geometry) → geometry

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

Immutable
st_flipcoordinates(geometry: geometry) → geometry

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

Immutable
st_force2d(geometry: geometry) → geometry

Returns a Geometry that is forced into XY layout with any Z or M dimensions discarded.

Immutable
st_force3d(geometry: geometry) → geometry

Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to 0. If a M coordinate is present, it will be discarded.

Immutable
st_force3d(geometry: geometry, defaultZ: float) → geometry

Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified default Z value. If a M coordinate is present, it will be discarded.

Immutable
st_force3dm(geometry: geometry) → geometry

Returns a Geometry that is forced into XYM layout. If a M coordinate doesn’t exist, it will be set to 0. If a Z coordinate is present, it will be discarded.

Immutable
st_force3dm(geometry: geometry, defaultM: float) → geometry

Returns a Geometry that is forced into XYM layout. If a M coordinate doesn’t exist, it will be set to the specified default M value. If a Z coordinate is present, it will be discarded.

Immutable
st_force3dz(geometry: geometry) → geometry

Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to 0. If a M coordinate is present, it will be discarded.

Immutable
st_force3dz(geometry: geometry, defaultZ: float) → geometry

Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified default Z value. If a M coordinate is present, it will be discarded.

Immutable
st_force4d(geometry: geometry) → geometry

Returns a Geometry that is forced into XYZM layout. If a Z coordinate doesn’t exist, it will be set to 0. If a M coordinate doesn’t exist, it will be set to 0.

Immutable
st_force4d(geometry: geometry, defaultZ: float) → geometry

Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified default Z value. If a M coordinate doesn’t exist, it will be set to 0.

Immutable
st_force4d(geometry: geometry, defaultZ: float, defaultM: float) → geometry

Returns a Geometry that is forced into XYZ layout. If a Z coordinate doesn’t exist, it will be set to the specified Z value. If a M coordinate doesn’t exist, it will be set to the specified M value.

Immutable
st_forcecollection(geometry: geometry) → geometry

Converts the geometry into a GeometryCollection.

Immutable
st_forcepolygonccw(geometry: geometry) → geometry

Returns a Geometry where all Polygon objects have exterior rings in the counter-clockwise orientation and interior rings in the clockwise orientation. Non-Polygon objects are unchanged.

Immutable
st_forcepolygoncw(geometry: geometry) → geometry

Returns a Geometry where all Polygon objects have exterior rings in the clockwise orientation and interior rings in the counter-clockwise orientation. Non-Polygon objects are unchanged.

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

Returns the Frechet distance between the given geometries.

This function utilizes the GEOS module.

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

Returns the Frechet distance between the given geometries, with the given segment densification (range 0.0-1.0, -1 to disable).

Smaller densify_frac gives a more accurate Fréchet distance. However, the computation time and memory usage increases with the square of the number of subsegments.

This function utilizes the GEOS module.

Immutable
st_generatepoints(geometry: geometry, npoints: int4) → geometry

Generates pseudo-random points until the requested number are found within the input area. Uses system time as a seed. The requested number of points must be not larger than 65336.

Volatile
st_generatepoints(geometry: geometry, npoints: int4, seed: int4) → geometry

Generates pseudo-random points until the requested number are found within the input area. The requested number of points must be not larger than 65336.

Immutable
st_geogfromewkb(val: bytes) → geography

Returns the Geography from an EWKB representation.

Immutable
st_geogfromewkt(val: string) → geography

Returns the Geography from an EWKT representation.

Immutable
st_geogfromgeojson(val: string) → geography

Returns the Geography from an GeoJSON representation.

Immutable
st_geogfromgeojson(val: jsonb) → geography

Returns the Geography from an GeoJSON representation.

Immutable
st_geogfromtext(str: string, srid: int) → geography

Returns the Geography from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_geogfromtext(val: string) → geography

Returns the Geography from a WKT or EWKT representation.

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

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

Immutable
st_geogfromwkb(val: bytes) → geography

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

Immutable
st_geographyfromtext(str: string, srid: int) → geography

Returns the Geography from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_geographyfromtext(val: string) → geography

Returns the Geography from a WKT or EWKT representation.

Immutable
st_geohash(geography: geography) → string

Returns a GeoHash representation of the geeographywith full precision if a point is provided, or with variable precision based on the size of the feature.

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

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

Immutable
st_geohash(geometry: geometry) → string

Returns a GeoHash representation of the geometry with full precision if a point is provided, or with variable precision based on the size of the feature. This will error any coordinates are outside the bounds of longitude/latitude.

Immutable
st_geohash(geometry: geometry, precision: int) → string

Returns a GeoHash representation of the geometry with the supplied precision. This will error any coordinates are outside the bounds of longitude/latitude.

Immutable
st_geomcollfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not GeometryCollection, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_geomcollfromtext(val: string) → geometry

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

Immutable
st_geomcollfromwkb(val: bytes) → geometry

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

Immutable
st_geomcollfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_geometryfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_geometryfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation.

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

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

Immutable
st_geometrytype(geometry: geometry) → string

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

This function utilizes the GEOS module.

Immutable
st_geomfromewkb(val: bytes) → geometry

Returns the Geometry from an EWKB representation.

Immutable
st_geomfromewkt(val: string) → geometry

Returns the Geometry from an EWKT representation.

Immutable
st_geomfromgeohash(geohash: string) → geometry

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

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

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

Immutable
st_geomfromgeojson(val: string) → geometry

Returns the Geometry from an GeoJSON representation.

Immutable
st_geomfromgeojson(val: jsonb) → geometry

Returns the Geometry from an GeoJSON representation.

Immutable
st_geomfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_geomfromtext(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation.

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

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

Immutable
st_geomfromwkb(val: bytes) → geometry

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

Immutable
st_hasarc(geometry: geometry) → bool

Returns whether there is a CIRCULARSTRING in the geometry.

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

Returns the Hausdorff distance between the given geometries.

This function utilizes the GEOS module.

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

Returns the Hausdorff distance between the given geometries, with the given segment densification (range 0.0-1.0).

This function utilizes the GEOS module.

Immutable
st_interiorringn(geometry: geometry, n: int) → geometry

Returns the n-th (1-indexed) interior ring of a Polygon as a LineString. Returns NULL if the shape is not a Polygon, or the ring does not exist.

Immutable
st_intersection(geography_a: geography, geography_b: geography) → geography

Returns the point intersections of the given geographies.

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

This function utilizes the GEOS module.

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

Returns the point intersections of the given geometries.

This function utilizes the GEOS module.

Immutable
st_intersection(geometry_a_str: string, geometry_b_str: string) → geometry

Returns the point intersections of the given geometries.

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_intersects(geography_a: geography, geography_b: geography) → bool

Returns true if geography_a shares any portion of space with geography_b.

The calculations performed are have a precision of 1cm.

This function utilizes the S2 library for spherical calculations.

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

Immutable
st_intersects(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a shares any portion of space with geometry_b.

The calculations performed are have a precision of 1cm.

This function utilizes the GEOS module.

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

Immutable
st_intersects(geometry_a_str: string, geometry_b_str: string) → bool

Returns true if geometry_a shares any portion of space with geometry_b.

The calculations performed are have a precision of 1cm.

This function utilizes the GEOS module.

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

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_isclosed(geometry: geometry) → bool

Returns whether the geometry is closed as defined by whether the start and end points are coincident. Points are considered closed, empty geometries are not. For collections and multi-types, all members must be closed, as must all polygon rings.

Immutable
st_iscollection(geometry: geometry) → bool

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

Immutable
st_isempty(geometry: geometry) → bool

Returns whether the geometry is empty.

Immutable
st_ispolygonccw(geometry: geometry) → bool

Returns whether the Polygon objects inside the Geometry have exterior rings in the counter-clockwise orientation and interior rings in the clockwise orientation. Non-Polygon objects are considered counter-clockwise.

Immutable
st_ispolygoncw(geometry: geometry) → bool

Returns whether the Polygon objects inside the Geometry have exterior rings in the clockwise orientation and interior rings in the counter-clockwise orientation. Non-Polygon objects are considered clockwise.

Immutable
st_isring(geometry: geometry) → bool

Returns whether the geometry is a single linestring that is closed and simple, as defined by ST_IsClosed and ST_IsSimple.

This function utilizes the GEOS module.

Immutable
st_issimple(geometry: geometry) → bool

Returns true if the geometry has no anomalous geometric points, e.g. that it intersects with or lies tangent to itself.

This function utilizes the GEOS module.

Immutable
st_isvalid(geometry: geometry) → bool

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

This function utilizes the GEOS module.

Immutable
st_isvalid(geometry: geometry, flags: int) → bool

Returns whether the geometry is valid.

For flags=0, validity is defined by the OGC spec.

For flags=1, validity considers self-intersecting rings forming holes as valid as per ESRI. This is not valid under OGC and CRDB spatial operations may not operate correctly.

This function utilizes the GEOS module.

Immutable
st_isvalidreason(geometry: geometry) → string

Returns a string containing the reason the geometry is invalid along with the point of interest, or “Valid Geometry” if it is valid. Validity is defined by the OGC spec.

This function utilizes the GEOS module.

Immutable
st_isvalidreason(geometry: geometry, flags: int) → string

Returns the reason the geometry is invalid or “Valid Geometry” if it is valid.

For flags=0, validity is defined by the OGC spec.

For flags=1, validity considers self-intersecting rings forming holes as valid as per ESRI. This is not valid under OGC and CRDB spatial operations may not operate correctly.

This function utilizes the GEOS module.

Immutable
st_isvalidtrajectory(geometry: geometry) → bool

Returns whether the geometry encodes a valid trajectory.

Note the geometry must be a LineString with M coordinates.

Immutable
st_length(geography: geography) → float

Returns the length of the given geography in meters. Uses a spheroid to perform the operation.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_length(geography: geography, use_spheroid: bool) → float

Returns the length of the given geography in meters.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_length(geometry: geometry) → float

Returns the length of the given geometry.

Note ST_Length is only valid for LineString - use ST_Perimeter for Polygon.

This function utilizes the GEOS module.

Immutable
st_length(geometry_str: string) → float

Returns the length of the given geometry.

Note ST_Length is only valid for LineString - use ST_Perimeter for Polygon.

This function utilizes the GEOS module.

This variant will cast all geometry_str arguments into Geometry types.

Immutable
st_length2d(geometry: geometry) → float

Returns the length of the given geometry.

Note ST_Length is only valid for LineString - use ST_Perimeter for Polygon.

This function utilizes the GEOS module.

Immutable
st_linecrossingdirection(linestring_a: geometry, linestring_b: geometry) → int

Returns an interger value defining behavior of crossing of lines: 0: lines do not cross, -1: linestring_b crosses linestring_a from right to left, 1: linestring_b crosses linestring_a from left to right, -2: linestring_b crosses linestring_a multiple times from right to left, 2: linestring_b crosses linestring_a multiple times from left to right, -3: linestring_b crosses linestring_a multiple times from left to left, 3: linestring_b crosses linestring_a multiple times from right to right.

Note that the top vertex of the segment touching another line does not count as a crossing, but the bottom vertex of segment touching another line is considered a crossing.

Immutable
st_linefromencodedpolyline(encoded_polyline: string) → geometry

Creates a LineString from an Encoded Polyline string.

Returns valid results only if the polyline was encoded with 5 decimal places.

See http://developers.google.com/maps/documentation/utilities/polylinealgorithm

Immutable
st_linefromencodedpolyline(encoded_polyline: string, precision: int4) → geometry

Creates a LineString from an Encoded Polyline string.

Precision specifies how many decimal places will be preserved in Encoded Polyline. Value should be the same on encoding and decoding, or coordinates will be incorrect.

See http://developers.google.com/maps/documentation/utilities/polylinealgorithm

Immutable
st_linefrommultipoint(geometry: geometry) → geometry

Creates a LineString from a MultiPoint geometry.

Immutable
st_linefromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not LineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_linefromtext(val: string) → geometry

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

Immutable
st_linefromwkb(val: bytes) → geometry

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

Immutable
st_linefromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_lineinterpolatepoint(geometry: geometry, fraction: float) → geometry

Returns a point along the given LineString which is at given fraction of LineString’s total length.

This function utilizes the GEOS module.

Immutable
st_lineinterpolatepoints(geometry: geometry, fraction: float) → geometry

Returns one or more points along the LineString which is at an integral multiples of given fraction of LineString’s total length.

Note If the result has zero or one points, it will be returned as a POINT. If it has two or more points, it will be returned as a MULTIPOINT.

This function utilizes the GEOS module.

Immutable
st_lineinterpolatepoints(geometry: geometry, fraction: float, repeat: bool) → geometry

Returns one or more points along the LineString which is at an integral multiples of given fraction of LineString’s total length. If repeat is false (default true) then it returns first point.

Note If the result has zero or one points, it will be returned as a POINT. If it has two or more points, it will be returned as a MULTIPOINT.

This function utilizes the GEOS module.

Immutable
st_linelocatepoint(line: geometry, point: geometry) → float

Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length.

Immutable
st_linemerge(geometry: geometry) → geometry

Returns a LineString or MultiLineString by joining together constituents of a MultiLineString with matching endpoints. If the input is not a MultiLineString or LineString, an empty GeometryCollection is returned.

This function utilizes the GEOS module.

Immutable
st_linestringfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not LineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_linestringfromtext(val: string) → geometry

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

Immutable
st_linestringfromwkb(val: bytes) → geometry

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

Immutable
st_linestringfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_linesubstring(linestring: geometry, start_fraction: decimal, end_fraction: decimal) → geometry

Return a linestring being a substring of the input one starting and ending at the given fractions of total 2D length. Second and third arguments are float8 values between 0 and 1.

Immutable
st_linesubstring(linestring: geometry, start_fraction: float, end_fraction: float) → geometry

Return a linestring being a substring of the input one starting and ending at the given fractions of total 2D length. Second and third arguments are float8 values between 0 and 1.

Immutable
st_longestline(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the LineString corresponds to the max distance across every pair of points comprising the given geometries.

Note if geometries are the same, it will return the LineString with the maximum distance between the geometry’s vertexes. The function will return the longest line that was discovered first when comparing maximum distances if more than one is found.

Immutable
st_m(geometry: geometry) → float

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

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

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

Immutable
st_makeenvelope(xmin: float, ymin: float, xmax: float, ymax: float) → geometry

Creates a rectangular Polygon from the minimum and maximum values for X and Y with SRID 0.

Immutable
st_makeenvelope(xmin: float, ymin: float, xmax: float, ymax: float, srid: int) → geometry

Creates a rectangular Polygon from the minimum and maximum values for X and Y with the given SRID.

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

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

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

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

Immutable
st_makepoint(x: float, y: float, z: float, m: float) → geometry

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

Immutable
st_makepointm(x: float, y: float, m: float) → geometry

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

Immutable
st_makepolygon(geometry: geometry) → geometry

Returns a new Polygon with the given outer LineString.

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

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

Immutable
st_makevalid(geometry: geometry) → geometry

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

This function utilizes the GEOS module.

Immutable
st_maxdistance(geometry_a: geometry, geometry_b: geometry) → float

Returns the maximum distance across every pair of points comprising the given geometries. Note if the geometries are the same, it will return the maximum distance between the geometry’s vertexes.

Immutable
st_memsize(geometry: geometry) → int

Returns the amount of memory space (in bytes) the geometry takes.

Immutable
st_minimumboundingcircle(geometry: geometry) → geometry

Returns the smallest circle polygon that can fully contain a geometry.

Immutable
st_minimumboundingcircle(geometry: geometry, num_segs: int) → geometry

Returns the smallest circle polygon that can fully contain a geometry.

Immutable
st_minimumboundingradius(geometry: geometry) → tuple{geometry AS center, float AS radius}

Returns a record containing the center point and radius of the smallest circle that can fully contains the given geometry.

Immutable
st_minimumclearance(geometry: geometry) → float

Returns the minimum distance a vertex can move before producing an invalid geometry. Returns Infinity if no minimum clearance can be found (e.g. for a single point).

Immutable
st_minimumclearanceline(geometry: geometry) → geometry

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

Immutable
st_mlinefromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_mlinefromtext(val: string) → geometry

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

Immutable
st_mlinefromwkb(val: bytes) → geometry

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

Immutable
st_mlinefromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_mpointfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_mpointfromtext(val: string) → geometry

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

Immutable
st_mpointfromwkb(val: bytes) → geometry

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

Immutable
st_mpointfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_mpolyfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_mpolyfromtext(val: string) → geometry

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

Immutable
st_mpolyfromwkb(val: bytes) → geometry

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

Immutable
st_mpolyfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_multi(geometry: geometry) → geometry

Returns the geometry as a new multi-geometry, e.g converts a POINT to a MULTIPOINT. If the input is already a multitype or collection, it is returned as is.

Immutable
st_multilinefromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_multilinefromtext(val: string) → geometry

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

Immutable
st_multilinefromwkb(val: bytes) → geometry

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

Immutable
st_multilinefromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_multilinestringfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_multilinestringfromtext(val: string) → geometry

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

Immutable
st_multilinestringfromwkb(val: bytes) → geometry

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

Immutable
st_multilinestringfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_multipointfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_multipointfromtext(val: string) → geometry

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

Immutable
st_multipointfromwkb(val: bytes) → geometry

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

Immutable
st_multipointfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_multipolyfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_multipolyfromtext(val: string) → geometry

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

Immutable
st_multipolyfromwkb(val: bytes) → geometry

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

Immutable
st_multipolyfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_multipolygonfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_multipolygonfromtext(val: string) → geometry

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

Immutable
st_multipolygonfromwkb(val: bytes) → geometry

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

Immutable
st_multipolygonfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_ndims(geometry: geometry) → int

Returns the number of coordinate dimensions of a given Geometry.

Immutable
st_node(geometry: geometry) → geometry

Adds a node on a geometry for each intersection. Resulting geometry is always a MultiLineString.

Immutable
st_normalize(geometry: geometry) → geometry

Returns the geometry in its normalized form.

This function utilizes the GEOS module.

Immutable
st_npoints(geometry: geometry) → int

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

Immutable
st_nrings(geometry: geometry) → int

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

Immutable
st_numgeometries(geometry: geometry) → int

Returns the number of shapes inside a given Geometry.

Immutable
st_numinteriorring(geometry: geometry) → int

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

Immutable
st_numinteriorrings(geometry: geometry) → int

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

Immutable
st_numpoints(geometry: geometry) → int

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

Immutable
st_orderingequals(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a is exactly equal to geometry_b, having all coordinates in the same order, as well as the same type, SRID, bounding box, and so on.

Immutable
st_orientedenvelope(geometry: geometry) → geometry

Returns a minimum rotated rectangle enclosing a geometry. Note that more than one minimum rotated rectangle may exist. May return a Point or LineString in the case of degenerate inputs.

Immutable
st_overlaps(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a intersects but does not completely contain geometry_b, or vice versa. “Does not completely” implies ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = false.

This function utilizes the GEOS module.

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

Immutable
st_perimeter(geography: geography) → float

Returns the perimeter of the given geography in meters. Uses a spheroid to perform the operation.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_perimeter(geography: geography, use_spheroid: bool) → float

Returns the perimeter of the given geography in meters.

This function utilizes the S2 library for spherical calculations.

This function utilizes the GeographicLib library for spheroid calculations.

Immutable
st_perimeter(geometry: geometry) → float

Returns the perimeter of the given geometry.

Note ST_Perimeter is only valid for Polygon - use ST_Length for LineString.

This function utilizes the GEOS module.

Immutable
st_perimeter2d(geometry: geometry) → float

Returns the perimeter of the given geometry.

Note ST_Perimeter is only valid for Polygon - use ST_Length for LineString.

This function utilizes the GEOS module.

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

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

Immutable
st_pointfromgeohash(geohash: string) → geometry

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

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

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

Immutable
st_pointfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Point, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_pointfromtext(val: string) → geometry

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

Immutable
st_pointfromwkb(val: bytes) → geometry

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

Immutable
st_pointfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_pointinsidecircle(geometry: geometry, x_coord: float, y_coord: float, radius: float) → bool

Returns the true if the geometry is a point and is inside the circle. Returns false otherwise.

Immutable
st_pointn(geometry: geometry, n: int) → geometry

Returns the n-th Point of a LineString (1-indexed). Returns NULL if out of bounds or not a LineString.

Immutable
st_pointonsurface(geometry: geometry) → geometry

Returns a point that intersects with the given Geometry.

This function utilizes the GEOS module.

Immutable
st_points(geometry: geometry) → geometry

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

Immutable
st_polyfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Polygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_polyfromtext(val: string) → geometry

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

Immutable
st_polyfromwkb(val: bytes) → geometry

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

Immutable
st_polyfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_polygon(geometry: geometry, srid: int) → geometry

Returns a new Polygon from the given LineString and sets its SRID. It is equivalent to ST_MakePolygon with a single argument followed by ST_SetSRID.

Immutable
st_polygonfromtext(str: string, srid: int) → geometry

Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Polygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used.

Immutable
st_polygonfromtext(val: string) → geometry

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

Immutable
st_polygonfromwkb(val: bytes) → geometry

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

Immutable
st_polygonfromwkb(wkb: bytes, srid: int) → geometry

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

Immutable
st_project(geography: geography, distance: float, azimuth: float) → geography

Returns a point projected from a start point along a geodesic using a given distance and azimuth (bearing). This is known as the direct geodesic problem.

The distance is given in meters. Negative values are supported.

The azimuth (also known as heading or bearing) is given in radians. It is measured clockwise from true north (azimuth zero). East is azimuth π/2 (90 degrees); south is azimuth π (180 degrees); west is azimuth 3π/2 (270 degrees). Negative azimuth values and values greater than 2π (360 degrees) are supported.

Immutable
st_relate(geometry_a: geometry, geometry_b: geometry) → string

Returns the DE-9IM spatial relation between geometry_a and geometry_b.

This function utilizes the GEOS module.

Immutable
st_relate(geometry_a: geometry, geometry_b: geometry, bnr: int) → string

Returns the DE-9IM spatial relation between geometry_a and geometry_b using the given boundary node rule (1:OGC/MOD2, 2:Endpoint, 3:MultivalentEndpoint, 4:MonovalentEndpoint).

This function utilizes the GEOS module.

Immutable
st_relate(geometry_a: geometry, geometry_b: geometry, pattern: string) → bool

Returns whether the DE-9IM spatial relation between geometry_a and geometry_b matches the DE-9IM pattern.

This function utilizes the GEOS module.

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

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

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

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

Immutable
st_removerepeatedpoints(geometry: geometry) → geometry

Returns a geometry with repeated points removed.

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

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

Immutable
st_reverse(geometry: geometry) → geometry

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

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

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

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

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

Immutable
st_rotate(g: geometry, angle_radians: float, origin_x: float, origin_y: float) → geometry

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

Immutable
st_rotatex(g: geometry, angle_radians: float) → geometry

Returns a modified Geometry whose coordinates are rotated about the x axis by a rotation angle.

Immutable
st_rotatey(g: geometry, angle_radians: float) → geometry

Returns a modified Geometry whose coordinates are rotated about the y axis by a rotation angle.

Immutable
st_rotatez(g: geometry, angle_radians: float) → geometry

Returns a modified Geometry whose coordinates are rotated about the z axis by a rotation angle.

Immutable
st_s2covering(geography: geography) → geography

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

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

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

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

Immutable
st_s2covering(geometry: geometry) → geometry

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

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

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

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

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

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

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

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

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

Returns a modified Geometry scaled by the given factors.

Immutable
st_segmentize(geography: geography, max_segment_length_meters: float) → geography

Returns a modified Geography having no segment longer than the given max_segment_length meters.

The calculations are done on a sphere.

This function utilizes the S2 library for spherical calculations.

Immutable
st_segmentize(geometry: geometry, max_segment_length: float) → geometry

Returns a modified Geometry having no segment longer than the given max_segment_length. Length units are in units of spatial reference.

Immutable
st_setpoint(line_string: geometry, index: int, point: geometry) → geometry

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

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

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

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

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

Immutable
st_sharedpaths(geometry_a: geometry, geometry_b: geometry) → geometry

Returns a collection containing paths shared by the two input geometries.

Those going in the same direction are in the first element of the collection, those going in the opposite direction are in the second element. The paths themselves are given in the direction of the first geometry.

Immutable
st_shiftlongitude(geometry: geometry) → geometry

Returns a modified version of a geometry in which the longitude (X coordinate) of each point is incremented by 360 if it is <0 and decremented by 360 if it is >180. The result is only meaningful if the coordinates are in longitude/latitude.

Immutable
st_shortestline(geometry_a: geometry, geometry_b: geometry) → geometry

Returns the LineString corresponds to the minimum distance across every pair of points comprising the given geometries.

Note if geometries are the same, it will return the LineString with the minimum distance between the geometry’s vertexes. The function will return the shortest line that was discovered first when comparing minimum distances if more than one is found.

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

Simplifies the given geometry using the Douglas-Peucker algorithm.

This function utilizes the GEOS module.

Immutable
st_simplify(geometry: geometry, tolerance: float, preserve_collapsed: bool) → geometry

Simplifies the given geometry using the Douglas-Peucker algorithm, retaining objects that would be too small given the tolerance if preserve_collapsed is set to true.

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

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

This function utilizes the GEOS module.

Immutable
st_snap(input: geometry, target: geometry, tolerance: float) → geometry

Snaps the vertices and segments of input geometry the target geometry’s vertices. Tolerance is used to control where snapping is performed. The result geometry is the input geometry with the vertices snapped. If no snapping occurs then the input geometry is returned unchanged.

Immutable
st_snaptogrid(geometry: geometry, origin: geometry, size_x: float, size_y: float, size_z: float, size_m: float) → geometry

Snap a geometry to a grid defined by the given origin and X, Y, Z, and M cell sizes. Any dimension with a 0 cell size will not be snapped.

Immutable
st_snaptogrid(geometry: geometry, origin_x: float, origin_y: float, size_x: float, size_y: float) → geometry

Snap a geometry to a grid of with X coordinates snapped to size_x and Y coordinates snapped to size_y based on an origin of (origin_x, origin_y).

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

Snap a geometry to a grid of the given size. The specified size is only used to snap X and Y coordinates.

Immutable
st_snaptogrid(geometry: geometry, size_x: float, size_y: float) → geometry

Snap a geometry to a grid of with X coordinates snapped to size_x and Y coordinates snapped to size_y.

Immutable
st_srid(geography: geography) → int

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

Immutable
st_srid(geometry: geometry) → int

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

Immutable
st_startpoint(geometry: geometry) → geometry

Returns the first point of a geometry which has shape LineString. Returns NULL if the geometry is not a LineString.

Immutable
st_subdivide(geometry: geometry) → geometry

Returns a geometry divided into parts, where each part contains no more than 256 vertices.

Immutable
st_subdivide(geometry: geometry, max_vertices: int4) → geometry

Returns a geometry divided into parts, where each part contains no more than the number of vertices provided.

Immutable
st_summary(geography: geography) → string

Returns a text summary of the contents of the geography.

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

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

Returns a text summary of the contents of the geometry.

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

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

Returns a version of the given geometry with given ordinates swapped. The swap_ordinate_string parameter is a 2-character string naming the ordinates to swap. Valid names are: x, y, z and m.

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

Returns the symmetric difference of both geometries.

This function utilizes the GEOS module.

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

Returns the symmetric difference of both geometries.

This function utilizes the GEOS module.

Immutable
st_tileenvelope(tileZoom: int4, tileX: int4, tileY: int4) → geometry

Creates a rectangular Polygon giving the extent of a tile in the XYZ tile system. The tile is specifed by the zoom level Z and the XY index of the tile in the grid at that level. Can be used to define the tile bounds required by ST_AsMVTGeom to convert geometry into the MVT tile coordinate space.

Immutable
st_tileenvelope(tileZoom: int4, tileX: int4, tileY: int4, bounds: geometry) → geometry

Creates a rectangular Polygon giving the extent of a tile in the XYZ tile system. The tile is specifed by the zoom level Z and the XY index of the tile in the grid at that level. Can be used to define the tile bounds required by ST_AsMVTGeom to convert geometry into the MVT tile coordinate space.

Immutable
st_tileenvelope(tileZoom: int4, tileX: int4, tileY: int4, bounds: geometry, margin: float) → geometry

Creates a rectangular Polygon giving the extent of a tile in the XYZ tile system. The tile is specifed by the zoom level Z and the XY index of the tile in the grid at that level. Can be used to define the tile bounds required by ST_AsMVTGeom to convert geometry into the MVT tile coordinate space.

Immutable
st_touches(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if the only points in common between geometry_a and geometry_b are on the boundary. Note points do not touch other points.

This function utilizes the GEOS module.

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

Immutable
st_transform(geometry: geometry, from_proj_text: string, srid: int) → geometry

Transforms a geometry into the coordinate reference system assuming the from_proj_text to the new to_proj_text by projecting its coordinates. The supplied SRID is set on the new geometry.

This function utilizes the PROJ library for coordinate projections.

Immutable
st_transform(geometry: geometry, from_proj_text: string, to_proj_text: string) → geometry

Transforms a geometry into the coordinate reference system assuming the from_proj_text to the new to_proj_text by projecting its coordinates.

This function utilizes the PROJ library for coordinate projections.

Immutable
st_transform(geometry: geometry, srid: int) → geometry

Transforms a geometry into the given SRID coordinate reference system by projecting its coordinates.

This function utilizes the PROJ library for coordinate projections.

Immutable
st_transform(geometry: geometry, to_proj_text: string) → geometry

Transforms a geometry into the coordinate reference system referenced by the projection text by projecting its coordinates.

This function utilizes the PROJ library for coordinate projections.

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

Returns a modified Geometry translated by the given deltas.

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

Returns a modified Geometry translated by the given deltas.

Immutable
st_transscale(geometry: geometry, delta_x: float, delta_y: float, x_factor: float, y_factor: float) → geometry

Translates the geometry using the deltaX and deltaY args, then scales it using the XFactor, YFactor args, working in 2D only.

Immutable
st_unaryunion(geometry: geometry) → geometry

Returns a union of the components for any geometry or geometry collection provided. Dissolves boundaries of a multipolygon.

Immutable
st_voronoilines(geometry: geometry) → geometry

Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry asthe boundaries between cells in that diagram as a MultiLineString.

Immutable
st_voronoilines(geometry: geometry, tolerance: float) → geometry

Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry asthe boundaries between cells in that diagram as a MultiLineString.

Immutable
st_voronoilines(geometry: geometry, tolerance: float, extend_to: geometry) → geometry

Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry asthe boundaries between cells in that diagram as a MultiLineString.

Immutable
st_voronoipolygons(geometry: geometry) → geometry

Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry.

Immutable
st_voronoipolygons(geometry: geometry, tolerance: float) → geometry

Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry.

Immutable
st_voronoipolygons(geometry: geometry, tolerance: float, extend_to: geometry) → geometry

Returns a two-dimensional Voronoi diagram from the vertices of the supplied geometry.

Immutable
st_within(geometry_a: geometry, geometry_b: geometry) → bool

Returns true if geometry_a is completely inside geometry_b.

This function utilizes the GEOS module.

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

Immutable
st_wkbtosql(val: bytes) → geometry

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

Immutable
st_wkttosql(val: string) → geometry

Returns the Geometry from a WKT or EWKT representation.

Immutable
st_x(geometry: geometry) → float

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

Immutable
st_xmax(box2d: box2d) → float

Returns the maximum X ordinate of a box2d.

Immutable
st_xmax(geometry: geometry) → float

Returns the maximum X ordinate of a geometry.

Immutable
st_xmin(box2d: box2d) → float

Returns the minimum X ordinate of a box2d.

Immutable
st_xmin(geometry: geometry) → float

Returns the minimum X ordinate of a geometry.

Immutable
st_y(geometry: geometry) → float

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

Immutable
st_ymax(box2d: box2d) → float

Returns the maximum Y ordinate of a box2d.

Immutable
st_ymax(geometry: geometry) → float

Returns the maximum Y ordinate of a geometry.

Immutable
st_ymin(box2d: box2d) → float

Returns the minimum Y ordinate of a box2d.

Immutable
st_ymin(geometry: geometry) → float

Returns the minimum Y ordinate of a geometry.

Immutable
st_z(geometry: geometry) → float

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

Immutable
st_zmflag(geometry: geometry) → int2

Returns a code based on the ZM coordinate dimension of a geometry (XY = 0, XYM = 1, XYZ = 2, XYZM = 3).

Immutable

String and byte functions

Function → ReturnsDescriptionVolatility
ascii(val: string) → int

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

Immutable
bit_count(val: bytes) → int

Calculates the number of bits set used to represent val.

Immutable
bit_count(val: varbit) → int

Calculates the number of bits set used to represent val.

Immutable
bit_length(val: bytes) → int

Calculates the number of bits used to represent val.

Immutable
bit_length(val: string) → int

Calculates the number of bits used to represent val.

Immutable
bit_length(val: varbit) → int

Calculates the number of bits used to represent val.

Immutable
bitmask_and(a: string, b: string) → varbit

Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_and(a: string, b: varbit) → varbit

Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_and(a: varbit, b: string) → varbit

Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_and(a: varbit, b: varbit) → varbit

Calculates bitwise AND value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_or(a: string, b: string) → varbit

Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_or(a: string, b: varbit) → varbit

Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_or(a: varbit, b: string) → varbit

Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_or(a: varbit, b: varbit) → varbit

Calculates bitwise OR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_xor(a: string, b: string) → varbit

Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_xor(a: string, b: varbit) → varbit

Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_xor(a: varbit, b: string) → varbit

Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

Immutable
bitmask_xor(a: varbit, b: varbit) → varbit

Calculates bitwise XOR value of unsigned bit arrays ‘a’ and ‘b’ that may have different lengths.

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

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

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

Immutable
btrim(val: string) → string

Removes all spaces from the beginning and end of val.

Immutable
char_length(val: bytes) → int

Calculates the number of bytes in val.

Immutable
char_length(val: string) → int

Calculates the number of characters in val.

Immutable
character_length(val: bytes) → int

Calculates the number of bytes in val.

Immutable
character_length(val: string) → int

Calculates the number of characters in val.

Immutable
chr(val: int) → string

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

Immutable
compress(data: bytes, codec: string) → bytes

Compress data with the specified codec (gzip, ‘lz4’, ‘snappy’, 'zstd).

Immutable
concat(string...) → string

Concatenates a comma-separated list of strings.

Immutable
concat_ws(string...) → string

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

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

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

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

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

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

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

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

Immutable
decompress(data: bytes, codec: string) → bytes

Decompress data with the specified codec (gzip, ‘lz4’, ‘snappy’, 'zstd).

Immutable
difference(source: string, target: string) → int

Convert two strings to their Soundex codes and report the number of matching code positions.

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

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

Immutable
format(string, anyelement...) → string

Interprets the first argument as a format string similar to C sprintf and interpolates the remaining arguments.

Stable
from_ip(val: bytes) → string

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

Immutable
from_uuid(val: bytes) → string

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

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

Extracts a bit at given index in the bit array.

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

Extracts a bit at the given index in the byte array.

Immutable
get_byte(byte_string: bytes, index: int) → int

Extracts a byte at the given index in the byte array.

Immutable
initcap(val: string) → string

Capitalizes the first letter of val.

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

Returns the first return_set bytes from input.

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

Returns the first return_set characters from input.

Immutable
length(val: bytes) → int

Calculates the number of bytes in val.

Immutable
length(val: string) → int

Calculates the number of characters in val.

Immutable
length(val: varbit) → int

Calculates the number of bits in val.

Immutable
lower(val: string) → string

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

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

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

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

Immutable
ltrim(val: string) → string

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

Immutable
md5(bytes...) → string

Calculates the MD5 hash value of a set of values.

Leakproof
md5(string...) → string

Calculates the MD5 hash value of a set of values.

Leakproof
octet_length(val: bytes) → int

Calculates the number of bytes used to represent val.

Immutable
octet_length(val: string) → int

Calculates the number of bytes used to represent val.

Immutable
octet_length(val: varbit) → int

Calculates the number of bits used to represent val.

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

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

Immutable
parse_date(string: string, datestyle: string) → date

Parses a date assuming it is in format specified by DateStyle.

Immutable
parse_date(val: string) → date

Parses a date assuming it is in MDY format.

Immutable
parse_ident(qualified_identifier: string) → string[]

Splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. Extra characters after the last identifier are considered an error

Immutable
parse_ident(qualified_identifier: string, strict: bool) → string[]

Splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. If strict is false, then extra characters after the last identifier are ignored.

Immutable
parse_interval(string: string, style: string) → interval

Convert a string to an interval using the given IntervalStyle.

Immutable
parse_interval(val: string) → interval

Convert a string to an interval assuming the Postgres IntervalStyle.

Immutable
parse_time(string: string, timestyle: string) → time

Parses a time assuming the date (if any) is in format specified by DateStyle.

Immutable
parse_time(val: string) → time

Parses a time assuming the date (if any) is in MDY format.

Immutable
parse_timestamp(string: string, datestyle: string) → timestamp

Convert a string containing an absolute timestamp to the corresponding timestamp assuming dates formatted using the given DateStyle.

Immutable
parse_timestamp(val: string) → timestamp

Convert a string containing an absolute timestamp to the corresponding timestamp assuming dates are in MDY format.

Immutable
parse_timetz(string: string, timestyle: string) → timetz

Parses a timetz assuming the date (if any) is in format specified by DateStyle.

Immutable
parse_timetz(val: string) → timetz

Parses a timetz assuming the date (if any) is in MDY format.

Immutable
prettify_statement(statement: string, line_width: int, align_mode: int, case_mode: int) → string

Prettifies a statement using a user-configured pretty-printing config. Align mode values range from 0 - 3, representing no, partial, full, and extra alignment respectively. Case mode values range between 0 - 1, representing lower casing and upper casing respectively.

Immutable
prettify_statement(val: string) → string

Prettifies a statement using a the default pretty-printing config.

Immutable
quote_ident(val: string) → string

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

Immutable
quote_literal(val: string) → string

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

Immutable
quote_literal(val: anyelement) → string

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

Stable
quote_nullable(val: string) → string

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

Immutable
quote_nullable(val: anyelement) → string

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

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

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

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

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

Immutable
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

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

Concatenates input repeat_counter number of times.

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

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

Replaces all occurrences of find with replace in input

Immutable
reverse(val: string) → string

Reverses the order of the string’s characters.

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

Returns the last return_set bytes from input.

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

Returns the last return_set characters from input.

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

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

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

Immutable
rtrim(val: string) → string

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

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

Updates a bit at given index in the bit array.

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

Updates a bit at the given index in the byte array.

Immutable
set_byte(byte_string: bytes, index: int, to_set: int) → bytes

Updates a byte at the given index in the byte array.

Immutable
sha1(bytes...) → string

Calculates the SHA1 hash value of a set of values.

Leakproof
sha1(string...) → string

Calculates the SHA1 hash value of a set of values.

Leakproof
sha224(bytes...) → string

Calculates the SHA224 hash value of a set of values.

Leakproof
sha224(string...) → string

Calculates the SHA224 hash value of a set of values.

Leakproof
sha256(bytes...) → string

Calculates the SHA256 hash value of a set of values.

Leakproof
sha256(string...) → string

Calculates the SHA256 hash value of a set of values.

Leakproof
sha384(bytes...) → string

Calculates the SHA384 hash value of a set of values.

Leakproof
sha384(string...) → string

Calculates the SHA384 hash value of a set of values.

Leakproof
sha512(bytes...) → string

Calculates the SHA512 hash value of a set of values.

Leakproof
sha512(string...) → string

Calculates the SHA512 hash value of a set of values.

Leakproof
similar_escape(pattern: string) → string

Converts a SQL regexp pattern to a POSIX regexp pattern.

Immutable
similar_escape(pattern: string, escape: string) → string

Converts a SQL regexp pattern to a POSIX regexp pattern using escape as an escape token.

Immutable
similar_to_escape(pattern: string) → string

Converts a SQL regexp pattern to a POSIX regexp pattern.

Immutable
similar_to_escape(pattern: string, escape: string) → string

Converts a SQL regexp pattern to a POSIX regexp pattern using escape as an escape token.

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

Matches unescaped with pattern using escape as an escape token.

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

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

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

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

Calculates the position where the string find begins in input.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Immutable
to_char_with_style(date: date, datestyle: string) → string

Convert an date to a string assuming the string is formatted using the given DateStyle.

Immutable
to_char_with_style(interval: interval, style: string) → string

Convert an interval to a string using the given IntervalStyle.

Immutable
to_char_with_style(timestamp: timestamp, datestyle: string) → string

Convert an timestamp to a string assuming the string is formatted using the given DateStyle.

Immutable
to_english(val: int) → string

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

Immutable
to_hex(val: bytes) → string

Converts val to its hexadecimal representation.

Immutable
to_hex(val: int) → string

Converts val to its hexadecimal representation.

Immutable
to_hex(val: string) → string

Converts val to its hexadecimal representation.

Immutable
to_ip(val: string) → bytes

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

Immutable
to_uuid(val: string) → bytes

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

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

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

Immutable
ulid_to_uuid(val: string) → uuid

Converts a ULID string to its UUID-encoded representation.

Immutable
unaccent(val: string) → string

Removes accents (diacritic signs) from the text provided in val.

Immutable
upper(val: string) → string

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

Immutable

System info functions

Function → ReturnsDescriptionVolatility
cluster_logical_timestamp() → decimal

Returns the logical time of the current transaction as a CockroachDB HLC in decimal form.

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

Returns an error if run in a transaction with an isolation level weaker than SERIALIZABLE.

Volatile
current_database() → string

Returns the current database.

Stable
current_schema() → string

Returns the current schema.

Stable
current_schemas(include_pg_catalog: bool) → string[]

Returns the valid schemas in the search path.

Stable
current_user() → string

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

Stable
session_user() → string

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

Stable
to_regclass(text: string) → regtype

Translates a textual relation name to its OID

Stable
to_regnamespace(text: string) → regtype

Translates a textual schema name to its OID

Stable
to_regproc(text: string) → regtype

Translates a textual function or procedure name to its OID

Stable
to_regprocedure(text: string) → regtype

Translates a textual function or procedure name(with argument types) to its OID

Stable
to_regrole(text: string) → regtype

Translates a textual role name to its OID

Stable
to_regtype(text: string) → regtype

Translates a textual type name to its OID

Stable
version() → string

Returns the node’s version of CockroachDB.

Volatile

TIMETZ functions

Function → ReturnsDescriptionVolatility
current_time() → time

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

Stable
current_time() → timetz

Returns the current transaction’s time with time zone.

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

Stable
current_time(precision: int) → time

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

Stable
current_time(precision: int) → timetz

Returns the current transaction’s time with time zone.

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

Stable
localtime() → time

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

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

Stable
localtime() → timetz

Returns the current transaction’s time with time zone.

Stable
localtime(precision: int) → time

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

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

Stable
localtime(precision: int) → timetz

Returns the current transaction’s time with time zone.

Stable

Trigrams functions

Function → ReturnsDescriptionVolatility
show_trgm(input: string) → string[]

Returns an array of all the trigrams in the given string.

Immutable
similarity(left: string, right: string) → float

Returns a number that indicates how similar the two arguments are. The range of the result is zero (indicating that the two strings are completely dissimilar) to one (indicating that the two strings are identical).

Immutable

UUID functions

Function → ReturnsDescriptionVolatility
uuid_to_ulid(val: uuid) → string

Converts a UUID-encoded ULID to its string representation.

Immutable

Compatibility functions

Function → ReturnsDescriptionVolatility
col_description(table_oid: oid, column_number: int) → string

Returns the comment for a table column, which is specified by the OID of its table and its column number. (obj_description cannot be used for table columns, since columns do not have OIDs of their own.)

Stable
current_setting(setting_name: string) → string

System info

Stable
current_setting(setting_name: string, missing_ok: bool) → string

System info

Stable
format_type(type_oid: oid, typemod: int) → string

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

Stable
getdatabaseencoding() → string

Returns the current encoding name used by the database.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

Returns whether or not the user has privileges for column.

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

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

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

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

Stable
has_database_privilege(user: string, database: string, privilege: string) → bool

Returns whether or not the user has privileges for database.

Stable
has_database_privilege(user: string, database: oid, privilege: string) → bool

Returns whether or not the user has privileges for database.

Stable
has_database_privilege(user: oid, database: string, privilege: string) → bool

Returns whether or not the user has privileges for database.

Stable
has_database_privilege(user: oid, database: oid, privilege: string) → bool

Returns whether or not the user has privileges for database.

Stable
has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool

Returns whether or not the current user has privileges for foreign-data wrapper.

Stable
has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool

Returns whether or not the current user has privileges for foreign-data wrapper.

Stable
has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

Stable
has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

Stable
has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

Stable
has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign-data wrapper.

Stable
has_function_privilege(function: string, privilege: string) → bool

Returns whether or not the current user has privileges for function.

Stable
has_function_privilege(function: oid, privilege: string) → bool

Returns whether or not the current user has privileges for function.

Stable
has_function_privilege(user: string, function: string, privilege: string) → bool

Returns whether or not the user has privileges for function.

Stable
has_function_privilege(user: string, function: oid, privilege: string) → bool

Returns whether or not the user has privileges for function.

Stable
has_function_privilege(user: oid, function: string, privilege: string) → bool

Returns whether or not the user has privileges for function.

Stable
has_function_privilege(user: oid, function: oid, privilege: string) → bool

Returns whether or not the user has privileges for function.

Stable
has_language_privilege(language: string, privilege: string) → bool

Returns whether or not the current user has privileges for language.

Stable
has_language_privilege(language: oid, privilege: string) → bool

Returns whether or not the current user has privileges for language.

Stable
has_language_privilege(user: string, language: string, privilege: string) → bool

Returns whether or not the user has privileges for language.

Stable
has_language_privilege(user: string, language: oid, privilege: string) → bool

Returns whether or not the user has privileges for language.

Stable
has_language_privilege(user: oid, language: string, privilege: string) → bool

Returns whether or not the user has privileges for language.

Stable
has_language_privilege(user: oid, language: oid, privilege: string) → bool

Returns whether or not the user has privileges for language.

Stable
has_schema_privilege(schema: string, privilege: string) → bool

Returns whether or not the current user has privileges for schema.

Stable
has_schema_privilege(schema: oid, privilege: string) → bool

Returns whether or not the current user has privileges for schema.

Stable
has_schema_privilege(user: string, schema: string, privilege: string) → bool

Returns whether or not the user has privileges for schema.

Stable
has_schema_privilege(user: string, schema: oid, privilege: string) → bool

Returns whether or not the user has privileges for schema.

Stable
has_schema_privilege(user: oid, schema: string, privilege: string) → bool

Returns whether or not the user has privileges for schema.

Stable
has_schema_privilege(user: oid, schema: oid, privilege: string) → bool

Returns whether or not the user has privileges for schema.

Stable
has_sequence_privilege(sequence: string, privilege: string) → bool

Returns whether or not the current user has privileges for sequence.

Stable
has_sequence_privilege(sequence: oid, privilege: string) → bool

Returns whether or not the current user has privileges for sequence.

Stable
has_sequence_privilege(user: string, sequence: string, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

Stable
has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

Stable
has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

Stable
has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool

Returns whether or not the user has privileges for sequence.

Stable
has_server_privilege(server: string, privilege: string) → bool

Returns whether or not the current user has privileges for foreign server.

Stable
has_server_privilege(server: oid, privilege: string) → bool

Returns whether or not the current user has privileges for foreign server.

Stable
has_server_privilege(user: string, server: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

Stable
has_server_privilege(user: string, server: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

Stable
has_server_privilege(user: oid, server: string, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

Stable
has_server_privilege(user: oid, server: oid, privilege: string) → bool

Returns whether or not the user has privileges for foreign server.

Stable
has_table_privilege(table: string, privilege: string) → bool

Returns whether or not the current user has privileges for table.

Stable
has_table_privilege(table: oid, privilege: string) → bool

Returns whether or not the current user has privileges for table.

Stable
has_table_privilege(user: string, table: string, privilege: string) → bool

Returns whether or not the user has privileges for table.

Stable
has_table_privilege(user: string, table: oid, privilege: string) → bool

Returns whether or not the user has privileges for table.

Stable
has_table_privilege(user: oid, table: string, privilege: string) → bool

Returns whether or not the user has privileges for table.

Stable
has_table_privilege(user: oid, table: oid, privilege: string) → bool

Returns whether or not the user has privileges for table.

Stable
has_tablespace_privilege(tablespace: string, privilege: string) → bool

Returns whether or not the current user has privileges for tablespace.

Stable
has_tablespace_privilege(tablespace: oid, privilege: string) → bool

Returns whether or not the current user has privileges for tablespace.

Stable
has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

Stable
has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

Stable
has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

Stable
has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool

Returns whether or not the user has privileges for tablespace.

Stable
has_type_privilege(type: string, privilege: string) → bool

Returns whether or not the current user has privileges for type.

Stable
has_type_privilege(type: oid, privilege: string) → bool

Returns whether or not the current user has privileges for type.

Stable
has_type_privilege(user: string, type: string, privilege: string) → bool

Returns whether or not the user has privileges for type.

Stable
has_type_privilege(user: string, type: oid, privilege: string) → bool

Returns whether or not the user has privileges for type.

Stable
has_type_privilege(user: oid, type: string, privilege: string) → bool

Returns whether or not the user has privileges for type.

Stable
has_type_privilege(user: oid, type: oid, privilege: string) → bool

Returns whether or not the user has privileges for type.

Stable
information_schema._pg_numeric_precision(typid: oid, typmod: int4) → int

Returns the precision of the given type with type modifier

Immutable
information_schema._pg_numeric_precision_radix(typid: oid, typmod: int4) → int

Returns the radix of the given type with type modifier

Immutable
information_schema._pg_numeric_scale(typid: oid, typmod: int4) → int

Returns the scale of the given type with type modifier

Immutable
nameconcatoid(name: string, oid: oid) → name

Used in the information_schema to produce specific_name columns, which are supposed to be unique per schema. The result is the same as ($1::text || ‘_’ || $2::text)::name except that, if it would not fit in 63 characters, we make it do so by truncating the name input (not the oid).

Immutable
obj_description(object_oid: oid) → string

Returns the comment for a database object specified by its OID alone. This is deprecated since there is no guarantee that OIDs are unique across different system catalogs; therefore, the wrong comment might be returned.

Stable
obj_description(object_oid: oid, catalog_name: string) → string

Returns the comment for a database object specified by its OID and the name of the containing system catalog. For example, obj_description(123456, ‘pg_class’) would retrieve the comment for the table with OID 123456.

Stable
oidvectortypes(vector: oidvector) → string

Generates a comma seperated string of type names from an oidvector.

Stable
pg_backend_pid() → int

Returns a numerical ID attached to this session. This ID is part of the query cancellation key used by the wire protocol. This function was only added for compatibility, and unlike in Postgres, the returned value does not correspond to a real process ID.

Stable
pg_collation_for(str: anyelement) → string

Returns the collation of the argument

Stable
pg_column_is_updatable(reloid: oid, attnum: int2, include_triggers: bool) → bool

Returns whether the given column can be updated.

Stable
pg_column_size(anyelement...) → int

Return size in bytes of the column provided as an argument

Immutable
pg_function_is_visible(oid: oid) → bool

Returns whether the function with the given OID belongs to one of the schemas on the search path.

Stable
pg_get_function_arg_default(func_oid: oid, arg_num: int4) → string

Get textual representation of a function argument’s default value. The second argument of this function is the argument number among all arguments (i.e. proallargtypes, not proargtypes), starting with 1, because that’s how information_schema.sql uses it. Currently, this always returns NULL, since CockroachDB does not support default values.

Stable
pg_get_function_arguments(func_oid: oid) → string

Returns the argument list (with defaults) necessary to identify a function, in the form it would need to appear in within CREATE FUNCTION.

Stable
pg_get_function_identity_arguments(func_oid: oid) → string

Returns the argument list (without defaults) necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance.

Stable
pg_get_function_result(func_oid: oid) → string

Returns the types of the result of the specified function.

Stable
pg_get_functiondef(func_oid: oid) → string

For user-defined functions, returns the definition of the specified function. For builtin functions, returns the name of the function.

Stable
pg_get_indexdef(index_oid: oid) → string

Gets the CREATE INDEX command for index

Stable
pg_get_indexdef(index_oid: oid, column_no: int, pretty_bool: bool) → string

Gets the CREATE INDEX command for index, or definition of just one index column when given a non-zero column number

Stable
pg_get_serial_sequence(table_name: string, column_name: string) → string

Returns the name of the sequence used by the given column_name in the table table_name.

Stable
pg_get_viewdef(view_oid: oid) → string

Returns the CREATE statement for an existing view.

Stable
pg_get_viewdef(view_oid: oid, pretty_bool: bool) → string

Returns the CREATE statement for an existing view.

Stable
pg_has_role(role: string, privilege: string) → bool

Returns whether or not the current user has privileges for role.

Stable
pg_has_role(role: oid, privilege: string) → bool

Returns whether or not the current user has privileges for role.

Stable
pg_has_role(user: string, role: string, privilege: string) → bool

Returns whether or not the user has privileges for role.

Stable
pg_has_role(user: string, role: oid, privilege: string) → bool

Returns whether or not the user has privileges for role.

Stable
pg_has_role(user: oid, role: string, privilege: string) → bool

Returns whether or not the user has privileges for role.

Stable
pg_has_role(user: oid, role: oid, privilege: string) → bool

Returns whether or not the user has privileges for role.

Stable
pg_is_other_temp_schema(oid: oid) → bool

Returns true if the given OID is the OID of another session’s temporary schema. (This can be useful, for example, to exclude other sessions’ temporary tables from a catalog display.)

Stable
pg_my_temp_schema() → oid

Returns the OID of the current session’s temporary schema, or zero if it has none (because it has not created any temporary tables).

Stable
pg_relation_is_updatable(reloid: oid, include_triggers: bool) → int4

Returns the update events the relation supports.

Stable
pg_sequence_last_value(sequence_oid: oid) → int

Returns the last value generated by a sequence, or NULL if the sequence has not been used yet.

Volatile
pg_sleep(seconds: float) → bool

pg_sleep makes the current session’s process sleep until seconds seconds have elapsed. seconds is a value of type double precision, so fractional-second delays can be specified.

Volatile
pg_table_is_visible(oid: oid) → bool

Returns whether the table with the given OID belongs to one of the schemas on the search path.

Stable
pg_type_is_visible(oid: oid) → bool

Returns whether the type with the given OID belongs to one of the schemas on the search path.

Stable
set_config(setting_name: string, new_value: string, is_local: bool) → string

System info

Volatile
shobj_description(object_oid: oid, catalog_name: string) → string

Returns the comment for a shared database object specified by its OID and the name of the containing system catalog. This is just like obj_description except that it is used for retrieving comments on shared objects (e.g. databases).

Stable

Aggregate functions

For examples showing how to use aggregate functions, see the SELECT clause documentation.

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 → ReturnsDescriptionVolatility
array_agg(arg1: bool) → bool[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: bool[]) → bool[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: bytes) → bytes[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: bytes[]) → bytes[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: date) → date[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: date[]) → date[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: decimal) → decimal[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: decimal[]) → decimal[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: float) → float[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: float[]) → float[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: inet) → inet[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: inet[]) → inet[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: int) → int[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: int[]) → int[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: interval) → interval[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: interval[]) → interval[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: string) → string[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: string[]) → string[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: time) → time[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: time[]) → time[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: timestamp) → timestamp[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: timestamp[]) → timestamp[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: timestamptz) → timestamptz[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: timestamptz[]) → timestamptz[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: uuid) → uuid[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: uuid[]) → uuid[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: anyenum) → anyenum[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: anyenum[]) → anyenum[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: box2d) → box2d[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: box2d[]) → box2d[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: geography) → geography[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: geography[]) → geography[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: geometry) → geometry[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: geometry[]) → geometry[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: jsonb) → jsonb[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: jsonb[]) → jsonb[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: oid) → oid[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: oid[]) → oid[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: pg_lsn) → pg_lsn[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: pg_lsn[]) → pg_lsn[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: refcursor) → refcursor[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: refcursor[]) → refcursor[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: timetz) → timetz[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: timetz[]) → timetz[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: tuple) → tuple[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: tuple[]) → tuple[][]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: varbit) → varbit[]

Aggregates the selected values into an array.

Immutable
array_agg(arg1: varbit[]) → varbit[][]

Aggregates the selected values into an array.

Immutable
array_cat_agg(arg1: bool[]) → bool[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: bytes[]) → bytes[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: date[]) → date[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: decimal[]) → decimal[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: float[]) → float[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: inet[]) → inet[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: int[]) → int[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: interval[]) → interval[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: string[]) → string[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: time[]) → time[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: timestamp[]) → timestamp[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: timestamptz[]) → timestamptz[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: uuid[]) → uuid[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: anyenum[]) → anyenum[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: box2d[]) → box2d[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: geography[]) → geography[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: geometry[]) → geometry[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: jsonb[]) → jsonb[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: oid[]) → oid[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: pg_lsn[]) → pg_lsn[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: refcursor[]) → refcursor[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: timetz[]) → timetz[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: tuple[]) → tuple[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
array_cat_agg(arg1: varbit[]) → varbit[]

Unnests the selected arrays into elements that are then aggregated into a single array.

Immutable
avg(arg1: decimal) → decimal

Calculates the average of the selected values.

Immutable
avg(arg1: float) → float

Calculates the average of the selected values.

Immutable
avg(arg1: int) → decimal

Calculates the average of the selected values.

Immutable
avg(arg1: interval) → interval

Calculates the average of the selected values.

Immutable
bit_and(arg1: int) → int

Calculates the bitwise AND of all non-null input values, or null if none.

Immutable
bit_and(arg1: varbit) → varbit

Calculates the bitwise AND of all non-null input values, or null if none.

Immutable
bit_or(arg1: int) → int

Calculates the bitwise OR of all non-null input values, or null if none.

Immutable
bit_or(arg1: varbit) → varbit

Calculates the bitwise OR of all non-null input values, or null if none.

Immutable
bool_and(arg1: bool) → bool

Calculates the boolean value of ANDing all selected values.

Immutable
bool_or(arg1: bool) → bool

Calculates the boolean value of ORing all selected values.

Immutable
concat_agg(arg1: bytes) → bytes

Concatenates all selected values.

Immutable
concat_agg(arg1: string) → string

Concatenates all selected values.

Immutable
corr(arg1: decimal, arg2: decimal) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: decimal, arg2: float) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: decimal, arg2: int) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: float, arg2: decimal) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: float, arg2: float) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: float, arg2: int) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: int, arg2: decimal) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: int, arg2: float) → float

Calculates the correlation coefficient of the selected values.

Immutable
corr(arg1: int, arg2: int) → float

Calculates the correlation coefficient of the selected values.

Immutable
count(arg1: anyelement) → int

Calculates the number of selected elements.

Immutable
count_rows() → int

Calculates the number of rows.

Immutable
covar_pop(arg1: decimal, arg2: decimal) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: decimal, arg2: float) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: decimal, arg2: int) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: float, arg2: decimal) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: float, arg2: float) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: float, arg2: int) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: int, arg2: decimal) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: int, arg2: float) → float

Calculates the population covariance of the selected values.

Immutable
covar_pop(arg1: int, arg2: int) → float

Calculates the population covariance of the selected values.

Immutable
covar_samp(arg1: decimal, arg2: decimal) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: decimal, arg2: float) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: decimal, arg2: int) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: float, arg2: decimal) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: float, arg2: float) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: float, arg2: int) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: int, arg2: decimal) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: int, arg2: float) → float

Calculates the sample covariance of the selected values.

Immutable
covar_samp(arg1: int, arg2: int) → float

Calculates the sample covariance of the selected values.

Immutable
every(arg1: bool) → bool

Calculates the boolean value of ANDing all selected values.

Immutable
json_agg(arg1: anyelement) → jsonb

Aggregates values as a JSON or JSONB array.

Stable
json_object_agg(arg1: string, arg2: anyelement) → jsonb

Aggregates values as a JSON or JSONB object.

Stable
jsonb_agg(arg1: anyelement) → jsonb

Aggregates values as a JSON or JSONB array.

Stable
jsonb_object_agg(arg1: string, arg2: anyelement) → jsonb

Aggregates values as a JSON or JSONB object.

Stable
max(arg1: bool) → bool

Identifies the maximum selected value.

Immutable
max(arg1: bytes) → bytes

Identifies the maximum selected value.

Immutable
max(arg1: date) → date

Identifies the maximum selected value.

Immutable
max(arg1: decimal) → decimal

Identifies the maximum selected value.

Immutable
max(arg1: float) → float

Identifies the maximum selected value.

Immutable
max(arg1: inet) → inet

Identifies the maximum selected value.

Immutable
max(arg1: int) → int

Identifies the maximum selected value.

Immutable
max(arg1: interval) → interval

Identifies the maximum selected value.

Immutable
max(arg1: string) → string

Identifies the maximum selected value.

Immutable
max(arg1: time) → time

Identifies the maximum selected value.

Immutable
max(arg1: timestamp) → timestamp

Identifies the maximum selected value.

Immutable
max(arg1: timestamptz) → timestamptz

Identifies the maximum selected value.

Immutable
max(arg1: uuid) → uuid

Identifies the maximum selected value.

Immutable
max(arg1: anyenum) → anyenum

Identifies the maximum selected value.

Immutable
max(arg1: box2d) → box2d

Identifies the maximum selected value.

Immutable
max(arg1: collatedstring{*}) → collatedstring{*}

Identifies the maximum selected value.

Immutable
max(arg1: geography) → geography

Identifies the maximum selected value.

Immutable
max(arg1: geometry) → geometry

Identifies the maximum selected value.

Immutable
max(arg1: jsonb) → jsonb

Identifies the maximum selected value.

Immutable
max(arg1: oid) → oid

Identifies the maximum selected value.

Immutable
max(arg1: pg_lsn) → pg_lsn

Identifies the maximum selected value.

Immutable
max(arg1: timetz) → timetz

Identifies the maximum selected value.

Immutable
max(arg1: varbit) → varbit

Identifies the maximum selected value.

Immutable
min(arg1: bool) → bool

Identifies the minimum selected value.

Immutable
min(arg1: bytes) → bytes

Identifies the minimum selected value.

Immutable
min(arg1: date) → date

Identifies the minimum selected value.

Immutable
min(arg1: decimal) → decimal

Identifies the minimum selected value.

Immutable
min(arg1: float) → float

Identifies the minimum selected value.

Immutable
min(arg1: inet) → inet

Identifies the minimum selected value.

Immutable
min(arg1: int) → int

Identifies the minimum selected value.

Immutable
min(arg1: interval) → interval

Identifies the minimum selected value.

Immutable
min(arg1: string) → string

Identifies the minimum selected value.

Immutable
min(arg1: time) → time

Identifies the minimum selected value.

Immutable
min(arg1: timestamp) → timestamp

Identifies the minimum selected value.

Immutable
min(arg1: timestamptz) → timestamptz

Identifies the minimum selected value.

Immutable
min(arg1: uuid) → uuid

Identifies the minimum selected value.

Immutable
min(arg1: anyenum) → anyenum

Identifies the minimum selected value.

Immutable
min(arg1: box2d) → box2d

Identifies the minimum selected value.

Immutable
min(arg1: collatedstring{*}) → collatedstring{*}

Identifies the minimum selected value.

Immutable
min(arg1: geography) → geography

Identifies the minimum selected value.

Immutable
min(arg1: geometry) → geometry

Identifies the minimum selected value.

Immutable
min(arg1: jsonb) → jsonb

Identifies the minimum selected value.

Immutable
min(arg1: oid) → oid

Identifies the minimum selected value.

Immutable
min(arg1: pg_lsn) → pg_lsn

Identifies the minimum selected value.

Immutable
min(arg1: timetz) → timetz

Identifies the minimum selected value.

Immutable
min(arg1: varbit) → varbit

Identifies the minimum selected value.

Immutable
percentile_cont(arg1: float) → float

Continuous percentile: returns a float corresponding to the specified fraction in the ordering, interpolating between adjacent input floats if needed.

Immutable
percentile_cont(arg1: float) → interval

Continuous percentile: returns an interval corresponding to the specified fraction in the ordering, interpolating between adjacent input intervals if needed.

Immutable
percentile_cont(arg1: float[]) → float[]

Continuous percentile: returns floats corresponding to the specified fractions in the ordering, interpolating between adjacent input floats if needed.

Immutable
percentile_cont(arg1: float[]) → interval[]

Continuous percentile: returns intervals corresponding to the specified fractions in the ordering, interpolating between adjacent input intervals if needed.

Immutable
percentile_disc(arg1: float) → anyelement

Discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction.

Immutable
percentile_disc(arg1: float[]) → anyelement

Discrete percentile: returns input values whose position in the ordering equals or exceeds the specified fractions.

Immutable
regr_avgx(arg1: decimal, arg2: decimal) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: decimal, arg2: float) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: decimal, arg2: int) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: float, arg2: decimal) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: float, arg2: float) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: float, arg2: int) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: int, arg2: decimal) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: int, arg2: float) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgx(arg1: int, arg2: int) → float

Calculates the average of the independent variable (sum(X)/N).

Immutable
regr_avgy(arg1: decimal, arg2: decimal) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: decimal, arg2: float) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: decimal, arg2: int) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: float, arg2: decimal) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: float, arg2: float) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: float, arg2: int) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: int, arg2: decimal) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: int, arg2: float) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_avgy(arg1: int, arg2: int) → float

Calculates the average of the dependent variable (sum(Y)/N).

Immutable
regr_count(arg1: decimal, arg2: decimal) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: decimal, arg2: float) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: decimal, arg2: int) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: float, arg2: decimal) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: float, arg2: float) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: float, arg2: int) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: int, arg2: decimal) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: int, arg2: float) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_count(arg1: int, arg2: int) → int

Calculates number of input rows in which both expressions are nonnull.

Immutable
regr_intercept(arg1: decimal, arg2: decimal) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: decimal, arg2: float) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: decimal, arg2: int) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: float, arg2: decimal) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: float, arg2: float) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: float, arg2: int) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: int, arg2: decimal) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: int, arg2: float) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_intercept(arg1: int, arg2: int) → float

Calculates y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_r2(arg1: decimal, arg2: decimal) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: decimal, arg2: float) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: decimal, arg2: int) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: float, arg2: decimal) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: float, arg2: float) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: float, arg2: int) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: int, arg2: decimal) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: int, arg2: float) → float

Calculates square of the correlation coefficient.

Immutable
regr_r2(arg1: int, arg2: int) → float

Calculates square of the correlation coefficient.

Immutable
regr_slope(arg1: decimal, arg2: decimal) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: decimal, arg2: float) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: decimal, arg2: int) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: float, arg2: decimal) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: float, arg2: float) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: float, arg2: int) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: int, arg2: decimal) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: int, arg2: float) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_slope(arg1: int, arg2: int) → float

Calculates slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Immutable
regr_sxx(arg1: decimal, arg2: decimal) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: decimal, arg2: float) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: decimal, arg2: int) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: float, arg2: decimal) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: float, arg2: float) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: float, arg2: int) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: int, arg2: decimal) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: int, arg2: float) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxx(arg1: int, arg2: int) → float

Calculates sum of squares of the independent variable.

Immutable
regr_sxy(arg1: decimal, arg2: decimal) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: decimal, arg2: float) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: decimal, arg2: int) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: float, arg2: decimal) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: float, arg2: float) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: float, arg2: int) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: int, arg2: decimal) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: int, arg2: float) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_sxy(arg1: int, arg2: int) → float

Calculates sum of products of independent times dependent variable.

Immutable
regr_syy(arg1: decimal, arg2: decimal) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: decimal, arg2: float) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: decimal, arg2: int) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: float, arg2: decimal) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: float, arg2: float) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: float, arg2: int) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: int, arg2: decimal) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: int, arg2: float) → float

Calculates sum of squares of the dependent variable.

Immutable
regr_syy(arg1: int, arg2: int) → float

Calculates sum of squares of the dependent variable.

Immutable
sqrdiff(arg1: decimal) → decimal

Calculates the sum of squared differences from the mean of the selected values.

Immutable
sqrdiff(arg1: float) → float

Calculates the sum of squared differences from the mean of the selected values.

Immutable
sqrdiff(arg1: int) → decimal

Calculates the sum of squared differences from the mean of the selected values.

Immutable
st_collect(arg1: geometry) → geometry

Collects geometries into a GeometryCollection or multi-type as appropriate.

Immutable
st_extent(arg1: geometry) → box2d

Forms a Box2D that encapsulates all provided geometries.

Immutable
st_makeline(arg1: geometry) → geometry

Forms a LineString from Point, MultiPoint or LineStrings. Other shapes will be ignored.

Immutable
st_memcollect(arg1: geometry) → geometry

Collects geometries into a GeometryCollection or multi-type as appropriate.

Immutable
st_memunion(arg1: geometry) → geometry

Applies a spatial union to the geometries provided.

Immutable
st_union(arg1: geometry) → geometry

Applies a spatial union to the geometries provided.

Immutable
stddev(arg1: decimal) → decimal

Calculates the standard deviation of the selected values.

Immutable
stddev(arg1: float) → float

Calculates the standard deviation of the selected values.

Immutable
stddev(arg1: int) → decimal

Calculates the standard deviation of the selected values.

Immutable
stddev_pop(arg1: decimal) → decimal

Calculates the population standard deviation of the selected values.

Immutable
stddev_pop(arg1: float) → float

Calculates the population standard deviation of the selected values.

Immutable
stddev_pop(arg1: int) → decimal

Calculates the population standard deviation of the selected values.

Immutable
stddev_samp(arg1: decimal) → decimal

Calculates the standard deviation of the selected values.

Immutable
stddev_samp(arg1: float) → float

Calculates the standard deviation of the selected values.

Immutable
stddev_samp(arg1: int) → decimal

Calculates the standard deviation of the selected values.

Immutable
string_agg(arg1: bytes, arg2: bytes) → bytes

Concatenates all selected values using the provided delimiter.

Immutable
string_agg(arg1: string, arg2: string) → string

Concatenates all selected values using the provided delimiter.

Immutable
sum(arg1: decimal) → decimal

Calculates the sum of the selected values.

Immutable
sum(arg1: float) → float

Calculates the sum of the selected values.

Immutable
sum(arg1: int) → decimal

Calculates the sum of the selected values.

Immutable
sum(arg1: interval) → interval

Calculates the sum of the selected values.

Immutable
sum_int(arg1: int) → int

Calculates the sum of the selected values.

Immutable
var_pop(arg1: decimal) → decimal

Calculates the population variance of the selected values.

Immutable
var_pop(arg1: float) → float

Calculates the population variance of the selected values.

Immutable
var_pop(arg1: int) → decimal

Calculates the population variance of the selected values.

Immutable
var_samp(arg1: decimal) → decimal

Calculates the variance of the selected values.

Immutable
var_samp(arg1: float) → float

Calculates the variance of the selected values.

Immutable
var_samp(arg1: int) → decimal

Calculates the variance of the selected values.

Immutable
variance(arg1: decimal) → decimal

Calculates the variance of the selected values.

Immutable
variance(arg1: float) → float

Calculates the variance of the selected values.

Immutable
variance(arg1: int) → decimal

Calculates the variance of the selected values.

Immutable
xor_agg(arg1: bytes) → bytes

Calculates the bitwise XOR of the selected values.

Immutable
xor_agg(arg1: int) → int

Calculates the bitwise XOR of the selected values.

Immutable

Window functions

Function → ReturnsDescriptionVolatility
cume_dist() → float

Calculates the relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).

Immutable
dense_rank() → int

Calculates the rank of the current row without gaps; this function counts peer groups.

Immutable
first_value(val: bool) → bool

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: bytes) → bytes

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: date) → date

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: decimal) → decimal

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: float) → float

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: inet) → inet

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: int) → int

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: interval) → interval

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: string) → string

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: time) → time

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: timestamp) → timestamp

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: timestamptz) → timestamptz

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: uuid) → uuid

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: box2d) → box2d

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: geography) → geography

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: geometry) → geometry

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: jsonb) → jsonb

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: oid) → oid

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: pg_lsn) → pg_lsn

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: refcursor) → refcursor

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: timetz) → timetz

Returns val evaluated at the row that is the first row of the window frame.

Immutable
first_value(val: varbit) → varbit

Returns val evaluated at the row that is the first row of the window frame.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
lag(val: pg_lsn) → pg_lsn

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

Immutable
lag(val: pg_lsn, n: int) → pg_lsn

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.

Immutable
lag(val: pg_lsn, n: int, default: pg_lsn) → pg_lsn

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.

Immutable
lag(val: refcursor) → refcursor

Returns val evaluated at the previous row within current row’s partition; if there is no such row, instead returns null.

Immutable
lag(val: refcursor, n: int) → refcursor

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.

Immutable
lag(val: refcursor, n: int, default: refcursor) → refcursor

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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
last_value(val: bool) → bool

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: bytes) → bytes

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: date) → date

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: decimal) → decimal

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: float) → float

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: inet) → inet

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: int) → int

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: interval) → interval

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: string) → string

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: time) → time

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: timestamp) → timestamp

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: timestamptz) → timestamptz

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: uuid) → uuid

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: box2d) → box2d

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: geography) → geography

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: geometry) → geometry

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: jsonb) → jsonb

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: oid) → oid

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: pg_lsn) → pg_lsn

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: refcursor) → refcursor

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: timetz) → timetz

Returns val evaluated at the row that is the last row of the window frame.

Immutable
last_value(val: varbit) → varbit

Returns val evaluated at the row that is the last row of the window frame.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
lead(val: pg_lsn) → pg_lsn

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

Immutable
lead(val: pg_lsn, n: int) → pg_lsn

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.

Immutable
lead(val: pg_lsn, n: int, default: pg_lsn) → pg_lsn

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.

Immutable
lead(val: refcursor) → refcursor

Returns val evaluated at the following row within current row’s partition; if there is no such row, instead returns null.

Immutable
lead(val: refcursor, n: int) → refcursor

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.

Immutable
lead(val: refcursor, n: int, default: refcursor) → refcursor

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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
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.

Immutable
nth_value(val: pg_lsn, n: int) → pg_lsn

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

Immutable
nth_value(val: refcursor, n: int) → refcursor

Returns val evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

Immutable
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.

Immutable
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.

Immutable
ntile(n: int) → int

Calculates an integer ranging from 1 to n, dividing the partition as equally as possible.

Immutable
percent_rank() → float

Calculates the relative rank of the current row: (rank - 1) / (total rows - 1).

Immutable
rank() → int

Calculates the rank of the current row with gaps; same as row_number of its first peer.

Immutable
row_number() → int

Calculates the number of the current row within its partition, counting from 1.

Immutable

Operators

The following table lists all CockroachDB operators from highest to lowest precedence, i.e., the order in which they will be evaluated within a statement. Operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right.

Order of Precedence Operator Name Operator Arity
1 . Member field access operator binary
2 :: Type cast binary
3 - Unary minus unary (prefix)
~ Bitwise not unary (prefix)
4 ^ Exponentiation binary
5 * Multiplication binary
/ Division binary
// Floor division binary
% Modulo, or text similarity when accessing a STRING column. For more information, see Trigram Indexes. binary
6 + Addition binary
- Subtraction binary
7 << Bitwise left-shift binary
>> Bitwise right-shift binary
&& Overlaps binary
8 & Bitwise AND binary
9 # Bitwise XOR binary
10 | Bitwise OR binary
11 || Concatenation binary
< ANY, SOME, ALL Multi-valued "less than" comparison binary
> ANY, SOME, ALL Multi-valued "greater than" comparison binary
= ANY, SOME, ALL Multi-valued "equal" comparison binary
<= ANY, SOME, ALL Multi-valued "less than or equal" comparison binary
>= ANY, SOME, ALL Multi-valued "greater than or equal" comparison binary
<> ANY / != ANY, <> SOME / != SOME, <> ALL / != ALL Multi-valued "not equal" comparison binary
[NOT] LIKE ANY, [NOT] LIKE SOME, [NOT] LIKE ALL Multi-valued LIKE comparison binary
[NOT] ILIKE ANY, [NOT] ILIKE SOME, [NOT] ILIKE ALL Multi-valued ILIKE comparison binary
-> Access a JSONB field, returning a JSONB value. binary
->> Access a JSONB field, returning a string. binary
@> Tests whether the left JSONB or array field contains the right JSONB or array field. binary
>@ Tests whether the left JSONB or array field is contained by the right JSONB or array field. binary
#> Access a JSONB field at the specified path, returning a JSONB value. binary
#>> Access a JSONB field at the specified path, returning a string. binary
? Does the key or element string exist within the JSONB value? binary
?& Do all the key or element strings exist within the JSONB value? binary
?| Do any of the key or element strings exist within the JSONB value? binary
12 [NOT] BETWEEN Value is [not] within the range specified binary
[NOT] BETWEEN SYMMETRIC Like [NOT] BETWEEN, but in non-sorted order. For example, whereas a BETWEEN b AND c means b <= a <= c, a BETWEEN SYMMETRIC b AND c means (b <= a <= c) OR (c <= a <= b). binary
[NOT] IN Value is [not] in the set of values specified binary
[NOT] LIKE Matches [or not] LIKE expression, case sensitive binary
[NOT] ILIKE Matches [or not] LIKE expression, case insensitive binary
[NOT] SIMILAR Matches [or not] SIMILAR TO regular expression binary
~ Matches regular expression, case sensitive binary
!~ Does not match regular expression, case sensitive binary
~* Matches regular expression, case insensitive binary
!~* Does not match regular expression, case insensitive binary
13 = Equal binary
< Less than binary
> Greater than binary
<= Less than or equal to binary
>= Greater than or equal to binary
!=, <> Not equal binary
14 IS [DISTINCT FROM] Equal, considering NULL as value binary
IS NOT [DISTINCT FROM] a IS NOT b equivalent to NOT (a IS b) binary
ISNULL, IS UNKNOWN , NOTNULL, IS NOT UNKNOWN Equivalent to IS NULL / IS NOT NULL unary (postfix)
IS NAN, IS NOT NAN Comparison with the floating-point NaN value unary (postfix)
IS OF(...) Type predicate unary (postfix)
15 NOT Logical NOT unary
16 AND Logical AND binary
17 OR Logical OR binary

Supported operations

#Return
int # intint
varbit # varbitvarbit
#>Return
jsonb #> string[]jsonb
#>>Return
jsonb #>> string[]string
%Return
decimal % decimaldecimal
decimal % intdecimal
float % floatfloat
int % decimaldecimal
int % intint
string % stringbool
&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
vector * vectorvector
+Return
+decimaldecimal
+floatfloat
+intint
+intervalinterval
date + intdate
date + intervaltimestamp
date + timetimestamp
date + timetztimestamptz
decimal + decimaldecimal
decimal + intdecimal
decimal + pg_lsnpg_lsn
float + floatfloat
inet + intinet
int + datedate
int + decimaldecimal
int + inetinet
int + intint
interval + datetimestamp
interval + intervalinterval
interval + timetime
interval + timestamptimestamp
interval + timestamptztimestamptz
interval + timetztimetz
pg_lsn + decimalpg_lsn
time + datetimestamp
time + intervaltime
timestamp + intervaltimestamp
timestamptz + intervaltimestamptz
timetz + datetimestamptz
timetz + intervaltimetz
vector + vectorvector
-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
pg_lsn - decimalpg_lsn
pg_lsn - pg_lsndecimal
time - intervaltime
time - timeinterval
timestamp - intervaltimestamp
timestamp - timestampinterval
timestamp - timestamptzinterval
timestamptz - intervaltimestamptz
timestamptz - timestampinterval
timestamptz - timestamptzinterval
timetz - intervaltimetz
vector - vectorvector
->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
bpchar < bpcharbool
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
pg_lsn < pg_lsnbool
refcursor < refcursorbool
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
vector < vectorbool
<#>Return
vector <#> vectorfloat
<->Return
vector <-> vectorfloat
<<Return
inet << inetbool
int << intint
varbit << intvarbit
<=Return
anyenum <= anyenumbool
bool <= boolbool
bool[] <= bool[]bool
box2d <= box2dbool
bpchar <= bpcharbool
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
pg_lsn <= pg_lsnbool
refcursor <= refcursorbool
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
vector <= vectorbool
<=>Return
vector <=> vectorfloat
<@Return
anyelement <@ anyelementbool
jsonb <@ jsonbbool
=Return
anyenum = anyenumbool
bool = boolbool
bool[] = bool[]bool
box2d = box2dbool
bpchar = bpcharbool
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
pg_lsn = pg_lsnbool
refcursor = refcursorbool
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
tsquery = tsquerybool
tsvector = tsvectorbool
tuple = tuplebool
uuid = uuidbool
uuid[] = uuid[]bool
varbit = varbitbool
vector = vectorbool
>>Return
inet >> inetbool
int >> intint
varbit >> intvarbit
?Return
jsonb ? stringbool
?&Return
jsonb ?& string[]bool
?|Return
jsonb ?| string[]bool
@>Return
anyelement @> anyelementbool
jsonb @> jsonbbool
@@Return
tsquery @@ tsvectorbool
tsvector @@ tsquerybool
ILIKEReturn
string ILIKE stringbool
INReturn
anyenum IN tuplebool
bool IN tuplebool
box2d IN tuplebool
bpchar 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
pg_lsn IN tuplebool
refcursor 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
vector IN tuplebool
IS NOT DISTINCT FROMReturn
anyelement IS NOT DISTINCT FROM unknownbool
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
bpchar IS NOT DISTINCT FROM bpcharbool
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
pg_lsn IS NOT DISTINCT FROM pg_lsnbool
refcursor IS NOT DISTINCT FROM refcursorbool
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
tsquery IS NOT DISTINCT FROM tsquerybool
tsvector IS NOT DISTINCT FROM tsvectorbool
tuple IS NOT DISTINCT FROM tuplebool
unknown IS NOT DISTINCT FROM unknownbool
unknown IS NOT DISTINCT FROM voidbool
uuid IS NOT DISTINCT FROM uuidbool
uuid[] IS NOT DISTINCT FROM uuid[]bool
varbit IS NOT DISTINCT FROM varbitbool
vector IS NOT DISTINCT FROM vectorbool
void IS NOT DISTINCT FROM unknownbool
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 || stringstring
bool[] || boolbool[]
bool[] || bool[]bool[]
box2d || box2dbox2d
box2d || stringstring
bytes || bytesbytes
bytes || bytes[]bytes[]
bytes[] || bytesbytes[]
bytes[] || bytes[]bytes[]
date || date[]date[]
date || stringstring
date[] || datedate[]
date[] || date[]date[]
decimal || decimal[]decimal[]
decimal || stringstring
decimal[] || decimaldecimal[]
decimal[] || decimal[]decimal[]
float || float[]float[]
float || stringstring
float[] || floatfloat[]
float[] || float[]float[]
geography || geographygeography
geography || stringstring
geometry || geometrygeometry
geometry || stringstring
inet || inet[]inet[]
inet || stringstring
inet[] || inetinet[]
inet[] || inet[]inet[]
int || int[]int[]
int || stringstring
int[] || intint[]
int[] || int[]int[]
interval || interval[]interval[]
interval || stringstring
interval[] || intervalinterval[]
interval[] || interval[]interval[]
jsonb || jsonbjsonb
jsonb || stringstring
oid || oidoid
oid || stringstring
pg_lsn || pg_lsnpg_lsn
pg_lsn || stringstring
refcursor || refcursorrefcursor
refcursor || stringstring
string || boolstring
string || box2dstring
string || datestring
string || decimalstring
string || floatstring
string || geographystring
string || geometrystring
string || inetstring
string || intstring
string || intervalstring
string || jsonbstring
string || oidstring
string || pg_lsnstring
string || refcursorstring
string || stringstring
string || string[]string[]
string || timestring
string || timestampstring
string || timestamptzstring
string || timetzstring
string || tuplestring
string || uuidstring
string || varbitstring
string[] || stringstring[]
string[] || string[]string[]
time || stringstring
time || time[]time[]
time[] || timetime[]
time[] || time[]time[]
timestamp || stringstring
timestamp || timestamp[]timestamp[]
timestamp[] || timestamptimestamp[]
timestamp[] || timestamp[]timestamp[]
timestamptz || stringstring
timestamptz || timestamptztimestamptz
timestamptz || timestamptztimestamptz
timestamptz || timestamptztimestamptz
timetz || stringstring
timetz || timetztimetz
tuple || stringstring
uuid || stringstring
uuid || uuid[]uuid[]
uuid[] || uuiduuid[]
uuid[] || uuid[]uuid[]
varbit || stringstring
varbit || varbitvarbit
||/Return
||/decimaldecimal
||/floatfloat
~Return
~inetinet
~intint
~varbitvarbit
box2d ~ box2dbool
box2d ~ geometrybool
geometry ~ box2dbool
geometry ~ geometrybool
string ~ stringbool
~*Return
string ~* stringbool

Yes No
On this page

Yes No