CockroachDB supports the following SQL functions and operators for use in scalar expressions.
Special syntax forms
The following syntax forms are recognized for compatibility with the SQL standard and PostgreSQL, but are equivalent to regular builtin functions:
Special form  Equivalent to 

AT TIME ZONE 
timezone() 
CURRENT_CATALOG 
current_catalog() 
COLLATION FOR 
pg_collation_for() 
CURRENT_DATE 
current_date() 
CURRENT_ROLE 
current_user() 
CURRENT_SCHEMA 
current_schema() 
CURRENT_TIMESTAMP 
current_timestamp() 
CURRENT_TIME 
current_time() 
CURRENT_USER 
current_user() 
EXTRACT(<part> FROM <value>) 
extract("<part>", <value>) 
EXTRACT_DURATION(<part> FROM <value>) 
extract_duration("<part>", <value>) 
OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>) 
overlay(<text1>, <text2>, <int1>, <int2>) 
OVERLAY(<text1> PLACING <text2> FROM <int>) 
overlay(<text1>, <text2>, <int>) 
POSITION(<text1> IN <text2>) 
strpos(<text2>, <text1>) 
SESSION_USER 
current_user() 
SUBSTRING(<text> FOR <int1> FROM <int2>) 
substring(<text>, <int2>, <int1>) 
SUBSTRING(<text> FOR <int>) 
substring(<text>, 1, <int>) 
SUBSTRING(<text> FROM <int1> FOR <int2>) 
substring(<text>, <int1>, <int2>) 
SUBSTRING(<text> FROM <int>) 
substring(<text>, <int>) 
TRIM(<text1> FROM <text2>) 
btrim(<text2>, <text1>) 
TRIM(<text1>, <text2>) 
btrim(<text1>, <text2>) 
TRIM(FROM <text>) 
btrim(<text>) 
TRIM(LEADING <text1> FROM <text2>) 
ltrim(<text2>, <text1>) 
TRIM(LEADING FROM <text>) 
ltrim(<text>) 
TRIM(TRAILING <text1> FROM <text2>) 
rtrim(<text2>, <text1>) 
TRIM(TRAILING FROM <text>) 
rtrim(<text>) 
USER 
current_user() 
Conditional and functionlike operators
The following table lists the operators that look like builtin 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 nonNULL 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 
Builtin functions
Array functions
Function → Returns  Description 

array_append(array: bool[], elem: bool) → bool[]  Appends 
array_append(array: bytes[], elem: bytes) → bytes[]  Appends 
array_append(array: date[], elem: date) → date[]  Appends 
array_append(array: decimal[], elem: decimal) → decimal[]  Appends 
array_append(array: float[], elem: float) → float[]  Appends 
array_append(array: inet[], elem: inet) → inet[]  Appends 
array_append(array: int[], elem: int) → int[]  Appends 
array_append(array: interval[], elem: interval) → interval[]  Appends 
array_append(array: string[], elem: string) → string[]  Appends 
array_append(array: time[], elem: time) → time[]  Appends 
array_append(array: timestamp[], elem: timestamp) → timestamp[]  Appends 
array_append(array: timestamptz[], elem: timestamptz) → timestamptz[]  Appends 
array_append(array: uuid[], elem: uuid) → uuid[]  Appends 
array_append(array: box2d[], elem: box2d) → box2d[]  Appends 
array_append(array: geography[], elem: geography) → geography[]  Appends 
array_append(array: geometry[], elem: geometry) → geometry[]  Appends 
array_append(array: oid[], elem: oid) → oid[]  Appends 
array_append(array: timetz[], elem: timetz) → timetz[]  Appends 
array_append(array: varbit[], elem: varbit) → varbit[]  Appends 
array_cat(left: bool[], right: bool[]) → bool[]  Appends two arrays. 
array_cat(left: bytes[], right: bytes[]) → bytes[]  Appends two arrays. 
array_cat(left: date[], right: date[]) → date[]  Appends two arrays. 
array_cat(left: decimal[], right: decimal[]) → decimal[]  Appends two arrays. 
array_cat(left: float[], right: float[]) → float[]  Appends two arrays. 
array_cat(left: inet[], right: inet[]) → inet[]  Appends two arrays. 
array_cat(left: int[], right: int[]) → int[]  Appends two arrays. 
array_cat(left: interval[], right: interval[]) → interval[]  Appends two arrays. 
array_cat(left: string[], right: string[]) → string[]  Appends two arrays. 
array_cat(left: time[], right: time[]) → time[]  Appends two arrays. 
array_cat(left: timestamp[], right: timestamp[]) → timestamp[]  Appends two arrays. 
array_cat(left: timestamptz[], right: timestamptz[]) → timestamptz[]  Appends two arrays. 
array_cat(left: uuid[], right: uuid[]) → uuid[]  Appends two arrays. 
array_cat(left: box2d[], right: box2d[]) → box2d[]  Appends two arrays. 
array_cat(left: geography[], right: geography[]) → geography[]  Appends two arrays. 
array_cat(left: geometry[], right: geometry[]) → geometry[]  Appends two arrays. 
array_cat(left: oid[], right: oid[]) → oid[]  Appends two arrays. 
array_cat(left: timetz[], right: timetz[]) → timetz[]  Appends two arrays. 
array_cat(left: varbit[], right: varbit[]) → varbit[]  Appends two arrays. 
array_length(input: anyelement[], array_dimension: int) → int  Calculates the length of 
array_lower(input: anyelement[], array_dimension: int) → int  Calculates the minimum value of 
array_position(array: bool[], elem: bool) → int  Return the index of the first occurrence of 
array_position(array: bytes[], elem: bytes) → int  Return the index of the first occurrence of 
array_position(array: date[], elem: date) → int  Return the index of the first occurrence of 
array_position(array: decimal[], elem: decimal) → int  Return the index of the first occurrence of 
array_position(array: float[], elem: float) → int  Return the index of the first occurrence of 
array_position(array: inet[], elem: inet) → int  Return the index of the first occurrence of 
array_position(array: int[], elem: int) → int  Return the index of the first occurrence of 
array_position(array: interval[], elem: interval) → int  Return the index of the first occurrence of 
array_position(array: string[], elem: string) → int  Return the index of the first occurrence of 
array_position(array: time[], elem: time) → int  Return the index of the first occurrence of 
array_position(array: timestamp[], elem: timestamp) → int  Return the index of the first occurrence of 
array_position(array: timestamptz[], elem: timestamptz) → int  Return the index of the first occurrence of 
array_position(array: uuid[], elem: uuid) → int  Return the index of the first occurrence of 
array_position(array: box2d[], elem: box2d) → int  Return the index of the first occurrence of 
array_position(array: geography[], elem: geography) → int  Return the index of the first occurrence of 
array_position(array: geometry[], elem: geometry) → int  Return the index of the first occurrence of 
array_position(array: oid[], elem: oid) → int  Return the index of the first occurrence of 
array_position(array: timetz[], elem: timetz) → int  Return the index of the first occurrence of 
array_position(array: varbit[], elem: varbit) → int  Return the index of the first occurrence of 
array_positions(array: bool[], elem: bool) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: bytes[], elem: bytes) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: date[], elem: date) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: decimal[], elem: decimal) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: float[], elem: float) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: inet[], elem: inet) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: int[], elem: int) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: interval[], elem: interval) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: string[], elem: string) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: time[], elem: time) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: timestamp[], elem: timestamp) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: timestamptz[], elem: timestamptz) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: uuid[], elem: uuid) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: box2d[], elem: box2d) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: geography[], elem: geography) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: geometry[], elem: geometry) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: oid[], elem: oid) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: timetz[], elem: timetz) → int[]  Returns and array of indexes of all occurrences of 
array_positions(array: varbit[], elem: varbit) → int[]  Returns and array of indexes of all occurrences of 
array_prepend(elem: bool, array: bool[]) → bool[]  Prepends 
array_prepend(elem: bytes, array: bytes[]) → bytes[]  Prepends 
array_prepend(elem: date, array: date[]) → date[]  Prepends 
array_prepend(elem: decimal, array: decimal[]) → decimal[]  Prepends 
array_prepend(elem: float, array: float[]) → float[]  Prepends 
array_prepend(elem: inet, array: inet[]) → inet[]  Prepends 
array_prepend(elem: int, array: int[]) → int[]  Prepends 
array_prepend(elem: interval, array: interval[]) → interval[]  Prepends 
array_prepend(elem: string, array: string[]) → string[]  Prepends 
array_prepend(elem: time, array: time[]) → time[]  Prepends 
array_prepend(elem: timestamp, array: timestamp[]) → timestamp[]  Prepends 
array_prepend(elem: timestamptz, array: timestamptz[]) → timestamptz[]  Prepends 
array_prepend(elem: uuid, array: uuid[]) → uuid[]  Prepends 
array_prepend(elem: box2d, array: box2d[]) → box2d[]  Prepends 
array_prepend(elem: geography, array: geography[]) → geography[]  Prepends 
array_prepend(elem: geometry, array: geometry[]) → geometry[]  Prepends 
array_prepend(elem: oid, array: oid[]) → oid[]  Prepends 
array_prepend(elem: timetz, array: timetz[]) → timetz[]  Prepends 
array_prepend(elem: varbit, array: varbit[]) → varbit[]  Prepends 
array_remove(array: bool[], elem: bool) → bool[]  Remove from 
array_remove(array: bytes[], elem: bytes) → bytes[]  Remove from 
array_remove(array: date[], elem: date) → date[]  Remove from 
array_remove(array: decimal[], elem: decimal) → decimal[]  Remove from 
array_remove(array: float[], elem: float) → float[]  Remove from 
array_remove(array: inet[], elem: inet) → inet[]  Remove from 
array_remove(array: int[], elem: int) → int[]  Remove from 
array_remove(array: interval[], elem: interval) → interval[]  Remove from 
array_remove(array: string[], elem: string) → string[]  Remove from 
array_remove(array: time[], elem: time) → time[]  Remove from 
array_remove(array: timestamp[], elem: timestamp) → timestamp[]  Remove from 
array_remove(array: timestamptz[], elem: timestamptz) → timestamptz[]  Remove from 
array_remove(array: uuid[], elem: uuid) → uuid[]  Remove from 
array_remove(array: box2d[], elem: box2d) → box2d[]  Remove from 
array_remove(array: geography[], elem: geography) → geography[]  Remove from 
array_remove(array: geometry[], elem: geometry) → geometry[]  Remove from 
array_remove(array: oid[], elem: oid) → oid[]  Remove from 
array_remove(array: timetz[], elem: timetz) → timetz[]  Remove from 
array_remove(array: varbit[], elem: varbit) → varbit[]  Remove from 
array_replace(array: bool[], toreplace: bool, replacewith: bool) → bool[]  Replace all occurrences of 
array_replace(array: bytes[], toreplace: bytes, replacewith: bytes) → bytes[]  Replace all occurrences of 
array_replace(array: date[], toreplace: date, replacewith: date) → date[]  Replace all occurrences of 
array_replace(array: decimal[], toreplace: decimal, replacewith: decimal) → decimal[]  Replace all occurrences of 
array_replace(array: float[], toreplace: float, replacewith: float) → float[]  Replace all occurrences of 
array_replace(array: inet[], toreplace: inet, replacewith: inet) → inet[]  Replace all occurrences of 
array_replace(array: int[], toreplace: int, replacewith: int) → int[]  Replace all occurrences of 
array_replace(array: interval[], toreplace: interval, replacewith: interval) → interval[]  Replace all occurrences of 
array_replace(array: string[], toreplace: string, replacewith: string) → string[]  Replace all occurrences of 
array_replace(array: time[], toreplace: time, replacewith: time) → time[]  Replace all occurrences of 
array_replace(array: timestamp[], toreplace: timestamp, replacewith: timestamp) → timestamp[]  Replace all occurrences of 
array_replace(array: timestamptz[], toreplace: timestamptz, replacewith: timestamptz) → timestamptz[]  Replace all occurrences of 
array_replace(array: uuid[], toreplace: uuid, replacewith: uuid) → uuid[]  Replace all occurrences of 
array_replace(array: box2d[], toreplace: box2d, replacewith: box2d) → box2d[]  Replace all occurrences of 
array_replace(array: geography[], toreplace: geography, replacewith: geography) → geography[]  Replace all occurrences of 
array_replace(array: geometry[], toreplace: geometry, replacewith: geometry) → geometry[]  Replace all occurrences of 
array_replace(array: oid[], toreplace: oid, replacewith: oid) → oid[]  Replace all occurrences of 
array_replace(array: timetz[], toreplace: timetz, replacewith: timetz) → timetz[]  Replace all occurrences of 
array_replace(array: varbit[], toreplace: varbit, replacewith: varbit) → varbit[]  Replace all occurrences of 
array_to_string(input: anyelement[], delim: string) → string  Join an array into a string with a delimiter. 
array_to_string(input: anyelement[], delimiter: string, null: string) → string  Join an array into a string with a delimiter, replacing NULLs with a null string. 
array_upper(input: anyelement[], array_dimension: int) → int  Calculates the maximum value of 
string_to_array(str: string, delimiter: string) → string[]  Split a string into components on a delimiter. 
string_to_array(str: string, delimiter: string, null: string) → string[]  Split a string into components on a delimiter with a specified string to consider NULL. 
BOOL functions
Function → Returns  Description 

ilike_escape(unescaped: string, pattern: string, escape: string) → bool  Matches case insensetively 
inet_contained_by_or_equals(val: inet, container: inet) → bool  Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored. 
inet_contains_or_equals(container: inet, val: inet) → bool  Test for subnet inclusion or equality, using only the network parts of the addresses. The host part of the addresses is ignored. 
inet_same_family(val: inet, val: inet) → bool  Checks if two IP addresses are of the same IP family. 
like_escape(unescaped: string, pattern: string, escape: string) → bool  Matches 
not_ilike_escape(unescaped: string, pattern: string, escape: string) → bool  Checks whether 
not_like_escape(unescaped: string, pattern: string, escape: string) → bool  Checks whether 
not_similar_to_escape(unescaped: string, pattern: string, escape: string) → bool  Checks whether 
similar_to_escape(unescaped: string, pattern: string, escape: string) → bool  Matches 
Comparison functions
Function → Returns  Description 

greatest(anyelement...) → anyelement  Returns the element with the greatest value. 
least(anyelement...) → anyelement  Returns the element with the lowest value. 
num_nonnulls(anyelement...) → int  Returns the number of nonnull arguments. 
num_nulls(anyelement...) → int  Returns the number of null arguments. 
Date and time functions
Function → Returns  Description 

age(end: timestamptz, begin: timestamptz) → interval  Calculates the interval between 
age(val: timestamptz) → interval  Calculates the interval between 
clock_timestamp() → timestamp  Returns the current system time on one of the cluster nodes. 
clock_timestamp() → timestamptz  Returns the current system time on one of the cluster nodes. 
current_date() → date  Returns the date of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
current_timestamp() → date  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
current_timestamp() → timestamp  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
current_timestamp() → timestamptz  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. 
current_timestamp(precision: int) → date  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
current_timestamp(precision: int) → timestamp  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
current_timestamp(precision: int) → timestamptz  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. 
date_trunc(element: string, input: date) → timestamptz  Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. 
date_trunc(element: string, input: time) → interval  Truncates Compatible elements: hour, minute, second, millisecond, microsecond. 
date_trunc(element: string, input: timestamp) → timestamp  Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. 
date_trunc(element: string, input: timestamptz) → timestamptz  Truncates Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. 
experimental_follower_read_timestamp() → timestamptz  Same as follower_read_timestamp. This name is deprecated. 
experimental_strftime(input: date, extract_format: string) → string  From 
experimental_strftime(input: timestamp, extract_format: string) → string  From 
experimental_strftime(input: timestamptz, extract_format: string) → string  From 
experimental_strptime(input: string, format: string) → timestamptz  Returns 
extract(element: string, input: date) → float  Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch 
extract(element: string, input: interval) → float  Extracts Compatible elements: millennium, century, decade, year, month, day, hour, minute, second, millisecond, microsecond, epoch 
extract(element: string, input: time) → float  Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch 
extract(element: string, input: timestamp) → float  Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch 
extract(element: string, input: timestamptz) → float  Extracts Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute 
extract(element: string, input: timetz) → float  Extracts Compatible elements: hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute 
extract_duration(element: string, input: interval) → int  Extracts 
follower_read_timestamp() → timestamptz  Returns a timestamp which is very likely to be safe to perform against a follower replica. This function is intended to be used with an AS OF SYSTEM TIME clause to perform historical reads against a time which is recent but sufficiently old for reads to be performed against the closest replica as opposed to the currently leaseholder for a given range. Note that this function requires an enterprise license on a CCL distribution to return without an error. 
localtimestamp() → date  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
localtimestamp() → timestamp  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. 
localtimestamp() → timestamptz  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
localtimestamp(precision: int) → date  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
localtimestamp(precision: int) → timestamp  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. 
localtimestamp(precision: int) → timestamptz  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
now() → date  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
now() → timestamp  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
now() → timestamptz  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. 
statement_timestamp() → timestamp  Returns the start time of the current statement. 
statement_timestamp() → timestamptz  Returns the start time of the current statement. 
timeofday() → string  Returns the current system time on one of the cluster nodes as a string. 
timezone(timezone: string, time: time) → timetz  Treat given time without time zone as located in the specified time zone. 
timezone(timezone: string, timestamp: timestamp) → timestamptz  Treat given time stamp without time zone as located in the specified time zone. 
timezone(timezone: string, timestamptz: timestamptz) → timestamp  Convert given time stamp with time zone to the new time zone, with no time zone designation. 
timezone(timezone: string, timestamptz_string: string) → timestamp  Convert given time stamp with time zone to the new time zone, with no time zone designation. 
timezone(timezone: string, timetz: timetz) → timetz  Convert given time with time zone to the new time zone. 
transaction_timestamp() → date  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
transaction_timestamp() → timestamp  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. 
transaction_timestamp() → timestamptz  Returns the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. This function is the preferred overload and will be evaluated by default. 
Enum functions
FLOAT functions
Function → Returns  Description 

abs(val: decimal) → decimal  Calculates the absolute value of 
abs(val: float) → float  Calculates the absolute value of 
abs(val: int) → int  Calculates the absolute value of 
acos(val: float) → float  Calculates the inverse cosine of 
acosd(val: float) → float  Calculates the inverse cosine of 
acosh(val: float) → float  Calculates the inverse hyperbolic cosine of 
asin(val: float) → float  Calculates the inverse sine of 
asind(val: float) → float  Calculates the inverse sine of 
asinh(val: float) → float  Calculates the inverse hyperbolic sine of 
atan(val: float) → float  Calculates the inverse tangent of 
atan2(x: float, y: float) → float  Calculates the inverse tangent of 
atan2d(x: float, y: float) → float  Calculates the inverse tangent of 
atand(val: float) → float  Calculates the inverse tangent of 
atanh(val: float) → float  Calculates the inverse hyperbolic tangent of 
cbrt(val: decimal) → decimal  Calculates the cube root (∛) of 
cbrt(val: float) → float  Calculates the cube root (∛) of 
ceil(val: decimal) → decimal  Calculates the smallest integer not smaller than 
ceil(val: float) → float  Calculates the smallest integer not smaller than 
ceil(val: int) → float  Calculates the smallest integer not smaller than 
ceiling(val: decimal) → decimal  Calculates the smallest integer not smaller than 
ceiling(val: float) → float  Calculates the smallest integer not smaller than 
ceiling(val: int) → float  Calculates the smallest integer not smaller than 
cos(val: float) → float  Calculates the cosine of 
cosd(val: float) → float  Calculates the cosine of 
cosh(val: float) → float  Calculates the hyperbolic cosine of 
cot(val: float) → float  Calculates the cotangent of 
cotd(val: float) → float  Calculates the cotangent of 
degrees(val: float) → float  Converts 
div(x: decimal, y: decimal) → decimal  Calculates the integer quotient of 
div(x: float, y: float) → float  Calculates the integer quotient of 
div(x: int, y: int) → int  Calculates the integer quotient of 
exp(val: decimal) → decimal  Calculates e ^ 
exp(val: float) → float  Calculates e ^ 
floor(val: decimal) → decimal  Calculates the largest integer not greater than 
floor(val: float) → float  Calculates the largest integer not greater than 
floor(val: int) → float  Calculates the largest integer not greater than 
isnan(val: decimal) → bool  Returns true if 
isnan(val: float) → bool  Returns true if 
ln(val: decimal) → decimal  Calculates the natural log of 
ln(val: float) → float  Calculates the natural log of 
log(b: decimal, x: decimal) → decimal  Calculates the base 
log(b: float, x: float) → float  Calculates the base 
log(val: decimal) → decimal  Calculates the base 10 log of 
log(val: float) → float  Calculates the base 10 log of 
mod(x: decimal, y: decimal) → decimal  Calculates 
mod(x: float, y: float) → float  Calculates 
mod(x: int, y: int) → int  Calculates 
pi() → float  Returns the value for pi (3.141592653589793). 
pow(x: decimal, y: decimal) → decimal  Calculates 
pow(x: float, y: float) → float  Calculates 
pow(x: int, y: int) → int  Calculates 
power(x: decimal, y: decimal) → decimal  Calculates 
power(x: float, y: float) → float  Calculates 
power(x: int, y: int) → int  Calculates 
radians(val: float) → float  Converts 
random() → float  Returns a random float between 0 and 1. 
round(input: decimal, decimal_accuracy: int) → decimal  Keeps 
round(input: float, decimal_accuracy: int) → float  Keeps 
round(val: decimal) → decimal  Rounds 
round(val: float) → float  Rounds 
sign(val: decimal) → decimal  Determines the sign of 
sign(val: float) → float  Determines the sign of 
sign(val: int) → int  Determines the sign of 
sin(val: float) → float  Calculates the sine of 
sind(val: float) → float  Calculates the sine of 
sinh(val: float) → float  Calculates the hyperbolic sine of 
sqrt(val: decimal) → decimal  Calculates the square root of 
sqrt(val: float) → float  Calculates the square root of 
tan(val: float) → float  Calculates the tangent of 
tand(val: float) → float  Calculates the tangent of 
tanh(val: float) → float  Calculates the hyperbolic tangent of 
trunc(val: decimal) → decimal  Truncates the decimal values of 
trunc(val: float) → float  Truncates the decimal values of 
ID generation functions
Function → Returns  Description 

experimental_uuid_v4() → bytes  Returns a UUID. 
gen_random_uuid() → uuid  Generates a random UUID and returns it as a value of UUID type. 
unique_rowid() → int  Returns a unique ID used by CockroachDB to generate unique row IDs if a Primary Key isn’t defined for the table. The value is a combination of the insert timestamp and the ID of the node executing the statement, which guarantees this combination is globally unique. However, there can be gaps and the order is not completely guaranteed. 
uuid_v4() → bytes  Returns a UUID. 
INET functions
Function → Returns  Description 

abbrev(val: inet) → string  Converts the combined IP address and prefix length to an abbreviated display format as text.For INET types, this will omit the prefix length if it’s not the default (32 or IPv4, 128 for IPv6) For example, 
broadcast(val: inet) → inet  Gets the broadcast address for the network address represented by the value. For example, 
family(val: inet) → int  Extracts the IP family of the value; 4 for IPv4, 6 for IPv6. For example, 
host(val: inet) → string  Extracts the address part of the combined address/prefixlen value as text. For example, 
hostmask(val: inet) → inet  Creates an IP host mask corresponding to the prefix length in the value. For example, 
masklen(val: inet) → int  Retrieves the prefix length stored in the value. For example, 
netmask(val: inet) → inet  Creates an IP network mask corresponding to the prefix length in the value. For example, 
set_masklen(val: inet, prefixlen: int) → inet  Sets the prefix length of For example, 
text(val: inet) → string  Converts the IP address and prefix length to text. 
INT functions
Function → Returns  Description 

crc32c(bytes...) → int  Calculates the CRC32 hash using the Castagnoli polynomial. 
crc32c(string...) → int  Calculates the CRC32 hash using the Castagnoli polynomial. 
crc32ieee(bytes...) → int  Calculates the CRC32 hash using the IEEE polynomial. 
crc32ieee(string...) → int  Calculates the CRC32 hash using the IEEE polynomial. 
fnv32(bytes...) → int  Calculates the 32bit FNV1 hash value of a set of values. 
fnv32(string...) → int  Calculates the 32bit FNV1 hash value of a set of values. 
fnv32a(bytes...) → int  Calculates the 32bit FNV1a hash value of a set of values. 
fnv32a(string...) → int  Calculates the 32bit FNV1a hash value of a set of values. 
fnv64(bytes...) → int  Calculates the 64bit FNV1 hash value of a set of values. 
fnv64(string...) → int  Calculates the 64bit FNV1 hash value of a set of values. 
fnv64a(bytes...) → int  Calculates the 64bit FNV1a hash value of a set of values. 
fnv64a(string...) → int  Calculates the 64bit FNV1a hash value of a set of values. 
width_bucket(operand: decimal, b1: decimal, b2: decimal, count: int) → int  return the bucket number to which operand would be assigned in a histogram having count equalwidth buckets spanning the range b1 to b2. 
width_bucket(operand: int, b1: int, b2: int, count: int) → int  return the bucket number to which operand would be assigned in a histogram having count equalwidth buckets spanning the range b1 to b2. 
width_bucket(operand: anyelement, thresholds: anyelement[]) → int  return the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained 
JSONB functions
Function → Returns  Description 

array_to_json(array: anyelement[]) → jsonb  Returns the array as JSON or JSONB. 
array_to_json(array: anyelement[], pretty_bool: bool) → jsonb  Returns the array as JSON or JSONB. 
crdb_internal.json_to_pb(pbname: string, json: jsonb) → bytes  Convert JSONB data to protocol message bytes 
crdb_internal.pb_to_json(pbname: string, data: bytes) → jsonb  Converts protocol message to its JSONB representation. 
json_array_length(json: jsonb) → int  Returns the number of elements in the outermost JSON or JSONB array. 
json_build_array(anyelement...) → jsonb  Builds a possiblyheterogeneouslytyped JSON or JSONB array out of a variadic argument list. 
json_build_object(anyelement...) → jsonb  Builds a JSON object out of a variadic argument list. 
json_extract_path(jsonb, string...) → jsonb  Returns the JSON value pointed to by the variadic arguments. 
json_object(keys: string[], values: string[]) → jsonb  This form of json_object takes keys and values pairwise from two separate arrays. In all other respects it is identical to the oneargument form. 
json_object(texts: string[]) → jsonb  Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs. 
json_remove_path(val: jsonb, path: string[]) → jsonb  Remove the specified path from the JSON object. 
json_set(val: jsonb, path: string[], to: jsonb) → jsonb  Returns the JSON value pointed to by the variadic arguments. 
json_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb  Returns the JSON value pointed to by the variadic arguments. If 
json_strip_nulls(from_json: jsonb) → jsonb  Returns from_json with all object fields that have null values omitted. Other null values are untouched. 
json_typeof(val: jsonb) → string  Returns the type of the outermost JSON value as a text string. 
jsonb_array_length(json: jsonb) → int  Returns the number of elements in the outermost JSON or JSONB array. 
jsonb_build_array(anyelement...) → jsonb  Builds a possiblyheterogeneouslytyped JSON or JSONB array out of a variadic argument list. 
jsonb_build_object(anyelement...) → jsonb  Builds a JSON object out of a variadic argument list. 
jsonb_extract_path(jsonb, string...) → jsonb  Returns the JSON value pointed to by the variadic arguments. 
jsonb_insert(target: jsonb, path: string[], new_val: jsonb) → jsonb  Returns the JSON value pointed to by the variadic arguments. 
jsonb_insert(target: jsonb, path: string[], new_val: jsonb, insert_after: bool) → jsonb  Returns the JSON value pointed to by the variadic arguments. If 
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 oneargument form. 
jsonb_object(texts: string[]) → jsonb  Builds a JSON or JSONB object out of a text array. The array must have exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs. 
jsonb_pretty(val: jsonb) → string  Returns the given JSON value as a STRING indented and with newlines. 
jsonb_set(val: jsonb, path: string[], to: jsonb) → jsonb  Returns the JSON value pointed to by the variadic arguments. 
jsonb_set(val: jsonb, path: string[], to: jsonb, create_missing: bool) → jsonb  Returns the JSON value pointed to by the variadic arguments. If 
jsonb_strip_nulls(from_json: jsonb) → jsonb  Returns from_json with all object fields that have null values omitted. Other null values are untouched. 
jsonb_typeof(val: jsonb) → string  Returns the type of the outermost JSON value as a text string. 
to_json(val: anyelement) → jsonb  Returns the value as JSON or JSONB. 
to_jsonb(val: anyelement) → jsonb  Returns the value as JSON or JSONB. 
Multitenancy functions
Function → Returns  Description 

crdb_internal.sql_liveness_is_alive(session_id: bytes) → bool  Checks is given sqlliveness session id is not expired 
STRING[] functions
Function → Returns  Description  

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

Sequence functions
Function → Returns  Description 

currval(sequence_name: string) → int  Returns the latest value obtained with nextval for this sequence in this session. 
lastval() → int  Return value most recently obtained with nextval in this session. 
nextval(sequence_name: string) → int  Advances the given sequence and returns its new value. 
pg_get_serial_sequence(table_name: string, column_name: string) → string  Returns the name of the sequence used by the given column_name in the table table_name. 
setval(sequence_name: string, value: int) → int  Set the given sequence’s current value. The next call to nextval will return 
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 
Setreturning functions
Function → Returns  Description  

aclexplode(aclitems: string[]) → tuple{oid AS grantor, oid AS grantee, string AS privilege_type, bool AS is_grantable}  Produces a virtual table containing aclitem stuff (returns no rows as this feature is unsupported in CockroachDB)  
crdb_internal.testing_callback(name: string) → int  For internal CRDB testing only. The function calls a callback identified by  
crdb_internal.unary_table() → tuple  Produces a virtual table containing a single row with no values. This function is used only by CockroachDB’s developers for testing purposes.  
generate_series(start: int, end: int) → int  Produces a virtual table containing the integer values from  
generate_series(start: int, end: int, step: int) → int  Produces a virtual table containing the integer values from  
generate_series(start: timestamp, end: timestamp, step: interval) → timestamp  Produces a virtual table containing the timestamp values from  
generate_subscripts(array: anyelement[]) → int  Returns a series comprising the given array’s subscripts.  
generate_subscripts(array: anyelement[], dim: int) → int  Returns a series comprising the given array’s subscripts.  
generate_subscripts(array: anyelement[], dim: int, reverse: bool) → int  Returns a series comprising the given array’s subscripts. When reverse is true, the series is returned in reverse order.  
information_schema._pg_expandarray(input: anyelement[]) → anyelement  Returns the input array as a set of rows with an index  
json_array_elements(input: jsonb) → jsonb  Expands a JSON array to a set of JSON values.  
json_array_elements_text(input: jsonb) → string  Expands a JSON array to a set of text values.  
json_each(input: jsonb) → tuple{string AS key, jsonb AS value}  Expands the outermost JSON or JSONB object into a set of key/value pairs.  
json_each_text(input: jsonb) → tuple{string AS key, string AS value}  Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.  
json_object_keys(input: jsonb) → string  Returns sorted set of keys in the outermost JSON object.  
jsonb_array_elements(input: jsonb) → jsonb  Expands a JSON array to a set of JSON values.  
jsonb_array_elements_text(input: jsonb) → string  Expands a JSON array to a set of text values.  
jsonb_each(input: jsonb) → tuple{string AS key, jsonb AS value}  Expands the outermost JSON or JSONB object into a set of key/value pairs.  
jsonb_each_text(input: jsonb) → tuple{string AS key, string AS value}  Expands the outermost JSON or JSONB object into a set of key/value pairs. The returned values will be of type text.  
jsonb_object_keys(input: jsonb) → string  Returns sorted set of keys in the outermost JSON object.  
pg_get_keywords() → tuple{string AS word, string AS catcode, string AS catdesc}  Produces a virtual table containing the keywords known to the SQL parser.  
regexp_split_to_table(string: string, pattern: string) → string  Split string using a POSIX regular expression as the delimiter.  
regexp_split_to_table(string: string, pattern: string, flags: string) → string  Split string using a POSIX regular expression as the delimiter with flags. CockroachDB supports the following flags:
 
unnest(anyelement[], anyelement[], anyelement[]...) → tuple  Returns the input arrays as a set of rows  
unnest(input: anyelement[]) → anyelement  Returns the input array as a set of rows 
Spatial functions
Function → Returns  Description 

_st_contains(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if no points of geometry_b lie in the exterior of geometry_a, and there is at least one point in the interior of geometry_b that lies in the interior of geometry_a. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_containsproperly(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_coveredby(geography_a: geography, geography_b: geography) → bool  Returns true if no point in geography_a is outside geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant does not utilize any spatial index. 
_st_coveredby(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if no point in geometry_a is outside geometry_b. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_covers(geography_a: geography, geography_b: geography) → bool  Returns true if no point in geography_b is outside geography_a. This function utilizes the S2 library for spherical calculations. This function variant does not utilize any spatial index. 
_st_covers(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if no point in geometry_b is outside geometry_a. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_crosses(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a has some  but not all  interior points in common with geometry_b. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_dfullywithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, inclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than or equal to distance units. This function variant does not utilize any spatial index. 
_st_dfullywithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, exclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than distance units. This function variant does not utilize any spatial index. 
_st_dwithin(geography_a: geography, geography_b: geography, distance: float) → bool  Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. 
_st_dwithin(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool  Returns true if any of geography_a is within distance meters of geography_b, inclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. 
_st_dwithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if any of geometry_a is within distance units of geometry_b, inclusive. This function variant does not utilize any spatial index. 
_st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float) → bool  Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. 
_st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool  Returns true if any of geography_a is within distance meters of geography_b, exclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant does not utilize any spatial index. 
_st_dwithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if any of geometry_a is within distance units of geometry_b, exclusive. This function variant does not utilize any spatial index. 
_st_equals(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a is spatially equal to geometry_b, i.e. ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = true. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_intersects(geography_a: geography, geography_b: geography) → bool  Returns true if geography_a shares any portion of space with geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant does not utilize any spatial index. 
_st_intersects(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a shares any portion of space with geometry_b. The calculations performed are have a precision of 1cm. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_overlaps(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a intersects but does not completely contain geometry_b, or vice versa. “Does not completely” implies ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = false. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_touches(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if the only points in common between geometry_a and geometry_b are on the boundary. Note points do not touch other points. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
_st_within(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a is completely inside geometry_b. This function utilizes the GEOS module. This function variant does not utilize any spatial index. 
addgeometrycolumn(catalog_name: string, schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int) → string  Adds a new geometry column to an existing table and returns metadata about the column created. 
addgeometrycolumn(catalog_name: string, schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string  Adds a new geometry column to an existing table and returns metadata about the column created. 
addgeometrycolumn(schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int) → string  Adds a new geometry column to an existing table and returns metadata about the column created. 
addgeometrycolumn(schema_name: string, table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string  Adds a new geometry column to an existing table and returns metadata about the column created. 
addgeometrycolumn(table_name: string, column_name: string, srid: int, type: string, dimension: int) → string  Adds a new geometry column to an existing table and returns metadata about the column created. 
addgeometrycolumn(table_name: string, column_name: string, srid: int, type: string, dimension: int, use_typmod: bool) → string  Adds a new geometry column to an existing table and returns metadata about the column created. 
geometrytype(geometry: geometry) → string  Returns the type of geometry as a string. This function utilizes the GEOS module. 
geomfromewkb(val: bytes) → geometry  Returns the Geometry from an EWKB representation. 
geomfromewkt(val: string) → geometry  Returns the Geometry from an EWKT representation. 
postgis_addbbox(geometry: geometry) → geometry  Compatibility placeholder function with PostGIS. This does not perform any operation on the Geometry. 
postgis_dropbbox(geometry: geometry) → geometry  Compatibility placeholder function with PostGIS. This does not perform any operation on the Geometry. 
postgis_extensions_upgrade() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_full_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_geos_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_getbbox(geometry: geometry) → box2d  Returns a box2d encapsulating the given Geometry. 
postgis_hasbbox(geometry: geometry) → bool  Returns whether a given Geometry has a bounding box. False for points and empty geometries; always true otherwise. 
postgis_lib_build_date() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_lib_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_liblwgeom_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_libxml_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_proj_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_scripts_build_date() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_scripts_installed() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_scripts_released() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
postgis_wagyu_version() → string  Compatibility placeholder function with PostGIS. Returns a fixed string based on PostGIS 3.0.1, with minor edits. 
st_addpoint(line_string: geometry, point: geometry) → geometry  Adds a Point to the end of a LineString. 
st_addpoint(line_string: geometry, point: geometry, index: int) → geometry  Adds a Point to a LineString at the given 0based index (1 to append). 
st_affine(geometry: geometry, a: float, b: float, d: float, e: float, x_off: float, y_off: float) → geometry  Applies a 2D affine transformation to the given geometry. The matrix transformation will be applied as follows for each coordinate:
/ a b x_off \ / x 
st_angle(line1: geometry, line2: geometry) → float  Returns the clockwise angle between two LINESTRING geometries, treating them as vectors between their start and endpoints. Returns NULL if any vectors have 0 length. 
st_angle(point1: geometry, point2: geometry, point3: geometry) → float  Returns the clockwise angle between the vectors formed by point2,point1 and point2,point3. The arguments must be POINT geometries. Returns NULL if any vectors have 0 length. 
st_angle(point1: geometry, point2: geometry, point3: geometry, point4: geometry) → float  Returns the clockwise angle between the vectors formed by point1,point2 and point3,point4. The arguments must be POINT geometries. Returns NULL if any vectors have 0 length. 
st_area(geography: geography) → float  Returns the area of the given geography in meters^2. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. 
st_area(geography: geography, use_spheroid: bool) → float  Returns the area of the given geography in meters^2. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. 
st_area(geometry: geometry) → float  Returns the area of the given geometry. This function utilizes the GEOS module. 
st_area(geometry_str: string) → float  Returns the area of the given geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_area2d(geometry: geometry) → float  Returns the area of the given geometry. This function utilizes the GEOS module. 
st_asbinary(geography: geography) → bytes  Returns the WKB representation of a given Geography. 
st_asbinary(geography: geography, xdr_or_ndr: string) → bytes  Returns the WKB representation of a given Geography. This variant has a second argument denoting the encoding  
st_asbinary(geometry: geometry) → bytes  Returns the WKB representation of a given Geometry. 
st_asbinary(geometry: geometry, xdr_or_ndr: string) → bytes  Returns the WKB representation of a given Geometry. This variant has a second argument denoting the encoding  
st_asewkb(geography: geography) → bytes  Returns the EWKB representation of a given Geography. 
st_asewkb(geometry: geometry) → bytes  Returns the EWKB representation of a given Geometry. 
st_asewkt(geography: geography) → string  Returns the EWKT representation of a given Geography. A default of 15 decimal digits is used. 
st_asewkt(geography: geography, max_decimal_digits: int) → string  Returns the EWKT representation of a given Geography. The max_decimal_digits parameter controls the maximum decimal digits to print after the 
st_asewkt(geometry: geometry) → string  Returns the EWKT representation of a given Geometry. A maximum of 15 decimal digits is used. 
st_asewkt(geometry: geometry, max_decimal_digits: int) → string  Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the 
st_asewkt(geometry_str: string) → string  Returns the EWKT representation of a given Geometry. A maximum of 15 decimal digits is used. This variant will cast all geometry_str arguments into Geometry types. 
st_asewkt(geometry_str: string, max_decimal_digits: int) → string  Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the This variant will cast all geometry_str arguments into Geometry types. 
st_asgeojson(geography: geography) → string  Returns the GeoJSON representation of a given Geography. Coordinates have a maximum of 9 decimal digits. 
st_asgeojson(geography: geography, max_decimal_digits: int) → string  Returns the GeoJSON representation of a given Geography with max_decimal_digits output for each coordinate value. 
st_asgeojson(geography: geography, max_decimal_digits: int, options: int) → string  Returns the GeoJSON representation of a given Geography with max_decimal_digits output for each coordinate value. Options is a flag that can be bitmasked. The options are:

st_asgeojson(geometry: geometry) → string  Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits. 
st_asgeojson(geometry: geometry, max_decimal_digits: int) → string  Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. 
st_asgeojson(geometry: geometry, max_decimal_digits: int, options: int) → string  Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. Options is a flag that can be bitmasked. The options are:

st_asgeojson(geometry_str: string) → string  Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits. This variant will cast all geometry_str arguments into Geometry types. 
st_asgeojson(geometry_str: string, max_decimal_digits: int) → string  Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. This variant will cast all geometry_str arguments into Geometry types. 
st_asgeojson(geometry_str: string, max_decimal_digits: int, options: int) → string  Returns the GeoJSON representation of a given Geometry with max_decimal_digits output for each coordinate value. Options is a flag that can be bitmasked. The options are:
This variant will cast all geometry_str arguments into Geometry types. 
st_asgeojson(row: tuple) → string  Returns the GeoJSON representation of a given Geometry. Coordinates have a maximum of 9 decimal digits. 
st_asgeojson(row: tuple, geo_column: string) → string  Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. Coordinates have a maximum of 9 decimal digits. 
st_asgeojson(row: tuple, geo_column: string, max_decimal_digits: int) → string  Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. max_decimal_digits will be output for each coordinate value. 
st_asgeojson(row: tuple, geo_column: string, max_decimal_digits: int, pretty: bool) → string  Returns the GeoJSON representation of a given Geometry, using geo_column as the geometry for the given Feature. max_decimal_digits will be output for each coordinate value. Output will be pretty printed in JSON if pretty is true. 
st_ashexewkb(geography: geography) → string  Returns the EWKB representation in hex of a given Geography. 
st_ashexewkb(geography: geography, xdr_or_ndr: string) → string  Returns the EWKB representation in hex of a given Geography. This variant has a second argument denoting the encoding  
st_ashexewkb(geometry: geometry) → string  Returns the EWKB representation in hex of a given Geometry. 
st_ashexewkb(geometry: geometry, xdr_or_ndr: string) → string  Returns the EWKB representation in hex of a given Geometry. This variant has a second argument denoting the encoding  
st_ashexwkb(geography: geography) → string  Returns the WKB representation in hex of a given Geography. 
st_ashexwkb(geometry: geometry) → string  Returns the WKB representation in hex of a given Geometry. 
st_askml(geography: geography) → string  Returns the KML representation of a given Geography. 
st_askml(geometry: geometry) → string  Returns the KML representation of a given Geometry. 
st_askml(geometry_str: string) → string  Returns the KML representation of a given Geometry. This variant will cast all geometry_str arguments into Geometry types. 
st_astext(geography: geography) → string  Returns the WKT representation of a given Geography. A default of 15 decimal digits is used. 
st_astext(geography: geography, max_decimal_digits: int) → string  Returns the WKT representation of a given Geography. The max_decimal_digits parameter controls the maximum decimal digits to print after the 
st_astext(geometry: geometry) → string  Returns the WKT representation of a given Geometry. A maximum of 15 decimal digits is used. 
st_astext(geometry: geometry, max_decimal_digits: int) → string  Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the 
st_astext(geometry_str: string) → string  Returns the WKT representation of a given Geometry. A maximum of 15 decimal digits is used. This variant will cast all geometry_str arguments into Geometry types. 
st_astext(geometry_str: string, max_decimal_digits: int) → string  Returns the WKT representation of a given Geometry. The max_decimal_digits parameter controls the maximum decimal digits to print after the This variant will cast all geometry_str arguments into Geometry types. 
st_azimuth(geography_a: geography, geography_b: geography) → float  Returns the azimuth in radians of the segment defined by the given point geographies, or NULL if the two points are coincident. It is solved using the Inverse geodesic problem. The azimuth is angle is referenced from north, and is positive clockwise: North = 0; East = π/2; South = π; West = 3π/2. This function utilizes the GeographicLib library for spheroid calculations. 
st_azimuth(geometry_a: geometry, geometry_b: geometry) → float  Returns the azimuth in radians of the segment defined by the given point geometries, or NULL if the two points are coincident. The azimuth is angle is referenced from north, and is positive clockwise: North = 0; East = π/2; South = π; West = 3π/2. 
st_boundary(geometry: geometry) → geometry  Returns the closure of the combinatorial boundary of this Geometry. This function utilizes the GEOS module. 
st_box2dfromgeohash(geohash: string) → box2d  Return a Box2D from a GeoHash string with max precision. 
st_box2dfromgeohash(geohash: string, precision: int) → box2d  Return a Box2D from a GeoHash string with supplied precision. 
st_buffer(geography: geography, distance: float) → geography  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. 
st_buffer(geography: geography, distance: float, buffer_style_params: string) → geography  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are:
This function utilizes the GEOS module. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. 
st_buffer(geography: geography, distance: float, quad_segs: int) → geography  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant approximates the circle into quad_seg segments per line (the default is 8). This function utilizes the GEOS module. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. 
st_buffer(geometry: geometry, distance: decimal) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. 
st_buffer(geometry: geometry, distance: float) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. 
st_buffer(geometry: geometry, distance: float, buffer_style_params: string) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are:
This function utilizes the GEOS module. 
st_buffer(geometry: geometry, distance: float, quad_segs: int) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant approximates the circle into quad_seg segments per line (the default is 8). This function utilizes the GEOS module. 
st_buffer(geometry: geometry, distance: int) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. 
st_buffer(geometry_str: string, distance: decimal) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_buffer(geometry_str: string, distance: float) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_buffer(geometry_str: string, distance: float, buffer_style_params: string) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant takes in a space separate parameter string, which will augment the buffer styles. Valid parameters are:
This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_buffer(geometry_str: string, distance: float, quad_segs: int) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This variant approximates the circle into quad_seg segments per line (the default is 8). This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_buffer(geometry_str: string, distance: int) → geometry  Returns a Geometry that represents all points whose distance is less than or equal to the given distance from the given Geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_centroid(geography: geography) → geography  Returns the centroid of given geography. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. 
st_centroid(geography: geography, use_spheroid: bool) → geography  Returns the centroid of given geography. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. 
st_centroid(geometry: geometry) → geometry  Returns the centroid of the given geometry. This function utilizes the GEOS module. 
st_centroid(geometry_str: string) → geometry  Returns the centroid of the given geometry. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_clipbybox2d(geometry: geometry, box2d: box2d) → geometry  Clips the geometry to conform to the bounding box specified by box2d. 
st_collectionextract(geometry: geometry, type: int) → geometry  Given a collection, returns a multitype consisting only of elements of the specified type. If there are no elements of the given type, an EMPTY geometry is returned. Types are specified as 1=POINT, 2=LINESTRING, 3=POLYGON  other types are not supported. 
st_collectionhomogenize(geometry: geometry) → geometry  Returns the “simplest” representation of a collection’s contents. Collections of a single type will be returned as an appopriate multitype, or a singleton if it only contains a single geometry. 
st_combinebbox(box2d: box2d, geometry: geometry) → box2d  Combines the current bounding box with the bounding box of the Geometry. 
st_contains(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if no points of geometry_b lie in the exterior of geometry_a, and there is at least one point in the interior of geometry_b that lies in the interior of geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_containsproperly(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_convexhull(geometry: geometry) → geometry  Returns a geometry that represents the Convex Hull of the given geometry. This function utilizes the GEOS module. 
st_coorddim(geometry: geometry) → int  Returns the number of coordinate dimensions of a given Geometry. 
st_coveredby(geography_a: geography, geography_b: geography) → bool  Returns true if no point in geography_a is outside geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant will attempt to utilize any available spatial index. 
st_coveredby(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if no point in geometry_a is outside geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_coveredby(geometry_a_str: string, geometry_b_str: string) → bool  Returns true if no point in geometry_a is outside geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. 
st_covers(geography_a: geography, geography_b: geography) → bool  Returns true if no point in geography_b is outside geography_a. This function utilizes the S2 library for spherical calculations. This function variant will attempt to utilize any available spatial index. 
st_covers(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if no point in geometry_b is outside geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_covers(geometry_a_str: string, geometry_b_str: string) → bool  Returns true if no point in geometry_b is outside geometry_a. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. 
st_crosses(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a has some  but not all  interior points in common with geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_dfullywithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, inclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than or equal to distance units. This function variant will attempt to utilize any available spatial index. 
st_dfullywithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if every pair of points comprising geometry_a and geometry_b are within distance units, exclusive. In other words, the ST_MaxDistance between geometry_a and geometry_b is less than distance units. This function variant will attempt to utilize any available spatial index. 
st_difference(geometry_a: geometry, geometry_b: geometry) → geometry  Returns the difference of two Geometries. This function utilizes the GEOS module. 
st_dimension(geometry: geometry) → int  Returns the number of topological dimensions of a given Geometry. 
st_disjoint(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a does not overlap, touch or is within geometry_b. This function utilizes the GEOS module. 
st_distance(geography_a: geography, geography_b: geography) → float  Returns the distance in meters between geography_a and geography_b. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. This function utilizes the GeographicLib library for spheroid calculations. 
st_distance(geography_a: geography, geography_b: geography, use_spheroid: bool) → float  Returns the distance in meters between geography_a and geography_b."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. 
st_distance(geometry_a: geometry, geometry_b: geometry) → float  Returns the distance between the given geometries. 
st_distance(geometry_a_str: string, geometry_b_str: string) → float  Returns the distance between the given geometries. This variant will cast all geometry_str arguments into Geometry types. 
st_distancesphere(geometry_a: geometry, geometry_b: geometry) → float  Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a sphere. This function utilizes the S2 library for spherical calculations. 
st_distancespheroid(geometry_a: geometry, geometry_b: geometry) → float  Returns the distance in meters between geometry_a and geometry_b assuming the coordinates represent lng/lat points on a spheroid."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. 
st_dwithin(geography_a: geography, geography_b: geography, distance: float) → bool  Returns true if any of geography_a is within distance meters of geography_b, inclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. 
st_dwithin(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool  Returns true if any of geography_a is within distance meters of geography_b, inclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. 
st_dwithin(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if any of geometry_a is within distance units of geometry_b, inclusive. This function variant will attempt to utilize any available spatial index. 
st_dwithin(geometry_a_str: string, geometry_b_str: string, distance: float) → bool  Returns true if any of geometry_a is within distance units of geometry_b, inclusive. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. 
st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float) → bool  Returns true if any of geography_a is within distance meters of geography_b, exclusive. Uses a spheroid to perform the operation."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. 
st_dwithinexclusive(geography_a: geography, geography_b: geography, distance: float, use_spheroid: bool) → bool  Returns true if any of geography_a is within distance meters of geography_b, exclusive."\n\nWhen operating on a spheroid, this function will use the sphere to calculate the closest two points. The spheroid distance between these two points is calculated using GeographicLib. This follows observed PostGIS behavior. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. This function variant will attempt to utilize any available spatial index. 
st_dwithinexclusive(geometry_a: geometry, geometry_b: geometry, distance: float) → bool  Returns true if any of geometry_a is within distance units of geometry_b, exclusive. This function variant will attempt to utilize any available spatial index. 
st_dwithinexclusive(geometry_a_str: string, geometry_b_str: string, distance: float) → bool  Returns true if any of geometry_a is within distance units of geometry_b, exclusive. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. 
st_endpoint(geometry: geometry) → geometry  Returns the last point of a geometry which has shape LineString. Returns NULL if the geometry is not a LineString. 
st_envelope(geometry: geometry) → geometry  Returns a bounding envelope for the given geometry. For geometries which have a POINT or LINESTRING bounding box (i.e. is a single point or a horizontal or vertical line), a POINT or LINESTRING is returned. Otherwise, the returned POLYGON will be ordered Bottom Left, Top Left, Top Right, Bottom Right, Bottom Left. 
st_equals(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a is spatially equal to geometry_b, i.e. ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = true. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_expand(box2d: box2d, delta: float) → box2d  Extends the box2d by delta units across all dimensions. 
st_expand(box2d: box2d, delta_x: float, delta_y: float) → box2d  Extends the box2d by delta_x units in the x dimension and delta_y units in the y dimension. 
st_expand(geometry: geometry, delta: float) → geometry  Extends the bounding box represented by the geometry by delta units across all dimensions, returning a Polygon representing the new bounding box. 
st_expand(geometry: geometry, delta_x: float, delta_y: float) → geometry  Extends the bounding box represented by the geometry by delta_x units in the x dimension and delta_y units in the y dimension, returning a Polygon representing the new bounding box. 
st_exteriorring(geometry: geometry) → geometry  Returns the exterior ring of a Polygon as a LineString. Returns NULL if the shape is not a Polygon. 
st_flipcoordinates(geometry: geometry) → geometry  Returns a new geometry with the X and Y axes flipped. 
st_force2d(geometry: geometry) → geometry  Returns a Geometry which only contains X and Y coordinates. 
st_forcecollection(geometry: geometry) → geometry  Converts the geometry into a GeometryCollection. 
st_forcepolygonccw(geometry: geometry) → geometry  Returns a Geometry where all Polygon objects have exterior rings in the counterclockwise orientation and interior rings in the clockwise orientation. NonPolygon objects are unchanged. 
st_forcepolygoncw(geometry: geometry) → geometry  Returns a Geometry where all Polygon objects have exterior rings in the clockwise orientation and interior rings in the counterclockwise orientation. NonPolygon objects are unchanged. 
st_frechetdistance(geometry_a: geometry, geometry_b: geometry) → float  Returns the Frechet distance between the given geometries. This function utilizes the GEOS module. 
st_frechetdistance(geometry_a: geometry, geometry_b: geometry, densify_frac: float) → float  Returns the Frechet distance between the given geometries, with the given segment densification (range 0.01.0, 1 to disable). This function utilizes the GEOS module. 
st_geogfromewkb(val: bytes) → geography  Returns the Geography from an EWKB representation. 
st_geogfromewkt(val: string) → geography  Returns the Geography from an EWKT representation. 
st_geogfromgeojson(val: string) → geography  Returns the Geography from an GeoJSON representation. 
st_geogfromgeojson(val: jsonb) → geography  Returns the Geography from an GeoJSON representation. 
st_geogfromtext(str: string, srid: int) → geography  Returns the Geography from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_geogfromtext(val: string) → geography  Returns the Geography from a WKT or EWKT representation. 
st_geogfromwkb(bytes: bytes, srid: int) → geography  Returns the Geography from a WKB (or EWKB) representation with the given SRID set. 
st_geogfromwkb(val: bytes) → geography  Returns the Geography from a WKB (or EWKB) representation. 
st_geographyfromtext(str: string, srid: int) → geography  Returns the Geography from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_geographyfromtext(val: string) → geography  Returns the Geography from a WKT or EWKT representation. 
st_geohash(geography: geography) → string  Returns a GeoHash representation of the geeographywith full precision if a point is provided, or with variable precision based on the size of the feature. 
st_geohash(geography: geography, precision: int) → string  Returns a GeoHash representation of the geography with the supplied precision. 
st_geohash(geometry: geometry) → string  Returns a GeoHash representation of the geometry with full precision if a point is provided, or with variable precision based on the size of the feature. This will error any coordinates are outside the bounds of longitude/latitude. 
st_geohash(geometry: geometry, precision: int) → string  Returns a GeoHash representation of the geometry with the supplied precision. This will error any coordinates are outside the bounds of longitude/latitude. 
st_geomcollfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not GeometryCollection, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_geomcollfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not GeometryCollection, NULL is returned. 
st_geomcollfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not GeometryCollection, NULL is returned. 
st_geomcollfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not GeometryCollection, NULL is returned. 
st_geometryfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_geometryfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. 
st_geometryn(geometry: geometry, n: int) → geometry  Returns the nth Geometry (1indexed). Returns NULL if out of bounds. 
st_geometrytype(geometry: geometry) → string  Returns the type of geometry as a string prefixed with This function utilizes the GEOS module. 
st_geomfromewkb(val: bytes) → geometry  Returns the Geometry from an EWKB representation. 
st_geomfromewkt(val: string) → geometry  Returns the Geometry from an EWKT representation. 
st_geomfromgeohash(geohash: string) → geometry  Return a POLYGON Geometry from a GeoHash string with max precision. 
st_geomfromgeohash(geohash: string, precision: int) → geometry  Return a POLYGON Geometry from a GeoHash string with supplied precision. 
st_geomfromgeojson(val: string) → geometry  Returns the Geometry from an GeoJSON representation. 
st_geomfromgeojson(val: jsonb) → geometry  Returns the Geometry from an GeoJSON representation. 
st_geomfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_geomfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. 
st_geomfromwkb(bytes: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with the given SRID set. 
st_geomfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. 
st_hausdorffdistance(geometry_a: geometry, geometry_b: geometry) → float  Returns the Hausdorff distance between the given geometries. This function utilizes the GEOS module. 
st_hausdorffdistance(geometry_a: geometry, geometry_b: geometry, densify_frac: float) → float  Returns the Hausdorff distance between the given geometries, with the given segment densification (range 0.01.0). This function utilizes the GEOS module. 
st_interiorringn(geometry: geometry, n: int) → geometry  Returns the nth (1indexed) interior ring of a Polygon as a LineString. Returns NULL if the shape is not a Polygon, or the ring does not exist. 
st_intersection(geography_a: geography, geography_b: geography) → geography  Returns the point intersections of the given geographies. This operation is done by transforming the object into a Geometry. This occurs by translating the Geography objects into Geometry objects before applying an LAEA, UTM or Web Mercator based projection based on the bounding boxes of the given Geography objects. When the result is calculated, the result is transformed back into a Geography with SRID 4326. This function utilizes the GEOS module. 
st_intersection(geometry_a: geometry, geometry_b: geometry) → geometry  Returns the point intersections of the given geometries. This function utilizes the GEOS module. 
st_intersection(geometry_a_str: string, geometry_b_str: string) → geometry  Returns the point intersections of the given geometries. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_intersects(geography_a: geography, geography_b: geography) → bool  Returns true if geography_a shares any portion of space with geography_b. The calculations performed are have a precision of 1cm. This function utilizes the S2 library for spherical calculations. This function variant will attempt to utilize any available spatial index. 
st_intersects(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a shares any portion of space with geometry_b. The calculations performed are have a precision of 1cm. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_intersects(geometry_a_str: string, geometry_b_str: string) → bool  Returns true if geometry_a shares any portion of space with geometry_b. The calculations performed are have a precision of 1cm. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. This variant will cast all geometry_str arguments into Geometry types. 
st_isclosed(geometry: geometry) → bool  Returns whether the geometry is closed as defined by whether the start and end points are coincident. Points are considered closed, empty geometries are not. For collections and multitypes, all members must be closed, as must all polygon rings. 
st_iscollection(geometry: geometry) → bool  Returns whether the geometry is of a collection type (including multitypes). 
st_isempty(geometry: geometry) → bool  Returns whether the geometry is empty. 
st_ispolygonccw(geometry: geometry) → bool  Returns whether the Polygon objects inside the Geometry have exterior rings in the counterclockwise orientation and interior rings in the clockwise orientation. NonPolygon objects are considered counterclockwise. 
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 counterclockwise orientation. NonPolygon objects are considered clockwise. 
st_isring(geometry: geometry) → bool  Returns whether the geometry is a single linestring that is closed and simple, as defined by ST_IsClosed and ST_IsSimple. This function utilizes the GEOS module. 
st_issimple(geometry: geometry) → bool  Returns true if the geometry has no anomalous geometric points, e.g. that it intersects with or lies tangent to itself. This function utilizes the GEOS module. 
st_isvalid(geometry: geometry) → bool  Returns whether the geometry is valid as defined by the OGC spec. This function utilizes the GEOS module. 
st_isvalid(geometry: geometry, flags: int) → bool  Returns whether the geometry is valid. For flags=0, validity is defined by the OGC spec. For flags=1, validity considers selfintersecting rings forming holes as valid as per ESRI. This is not valid under OGC and CRDB spatial operations may not operate correctly. This function utilizes the GEOS module. 
st_isvalidreason(geometry: geometry) → string  Returns a string containing the reason the geometry is invalid along with the point of interest, or “Valid Geometry” if it is valid. Validity is defined by the OGC spec. This function utilizes the GEOS module. 
st_isvalidreason(geometry: geometry, flags: int) → string  Returns the reason the geometry is invalid or “Valid Geometry” if it is valid. For flags=0, validity is defined by the OGC spec. For flags=1, validity considers selfintersecting rings forming holes as valid as per ESRI. This is not valid under OGC and CRDB spatial operations may not operate correctly. This function utilizes the GEOS module. 
st_length(geography: geography) → float  Returns the length of the given geography in meters. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. 
st_length(geography: geography, use_spheroid: bool) → float  Returns the length of the given geography in meters. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. 
st_length(geometry: geometry) → float  Returns the length of the given geometry. Note ST_Length is only valid for LineString  use ST_Perimeter for Polygon. This function utilizes the GEOS module. 
st_length(geometry_str: string) → float  Returns the length of the given geometry. Note ST_Length is only valid for LineString  use ST_Perimeter for Polygon. This function utilizes the GEOS module. This variant will cast all geometry_str arguments into Geometry types. 
st_length2d(geometry: geometry) → float  Returns the length of the given geometry. Note ST_Length is only valid for LineString  use ST_Perimeter for Polygon. This function utilizes the GEOS module. 
st_linefrommultipoint(geometry: geometry) → geometry  Creates a LineString from a MultiPoint geometry. 
st_linefromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not LineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_linefromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not LineString, NULL is returned. 
st_linefromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not LineString, NULL is returned. 
st_linefromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not LineString, NULL is returned. 
st_lineinterpolatepoint(geometry: geometry, fraction: float) → geometry  Returns a point along the given LineString which is at given fraction of LineString’s total length. This function utilizes the GEOS module. 
st_lineinterpolatepoints(geometry: geometry, fraction: float) → geometry  Returns one or more points along the LineString which is at an integral multiples of given fraction of LineString’s total length. Note If the result has zero or one points, it will be returned as a POINT. If it has two or more points, it will be returned as a MULTIPOINT. This function utilizes the GEOS module. 
st_lineinterpolatepoints(geometry: geometry, fraction: float, repeat: bool) → geometry  Returns one or more points along the LineString which is at an integral multiples of given fraction of LineString’s total length. If repeat is false (default true) then it returns first point. Note If the result has zero or one points, it will be returned as a POINT. If it has two or more points, it will be returned as a MULTIPOINT. This function utilizes the GEOS module. 
st_linemerge(geometry: geometry) → geometry  Returns a LineString or MultiLineString by joining together constituents of a MultiLineString with matching endpoints. If the input is not a MultiLineString or LineString, an empty GeometryCollection is returned. This function utilizes the GEOS module. 
st_linestringfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not LineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_linestringfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not LineString, NULL is returned. 
st_linestringfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not LineString, NULL is returned. 
st_linestringfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not LineString, NULL is returned. 
st_longestline(geometry_a: geometry, geometry_b: geometry) → geometry  Returns the LineString corresponds to the max distance across every pair of points comprising the given geometries. Note if geometries are the same, it will return the LineString with the maximum distance between the geometry’s vertexes. The function will return the longest line that was discovered first when comparing maximum distances if more than one is found. 
st_makebox2d(geometry_a: geometry, geometry_b: geometry) → box2d  Creates a box2d from two points. Errors if arguments are not two nonempty points. 
st_makepoint(x: float, y: float) → geometry  Returns a new Point with the given X and Y coordinates. 
st_makepolygon(geometry: geometry) → geometry  Returns a new Polygon with the given outer LineString. 
st_makepolygon(outer: geometry, interior: anyelement[]) → geometry  Returns a new Polygon with the given outer LineString and interior (hole) LineString(s). 
st_makevalid(geometry: geometry) → geometry  Returns a valid form of the given geometry according to the OGC spec. This function utilizes the GEOS module. 
st_maxdistance(geometry_a: geometry, geometry_b: geometry) → float  Returns the maximum distance across every pair of points comprising the given geometries. Note if the geometries are the same, it will return the maximum distance between the geometry’s vertexes. 
st_minimumclearance(geometry: geometry) → float  Returns the minimum distance a vertex can move before producing an invalid geometry. Returns Infinity if no minimum clearance can be found (e.g. for a single point). 
st_minimumclearanceline(geometry: geometry) → geometry  Returns a LINESTRING spanning the minimum distance a vertex can move before producing an invalid geometry. If no minimum clearance can be found (e.g. for a single point), an empty LINESTRING is returned. 
st_mlinefromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_mlinefromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned. 
st_mlinefromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned. 
st_mlinefromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. 
st_mpointfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_mpointfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPoint, NULL is returned. 
st_mpointfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPoint, NULL is returned. 
st_mpointfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. 
st_mpolyfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_mpolyfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned. 
st_mpolyfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned. 
st_mpolyfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. 
st_multi(geometry: geometry) → geometry  Returns the geometry as a new multigeometry, e.g converts a POINT to a MULTIPOINT. If the input is already a multitype or collection, it is returned as is. 
st_multilinefromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_multilinefromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned. 
st_multilinefromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned. 
st_multilinefromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. 
st_multilinestringfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_multilinestringfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiLineString, NULL is returned. 
st_multilinestringfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiLineString, NULL is returned. 
st_multilinestringfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiLineString, NULL is returned. 
st_multipointfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_multipointfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPoint, NULL is returned. 
st_multipointfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPoint, NULL is returned. 
st_multipointfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPoint, NULL is returned. 
st_multipolyfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_multipolyfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned. 
st_multipolyfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned. 
st_multipolyfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. 
st_multipolygonfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_multipolygonfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not MultiPolygon, NULL is returned. 
st_multipolygonfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not MultiPolygon, NULL is returned. 
st_multipolygonfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not MultiPolygon, NULL is returned. 
st_ndims(geometry: geometry) → int  Returns the number of coordinate dimensions of a given Geometry. 
st_normalize(geometry: geometry) → geometry  Returns the geometry in its normalized form. This function utilizes the GEOS module. 
st_npoints(geometry: geometry) → int  Returns the number of points in a given Geometry. Works for any shape type. 
st_nrings(geometry: geometry) → int  Returns the number of rings in a Polygon Geometry. Returns 0 if the shape is not a Polygon. 
st_numgeometries(geometry: geometry) → int  Returns the number of shapes inside a given Geometry. 
st_numinteriorring(geometry: geometry) → int  Returns the number of interior rings in a Polygon Geometry. Returns NULL if the shape is not a Polygon. 
st_numinteriorrings(geometry: geometry) → int  Returns the number of interior rings in a Polygon Geometry. Returns NULL if the shape is not a Polygon. 
st_numpoints(geometry: geometry) → int  Returns the number of points in a LineString. Returns NULL if the Geometry is not a LineString. 
st_orderingequals(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a is exactly equal to geometry_b, having all coordinates in the same order, as well as the same type, SRID, bounding box, and so on. 
st_overlaps(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a intersects but does not completely contain geometry_b, or vice versa. “Does not completely” implies ST_Within(geometry_a, geometry_b) = ST_Within(geometry_b, geometry_a) = false. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_perimeter(geography: geography) → float  Returns the perimeter of the given geography in meters. Uses a spheroid to perform the operation. This function utilizes the GeographicLib library for spheroid calculations. 
st_perimeter(geography: geography, use_spheroid: bool) → float  Returns the perimeter of the given geography in meters. This function utilizes the S2 library for spherical calculations. This function utilizes the GeographicLib library for spheroid calculations. 
st_perimeter(geometry: geometry) → float  Returns the perimeter of the given geometry. Note ST_Perimeter is only valid for Polygon  use ST_Length for LineString. This function utilizes the GEOS module. 
st_perimeter2d(geometry: geometry) → float  Returns the perimeter of the given geometry. Note ST_Perimeter is only valid for Polygon  use ST_Length for LineString. This function utilizes the GEOS module. 
st_point(x: float, y: float) → geometry  Returns a new Point with the given X and Y coordinates. 
st_pointfromgeohash(geohash: string) → geometry  Return a POINT Geometry from a GeoHash string with max precision. 
st_pointfromgeohash(geohash: string, precision: int) → geometry  Return a POINT Geometry from a GeoHash string with supplied precision. 
st_pointfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Point, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_pointfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Point, NULL is returned. 
st_pointfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Point, NULL is returned. 
st_pointfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not Point, NULL is returned. 
st_pointn(geometry: geometry, n: int) → geometry  Returns the nth Point of a LineString (1indexed). Returns NULL if out of bounds or not a LineString. 
st_pointonsurface(geometry: geometry) → geometry  Returns a point that intersects with the given Geometry. This function utilizes the GEOS module. 
st_points(geometry: geometry) → geometry  Returns all coordinates in the given Geometry as a MultiPoint, including duplicates. 
st_polyfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Polygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_polyfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Polygon, NULL is returned. 
st_polyfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Polygon, NULL is returned. 
st_polyfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not Polygon, NULL is returned. 
st_polygon(geometry: geometry, srid: int) → geometry  Returns a new Polygon from the given LineString and sets its SRID. It is equivalent to ST_MakePolygon with a single argument followed by ST_SetSRID. 
st_polygonfromtext(str: string, srid: int) → geometry  Returns the Geometry from a WKT or EWKT representation with an SRID. If the shape underneath is not Polygon, NULL is returned. If the SRID is present in both the EWKT and the argument, the argument value is used. 
st_polygonfromtext(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. If the shape underneath is not Polygon, NULL is returned. 
st_polygonfromwkb(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. If the shape underneath is not Polygon, NULL is returned. 
st_polygonfromwkb(wkb: bytes, srid: int) → geometry  Returns the Geometry from a WKB (or EWKB) representation with an SRID. If the shape underneath is not Polygon, NULL is returned. 
st_project(geography: geography, distance: float, azimuth: float) → geography  Returns a point projected from a start point along a geodesic using a given distance and azimuth (bearing). This is known as the direct geodesic problem. The distance is given in meters. Negative values are supported. The azimuth (also known as heading or bearing) is given in radians. It is measured clockwise from true north (azimuth zero). East is azimuth π/2 (90 degrees); south is azimuth π (180 degrees); west is azimuth 3π/2 (270 degrees). Negative azimuth values and values greater than 2π (360 degrees) are supported. 
st_relate(geometry_a: geometry, geometry_b: geometry) → string  Returns the DE9IM spatial relation between geometry_a and geometry_b. This function utilizes the GEOS module. 
st_relate(geometry_a: geometry, geometry_b: geometry, bnr: int) → string  Returns the DE9IM spatial relation between geometry_a and geometry_b using the given boundary node rule (1:OGC/MOD2, 2:Endpoint, 3:MultivalentEndpoint, 4:MonovalentEndpoint). This function utilizes the GEOS module. 
st_relate(geometry_a: geometry, geometry_b: geometry, pattern: string) → bool  Returns whether the DE9IM spatial relation between geometry_a and geometry_b matches the DE9IM pattern. This function utilizes the GEOS module. 
st_relatematch(intersection_matrix: string, pattern: string) → bool  Returns whether the given DE9IM intersection matrix satisfies the given pattern. 
st_removepoint(line_string: geometry, index: int) → geometry  Removes the Point at the given 0based index and returns the modified LineString geometry. 
st_removerepeatedpoints(geometry: geometry) → geometry  Returns a geometry with repeated points removed. 
st_removerepeatedpoints(geometry: geometry, tolerance: float) → geometry  Returns a geometry with repeated points removed, within the given distance tolerance. 
st_reverse(geometry: geometry) → geometry  Returns a modified geometry by reversing the order of its vertices. 
st_rotate(g: geometry, angle_radians: float) → geometry  Returns a modified Geometry whose coordinates are rotated around the origin by a rotation angle. 
st_rotate(g: geometry, angle_radians: float, origin_point: geometry) → geometry  Returns a modified Geometry whose coordinates are rotated around the provided origin by a rotation angle. 
st_rotate(g: geometry, angle_radians: float, origin_x: float, origin_y: float) → geometry  Returns a modified Geometry whose coordinates are rotated around the provided origin by a rotation angle. 
st_s2covering(geography: geography) → geography  Returns a geography which represents the S2 covering used by the index using the default index configuration. 
st_s2covering(geography: geography, settings: string) → geography  Returns a geography which represents the S2 covering used by the index using the index configuration specified by the settings parameter. The settings parameter uses the same format as the parameters inside the WITH in CREATE INDEX … WITH (…), e.g. CREATE INDEX t_idx ON t USING GIST(geom) WITH (s2_max_level=15, s2_level_mod=3) can be tried using SELECT ST_S2Covering(geography, ‘s2_max_level=15,s2_level_mod=3’). 
st_s2covering(geometry: geometry) → geometry  Returns a geometry which represents the S2 covering used by the index using the default index configuration. 
st_s2covering(geometry: geometry, settings: string) → geometry  Returns a geometry which represents the S2 covering used by the index using the index configuration specified by the settings parameter. The settings parameter uses the same format as the parameters inside the WITH in CREATE INDEX … WITH (…), e.g. CREATE INDEX t_idx ON t USING GIST(geom) WITH (s2_max_level=15, s2_level_mod=3) can be tried using SELECT ST_S2Covering(geometry, ‘s2_max_level=15,s2_level_mod=3’). 
st_scale(g: geometry, factor: geometry) → geometry  Returns a modified Geometry scaled by taking in a Geometry as the factor. 
st_scale(g: geometry, factor: geometry, origin: geometry) → geometry  Returns a modified Geometry scaled by the Geometry factor relative to a false origin. 
st_scale(geometry: geometry, x_factor: float, y_factor: float) → geometry  Returns a modified Geometry scaled by the given factors. 
st_segmentize(geography: geography, max_segment_length_meters: float) → geography  Returns a modified Geography having no segment longer than the given max_segment_length meters. The calculations are done on a sphere. This function utilizes the S2 library for spherical calculations. 
st_segmentize(geometry: geometry, max_segment_length: float) → geometry  Returns a modified Geometry having no segment longer than the given max_segment_length. Length units are in units of spatial reference. 
st_setpoint(line_string: geometry, index: int, point: geometry) → geometry  Sets the Point at the given 0based index and returns the modified LineString geometry. 
st_setsrid(geography: geography, srid: int) → geography  Sets a Geography to a new SRID without transforming the coordinates. 
st_setsrid(geometry: geometry, srid: int) → geometry  Sets a Geometry to a new SRID without transforming the coordinates. 
st_sharedpaths(geometry_a: geometry, geometry_b: geometry) → geometry  Returns a collection containing paths shared by the two input geometries. Those going in the same direction are in the first element of the collection, those going in the opposite direction are in the second element. The paths themselves are given in the direction of the first geometry. 
st_shortestline(geometry_a: geometry, geometry_b: geometry) → geometry  Returns the LineString corresponds to the minimum distance across every pair of points comprising the given geometries. Note if geometries are the same, it will return the LineString with the minimum distance between the geometry’s vertexes. The function will return the shortest line that was discovered first when comparing minimum distances if more than one is found. 
st_simplify(geometry: geometry, tolerance: float) → geometry  Simplifies the given geometry using the DouglasPeucker algorithm. 
st_simplify(geometry: geometry, tolerance: float, preserve_collapsed: bool) → geometry  Simplifies the given geometry using the DouglasPeucker algorithm, retaining objects that would be too small given the tolerance if preserve_collapsed is set to true. 
st_simplifypreservetopology(geometry: geometry, tolerance: float) → geometry  Simplifies the given geometry using the DouglasPeucker algorithm, avoiding the creation of invalid geometries. 
st_snaptogrid(geometry: geometry, origin_x: float, origin_y: float, size_x: float, size_y: float) → geometry  Snap a geometry to a grid of with X coordinates snapped to size_x and Y coordinates snapped to size_y based on an origin of (origin_x, origin_y). 
st_snaptogrid(geometry: geometry, size: float) → geometry  Snap a geometry to a grid of the given size. 
st_snaptogrid(geometry: geometry, size_x: float, size_y: float) → geometry  Snap a geometry to a grid of with X coordinates snapped to size_x and Y coordinates snapped to size_y. 
st_srid(geography: geography) → int  Returns the Spatial Reference Identifier (SRID) for the ST_Geography as defined in spatial_ref_sys table. 
st_srid(geometry: geometry) → int  Returns the Spatial Reference Identifier (SRID) for the ST_Geometry as defined in spatial_ref_sys table. 
st_startpoint(geometry: geometry) → geometry  Returns the first point of a geometry which has shape LineString. Returns NULL if the geometry is not a LineString. 
st_summary(geography: geography) → string  Returns a text summary of the contents of the geography. Flags shown square brackets after the geometry type have the following meaning:

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:

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 2character string naming the ordinates to swap. Valid names are: x, y, z and m. 
st_symdifference(geometry_a: geometry, geometry_b: geometry) → geometry  Returns the symmetric difference of both geometries. This function utilizes the GEOS module. 
st_symmetricdifference(geometry_a: geometry, geometry_b: geometry) → geometry  Returns the symmetric difference of both geometries. This function utilizes the GEOS module. 
st_touches(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if the only points in common between geometry_a and geometry_b are on the boundary. Note points do not touch other points. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_transform(geometry: geometry, from_proj_text: string, srid: int) → geometry  Transforms a geometry into the coordinate reference system assuming the from_proj_text to the new to_proj_text by projecting its coordinates. The supplied SRID is set on the new geometry. This function utilizes the PROJ library for coordinate projections. 
st_transform(geometry: geometry, from_proj_text: string, to_proj_text: string) → geometry  Transforms a geometry into the coordinate reference system assuming the from_proj_text to the new to_proj_text by projecting its coordinates. This function utilizes the PROJ library for coordinate projections. 
st_transform(geometry: geometry, srid: int) → geometry  Transforms a geometry into the given SRID coordinate reference system by projecting its coordinates. This function utilizes the PROJ library for coordinate projections. 
st_transform(geometry: geometry, to_proj_text: string) → geometry  Transforms a geometry into the coordinate reference system referenced by the projection text by projecting its coordinates. This function utilizes the PROJ library for coordinate projections. 
st_translate(g: geometry, delta_x: float, delta_y: float) → geometry  Returns a modified Geometry translated by the given deltas. 
st_within(geometry_a: geometry, geometry_b: geometry) → bool  Returns true if geometry_a is completely inside geometry_b. This function utilizes the GEOS module. This function variant will attempt to utilize any available spatial index. 
st_wkbtosql(val: bytes) → geometry  Returns the Geometry from a WKB (or EWKB) representation. 
st_wkttosql(val: string) → geometry  Returns the Geometry from a WKT or EWKT representation. 
st_x(geometry: geometry) → float  Returns the X coordinate of a geometry if it is a Point. 
st_y(geometry: geometry) → float  Returns the Y coordinate of a geometry if it is a Point. 
String and byte functions
Function → Returns  Description  

ascii(val: string) → int  Returns the character code of the first character in  
bit_length(val: bytes) → int  Calculates the number of bits used to represent  
bit_length(val: string) → int  Calculates the number of bits used to represent  
bit_length(val: varbit) → int  Calculates the number of bits used to represent  
btrim(input: string, trim_chars: string) → string  Removes any characters included in For example,  
btrim(val: string) → string  Removes all spaces from the beginning and end of  
char_length(val: bytes) → int  Calculates the number of bytes in  
char_length(val: string) → int  Calculates the number of characters in  
character_length(val: bytes) → int  Calculates the number of bytes in  
character_length(val: string) → int  Calculates the number of characters in  
chr(val: int) → string  Returns the character with the code given in  
concat(string...) → string  Concatenates a commaseparated list of strings.  
concat_ws(string...) → string  Uses the first argument as a separator between the concatenation of the subsequent arguments. For example  
convert_from(str: bytes, enc: string) → string  Decode the bytes in  
convert_to(str: string, enc: string) → bytes  Encode the string  
decode(text: string, format: string) → bytes  Decodes  
encode(data: bytes, format: string) → string  Encodes  
from_ip(val: bytes) → string  Converts the byte string representation of an IP to its character string representation.  
from_uuid(val: bytes) → string  Converts the byte string representation of a UUID to its character string representation.  
get_bit(bit_string: varbit, index: int) → int  Extracts a bit at given index in the bit array.  
get_bit(byte_string: bytes, index: int) → int  Extracts a bit at given index in the byte array.  
initcap(val: string) → string  Capitalizes the first letter of  
left(input: bytes, return_set: int) → bytes  Returns the first  
left(input: string, return_set: int) → string  Returns the first  
length(val: bytes) → int  Calculates the number of bytes in  
length(val: string) → int  Calculates the number of characters in  
length(val: varbit) → int  Calculates the number of bits in  
lower(val: string) → string  Converts all characters in  
lpad(string: string, length: int) → string  Pads  
lpad(string: string, length: int, fill: string) → string  Pads  
ltrim(input: string, trim_chars: string) → string  Removes any characters included in For example,  
ltrim(val: string) → string  Removes all spaces from the beginning (lefthand side) of  
md5(bytes...) → string  Calculates the MD5 hash value of a set of values.  
md5(string...) → string  Calculates the MD5 hash value of a set of values.  
octet_length(val: bytes) → int  Calculates the number of bytes used to represent  
octet_length(val: string) → int  Calculates the number of bytes used to represent  
octet_length(val: varbit) → int  Calculates the number of bits used to represent  
overlay(input: string, overlay_val: string, start_pos: int) → string  Replaces characters in For example,  
overlay(input: string, overlay_val: string, start_pos: int, end_pos: int) → string  Deletes the characters in  
pg_collation_for(str: anyelement) → string  Returns the collation of the argument  
quote_ident(val: string) → string  Return  
quote_literal(val: string) → string  Return  
quote_literal(val: anyelement) → string  Coerce  
quote_nullable(val: string) → string  Coerce  
quote_nullable(val: anyelement) → string  Coerce  
regexp_extract(input: string, regex: string) → string  Returns the first match for the Regular Expression  
regexp_replace(input: string, regex: string, replace: string) → string  Replaces matches for the Regular Expression  
regexp_replace(input: string, regex: string, replace: string, flags: string) → string  Replaces matches for the regular expression CockroachDB supports the following flags:
 
repeat(input: string, repeat_counter: int) → string  Concatenates For example,  
replace(input: string, find: string, replace: string) → string  Replaces all occurrences of  
reverse(val: string) → string  Reverses the order of the string’s characters.  
right(input: bytes, return_set: int) → bytes  Returns the last  
right(input: string, return_set: int) → string  Returns the last  
rpad(string: string, length: int) → string  Pads  
rpad(string: string, length: int, fill: string) → string  Pads  
rtrim(input: string, trim_chars: string) → string  Removes any characters included in For example,  
rtrim(val: string) → string  Removes all spaces from the end (righthand side) of  
set_bit(bit_string: varbit, index: int, to_set: int) → varbit  Updates a bit at given index in the bit array.  
set_bit(byte_string: bytes, index: int, to_set: int) → bytes  Updates a bit at given index in the byte array.  
sha1(bytes...) → string  Calculates the SHA1 hash value of a set of values.  
sha1(string...) → string  Calculates the SHA1 hash value of a set of values.  
sha256(bytes...) → string  Calculates the SHA256 hash value of a set of values.  
sha256(string...) → string  Calculates the SHA256 hash value of a set of values.  
sha512(bytes...) → string  Calculates the SHA512 hash value of a set of values.  
sha512(string...) → string  Calculates the SHA512 hash value of a set of values.  
split_part(input: string, delimiter: string, return_index_pos: int) → string  Splits For example,  
strpos(input: bytes, find: bytes) → int  Calculates the position where the byte subarray  
strpos(input: string, find: string) → int  Calculates the position where the string For example,  
strpos(input: varbit, find: varbit) → int  Calculates the position where the bit subarray  
substr(input: bytes, start_pos: int) → bytes  Returns a byte subarray of  
substr(input: bytes, start_pos: int, length: int) → bytes  Returns a byte subarray of  
substr(input: string, regex: string) → string  Returns a substring of  
substr(input: string, regex: string, escape_char: string) → string  Returns a substring of
 
substr(input: string, start_pos: int) → string  Returns a substring of  
substr(input: string, start_pos: int, length: int) → string  Returns a substring of  
substr(input: varbit, start_pos: int) → varbit  Returns a bit subarray of  
substr(input: varbit, start_pos: int, length: int) → varbit  Returns a bit subarray of  
substring(input: bytes, start_pos: int) → bytes  Returns a byte subarray of  
substring(input: bytes, start_pos: int, length: int) → bytes  Returns a byte subarray of  
substring(input: string, regex: string) → string  Returns a substring of  
substring(input: string, regex: string, escape_char: string) → string  Returns a substring of
 
substring(input: string, start_pos: int) → string  Returns a substring of  
substring(input: string, start_pos: int, length: int) → string  Returns a substring of  
substring(input: varbit, start_pos: int) → varbit  Returns a bit subarray of  
substring(input: varbit, start_pos: int, length: int) → varbit  Returns a bit subarray of  
to_english(val: int) → string  This function enunciates the value of its argument using English cardinals.  
to_hex(val: bytes) → string  Converts  
to_hex(val: int) → string  Converts  
to_hex(val: string) → string  Converts  
to_ip(val: string) → bytes  Converts the character string representation of an IP to its byte string representation.  
to_uuid(val: string) → bytes  Converts the character string representation of a UUID to its byte string representation.  
translate(input: string, find: string, replace: string) → string  In For example,  
upper(val: string) → string  Converts all characters in 
System info functions
Function → Returns  Description 

cluster_logical_timestamp() → decimal  Returns the logical time of the current transaction. This function is reserved for testing purposes by CockroachDB developers and its definition may change without prior notice. Note that uses of this function disable serverside optimizations and may increase either contention or retry errors, or both. 
crdb_internal.approximate_timestamp(timestamp: decimal) → timestamp  Converts the crdb_internal_mvcc_timestamp column into an approximate timestamp. 
crdb_internal.check_consistency(stats_only: bool, start_key: bytes, end_key: bytes) → tuple{int AS range_id, bytes AS start_key, string AS start_key_pretty, string AS status, string AS detail}  Runs a consistency check on ranges touching the specified key range. an empty start or end key is treated as the minimum and maximum possible, respectively. stats_only should only be set to false when targeting a small number of ranges to avoid overloading the cluster. Each returned row contains the range ID, the status (a roachpb.CheckConsistencyResponse_Status), and verbose detail. Example usage: SELECT * FROM crdb_internal.check_consistency(true, ‘\x02’, ‘\x04’) 
crdb_internal.cluster_id() → uuid  Returns the cluster ID. 
crdb_internal.cluster_name() → string  Returns the cluster name. 
crdb_internal.completed_migrations() → string[]  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.encode_key(table_id: int, index_id: int, row_tuple: anyelement) → bytes  Generate the key for a row on a particular table and index. 
crdb_internal.force_assertion_error(msg: string) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.force_error(errorCode: string, msg: string) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.force_log_fatal(msg: string) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.force_panic(msg: string) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.force_retry(val: interval) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.get_database_id(name: string) → int  
crdb_internal.get_namespace_id(parent_id: int, name: string) → int  
crdb_internal.get_zone_config(namespace_id: int) → bytes  
crdb_internal.has_role_option(option: string) → bool  Returns whether the current user has the specified role option 
crdb_internal.is_admin() → bool  Retrieves the current user’s admin status. 
crdb_internal.lease_holder(key: bytes) → int  This function is used to fetch the leaseholder corresponding to a request key 
crdb_internal.locality_value(key: string) → string  Returns the value of the specified locality key. 
crdb_internal.no_constant_folding(input: anyelement) → anyelement  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.node_executable_version() → string  Returns the version of CockroachDB this node is running. 
crdb_internal.node_id() → int  Returns the node ID. 
crdb_internal.notice(msg: string) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.notice(severity: string, msg: string) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.num_geo_inverted_index_entries(table_id: int, index_id: int, val: geography) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.num_geo_inverted_index_entries(table_id: int, index_id: int, val: geometry) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.num_inverted_index_entries(val: anyelement[]) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.num_inverted_index_entries(val: jsonb) → int  This function is used only by CockroachDB’s developers for testing purposes. 
crdb_internal.pretty_key(raw_key: bytes, skip_fields: int) → string  This function triggers a transaction retry, which can be useful for testing purposes. 
crdb_internal.range_stats(key: bytes) → jsonb  This function is used to retrieve range statistics information as a JSON object. 
crdb_internal.round_decimal_values(val: decimal, scale: int) → decimal  This function is used internally to round decimal values during mutations. 
crdb_internal.round_decimal_values(val: decimal[], scale: int) → decimal[]  This function is used internally to round decimal array values during mutations. 
crdb_internal.set_vmodule(vmodule_string: string) → int  Set the equivalent of the 
current_database() → string  Returns the current database. 
current_schema() → string  Returns the current schema. 
current_schemas(include_pg_catalog: bool) → string[]  Returns the valid schemas in the search path. 
current_user() → string  Returns the current user. This function is provided for compatibility with PostgreSQL. 
version() → string  Returns the node’s version of CockroachDB. 
TIMETZ functions
Function → Returns  Description 

current_time() → time  Returns the current transaction’s time with no time zone. 
current_time() → timetz  Returns the current transaction’s time with time zone. This function is the preferred overload and will be evaluated by default. 
current_time(precision: int) → time  Returns the current transaction’s time with no time zone. 
current_time(precision: int) → timetz  Returns the current transaction’s time with time zone. This function is the preferred overload and will be evaluated by default. 
localtime() → time  Returns the current transaction’s time with no time zone. This function is the preferred overload and will be evaluated by default. 
localtime() → timetz  Returns the current transaction’s time with time zone. 
localtime(precision: int) → time  Returns the current transaction’s time with no time zone. This function is the preferred overload and will be evaluated by default. 
localtime(precision: int) → timetz  Returns the current transaction’s time with time zone. 
TUPLE functions
Function → Returns  Description 

row_to_json(row: tuple) → jsonb  Returns the row as a JSON object. 
Compatibility functions
Function → Returns  Description 

format_type(type_oid: oid, typemod: int) → string  Returns the SQL name of a data type that is identified by its type OID and possibly a type modifier. Currently, the type modifier is ignored. 
getdatabaseencoding() → string  Returns the current encoding name used by the database. 
has_any_column_privilege(table: string, privilege: string) → bool  Returns whether or not the current user has privileges for any column of table. 
has_any_column_privilege(table: oid, privilege: string) → bool  Returns whether or not the current user has privileges for any column of table. 
has_any_column_privilege(user: string, table: string, privilege: string) → bool  Returns whether or not the user has privileges for any column of table. 
has_any_column_privilege(user: string, table: oid, privilege: string) → bool  Returns whether or not the user has privileges for any column of table. 
has_any_column_privilege(user: oid, table: string, privilege: string) → bool  Returns whether or not the user has privileges for any column of table. 
has_any_column_privilege(user: oid, table: oid, privilege: string) → bool  Returns whether or not the user has privileges for any column of table. 
has_column_privilege(table: string, column: int, privilege: string) → bool  Returns whether or not the current user has privileges for column. 
has_column_privilege(table: string, column: string, privilege: string) → bool  Returns whether or not the current user has privileges for column. 
has_column_privilege(table: oid, column: int, privilege: string) → bool  Returns whether or not the current user has privileges for column. 
has_column_privilege(table: oid, column: string, privilege: string) → bool  Returns whether or not the current user has privileges for column. 
has_column_privilege(user: string, table: string, column: int, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: string, table: string, column: string, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: string, table: oid, column: int, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: string, table: oid, column: string, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: oid, table: string, column: int, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: oid, table: string, column: string, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: oid, table: oid, column: int, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_column_privilege(user: oid, table: oid, column: string, privilege: string) → bool  Returns whether or not the user has privileges for column. 
has_database_privilege(database: string, privilege: string) → bool  Returns whether or not the current user has privileges for database. 
has_database_privilege(database: oid, privilege: string) → bool  Returns whether or not the current user has privileges for database. 
has_database_privilege(user: string, database: string, privilege: string) → bool  Returns whether or not the user has privileges for database. 
has_database_privilege(user: string, database: oid, privilege: string) → bool  Returns whether or not the user has privileges for database. 
has_database_privilege(user: oid, database: string, privilege: string) → bool  Returns whether or not the user has privileges for database. 
has_database_privilege(user: oid, database: oid, privilege: string) → bool  Returns whether or not the user has privileges for database. 
has_foreign_data_wrapper_privilege(fdw: string, privilege: string) → bool  Returns whether or not the current user has privileges for foreigndata wrapper. 
has_foreign_data_wrapper_privilege(fdw: oid, privilege: string) → bool  Returns whether or not the current user has privileges for foreigndata wrapper. 
has_foreign_data_wrapper_privilege(user: string, fdw: string, privilege: string) → bool  Returns whether or not the user has privileges for foreigndata wrapper. 
has_foreign_data_wrapper_privilege(user: string, fdw: oid, privilege: string) → bool  Returns whether or not the user has privileges for foreigndata wrapper. 
has_foreign_data_wrapper_privilege(user: oid, fdw: string, privilege: string) → bool  Returns whether or not the user has privileges for foreigndata wrapper. 
has_foreign_data_wrapper_privilege(user: oid, fdw: oid, privilege: string) → bool  Returns whether or not the user has privileges for foreigndata wrapper. 
has_function_privilege(function: string, privilege: string) → bool  Returns whether or not the current user has privileges for function. 
has_function_privilege(function: oid, privilege: string) → bool  Returns whether or not the current user has privileges for function. 
has_function_privilege(user: string, function: string, privilege: string) → bool  Returns whether or not the user has privileges for function. 
has_function_privilege(user: string, function: oid, privilege: string) → bool  Returns whether or not the user has privileges for function. 
has_function_privilege(user: oid, function: string, privilege: string) → bool  Returns whether or not the user has privileges for function. 
has_function_privilege(user: oid, function: oid, privilege: string) → bool  Returns whether or not the user has privileges for function. 
has_language_privilege(language: string, privilege: string) → bool  Returns whether or not the current user has privileges for language. 
has_language_privilege(language: oid, privilege: string) → bool  Returns whether or not the current user has privileges for language. 
has_language_privilege(user: string, language: string, privilege: string) → bool  Returns whether or not the user has privileges for language. 
has_language_privilege(user: string, language: oid, privilege: string) → bool  Returns whether or not the user has privileges for language. 
has_language_privilege(user: oid, language: string, privilege: string) → bool  Returns whether or not the user has privileges for language. 
has_language_privilege(user: oid, language: oid, privilege: string) → bool  Returns whether or not the user has privileges for language. 
has_schema_privilege(schema: string, privilege: string) → bool  Returns whether or not the current user has privileges for schema. 
has_schema_privilege(schema: oid, privilege: string) → bool  Returns whether or not the current user has privileges for schema. 
has_schema_privilege(user: string, schema: string, privilege: string) → bool  Returns whether or not the user has privileges for schema. 
has_schema_privilege(user: string, schema: oid, privilege: string) → bool  Returns whether or not the user has privileges for schema. 
has_schema_privilege(user: oid, schema: string, privilege: string) → bool  Returns whether or not the user has privileges for schema. 
has_schema_privilege(user: oid, schema: oid, privilege: string) → bool  Returns whether or not the user has privileges for schema. 
has_sequence_privilege(sequence: string, privilege: string) → bool  Returns whether or not the current user has privileges for sequence. 
has_sequence_privilege(sequence: oid, privilege: string) → bool  Returns whether or not the current user has privileges for sequence. 
has_sequence_privilege(user: string, sequence: string, privilege: string) → bool  Returns whether or not the user has privileges for sequence. 
has_sequence_privilege(user: string, sequence: oid, privilege: string) → bool  Returns whether or not the user has privileges for sequence. 
has_sequence_privilege(user: oid, sequence: string, privilege: string) → bool  Returns whether or not the user has privileges for sequence. 
has_sequence_privilege(user: oid, sequence: oid, privilege: string) → bool  Returns whether or not the user has privileges for sequence. 
has_server_privilege(server: string, privilege: string) → bool  Returns whether or not the current user has privileges for foreign server. 
has_server_privilege(server: oid, privilege: string) → bool  Returns whether or not the current user has privileges for foreign server. 
has_server_privilege(user: string, server: string, privilege: string) → bool  Returns whether or not the user has privileges for foreign server. 
has_server_privilege(user: string, server: oid, privilege: string) → bool  Returns whether or not the user has privileges for foreign server. 
has_server_privilege(user: oid, server: string, privilege: string) → bool  Returns whether or not the user has privileges for foreign server. 
has_server_privilege(user: oid, server: oid, privilege: string) → bool  Returns whether or not the user has privileges for foreign server. 
has_table_privilege(table: string, privilege: string) → bool  Returns whether or not the current user has privileges for table. 
has_table_privilege(table: oid, privilege: string) → bool  Returns whether or not the current user has privileges for table. 
has_table_privilege(user: string, table: string, privilege: string) → bool  Returns whether or not the user has privileges for table. 
has_table_privilege(user: string, table: oid, privilege: string) → bool  Returns whether or not the user has privileges for table. 
has_table_privilege(user: oid, table: string, privilege: string) → bool  Returns whether or not the user has privileges for table. 
has_table_privilege(user: oid, table: oid, privilege: string) → bool  Returns whether or not the user has privileges for table. 
has_tablespace_privilege(tablespace: string, privilege: string) → bool  Returns whether or not the current user has privileges for tablespace. 
has_tablespace_privilege(tablespace: oid, privilege: string) → bool  Returns whether or not the current user has privileges for tablespace. 
has_tablespace_privilege(user: string, tablespace: string, privilege: string) → bool  Returns whether or not the user has privileges for tablespace. 
has_tablespace_privilege(user: string, tablespace: oid, privilege: string) → bool  Returns whether or not the user has privileges for tablespace. 
has_tablespace_privilege(user: oid, tablespace: string, privilege: string) → bool  Returns whether or not the user has privileges for tablespace. 
has_tablespace_privilege(user: oid, tablespace: oid, privilege: string) → bool  Returns whether or not the user has privileges for tablespace. 
has_type_privilege(type: string, privilege: string) → bool  Returns whether or not the current user has privileges for type. 
has_type_privilege(type: oid, privilege: string) → bool  Returns whether or not the current user has privileges for type. 
has_type_privilege(user: string, type: string, privilege: string) → bool  Returns whether or not the user has privileges for type. 
has_type_privilege(user: string, type: oid, privilege: string) → bool  Returns whether or not the user has privileges for type. 
has_type_privilege(user: oid, type: string, privilege: string) → bool  Returns whether or not the user has privileges for type. 
has_type_privilege(user: oid, type: oid, privilege: string) → bool  Returns whether or not the user has privileges for type. 
oid(int: int) → oid  Converts an integer to an OID. 
pg_sleep(seconds: float) → bool  pg_sleep makes the current session’s process sleep until seconds seconds have elapsed. seconds is a value of type double precision, so fractionalsecond delays can be specified. 
Aggregate functions
For examples showing how to use aggregate functions, see the SELECT
clause documentation.
Noncommutative aggregate functions are sensitive to the order in which the rows are processed in the surrounding SELECT
clause. To specify the order in which input rows are processed, you can add an ORDER BY
clause within the function argument list. For examples, see the SELECT
clause documentation.
Function → Returns  Description 

array_agg(arg1: bool) → bool[]  Aggregates the selected values into an array. 
array_agg(arg1: bytes) → bytes[]  Aggregates the selected values into an array. 
array_agg(arg1: date) → date[]  Aggregates the selected values into an array. 
array_agg(arg1: decimal) → decimal[]  Aggregates the selected values into an array. 
array_agg(arg1: float) → float[]  Aggregates the selected values into an array. 
array_agg(arg1: inet) → inet[]  Aggregates the selected values into an array. 
array_agg(arg1: int) → int[]  Aggregates the selected values into an array. 
array_agg(arg1: interval) → interval[]  Aggregates the selected values into an array. 
array_agg(arg1: string) → string[]  Aggregates the selected values into an array. 
array_agg(arg1: time) → time[]  Aggregates the selected values into an array. 
array_agg(arg1: timestamp) → timestamp[]  Aggregates the selected values into an array. 
array_agg(arg1: timestamptz) → timestamptz[]  Aggregates the selected values into an array. 
array_agg(arg1: uuid) → uuid[]  Aggregates the selected values into an array. 
array_agg(arg1: box2d) → box2d[]  Aggregates the selected values into an array. 
array_agg(arg1: geography) → geography[]  Aggregates the selected values into an array. 
array_agg(arg1: geometry) → geometry[]  Aggregates the selected values into an array. 
array_agg(arg1: oid) → oid[]  Aggregates the selected values into an array. 
array_agg(arg1: timetz) → timetz[]  Aggregates the selected values into an array. 
array_agg(arg1: varbit) → varbit[]  Aggregates the selected values into an array. 
avg(arg1: decimal) → decimal  Calculates the average of the selected values. 
avg(arg1: float) → float  Calculates the average of the selected values. 
avg(arg1: int) → decimal  Calculates the average of the selected values. 
avg(arg1: interval) → interval  Calculates the average of the selected values. 
bit_and(arg1: int) → int  Calculates the bitwise AND of all nonnull input values, or null if none. 
bit_and(arg1: varbit) → varbit  Calculates the bitwise AND of all nonnull input values, or null if none. 
bit_or(arg1: int) → int  Calculates the bitwise OR of all nonnull input values, or null if none. 
bit_or(arg1: varbit) → varbit  Calculates the bitwise OR of all nonnull input values, or null if none. 
bool_and(arg1: bool) → bool  Calculates the boolean value of 
bool_or(arg1: bool) → bool  Calculates the boolean value of 
concat_agg(arg1: bytes) → bytes  Concatenates all selected values. 
concat_agg(arg1: string) → string  Concatenates all selected values. 
corr(arg1: float, arg2: float) → float  Calculates the correlation coefficient of the selected values. 
corr(arg1: float, arg2: int) → float  Calculates the correlation coefficient of the selected values. 
corr(arg1: int, arg2: float) → float  Calculates the correlation coefficient of the selected values. 
corr(arg1: int, arg2: int) → float  Calculates the correlation coefficient of the selected values. 
count(arg1: anyelement) → int  Calculates the number of selected elements. 
count_rows() → int  Calculates the number of rows. 
every(arg1: bool) → bool  Calculates the boolean value of 
json_agg(arg1: anyelement) → jsonb  Aggregates values as a JSON or JSONB array. 
json_object_agg(arg1: string, arg2: anyelement) → jsonb  Aggregates values as a JSON or JSONB object. 
jsonb_agg(arg1: anyelement) → jsonb  Aggregates values as a JSON or JSONB array. 
jsonb_object_agg(arg1: string, arg2: anyelement) → jsonb  Aggregates values as a JSON or JSONB object. 
max(arg1: bool) → bool  Identifies the maximum selected value. 
max(arg1: bytes) → bytes  Identifies the maximum selected value. 
max(arg1: date) → date  Identifies the maximum selected value. 
max(arg1: decimal) → decimal  Identifies the maximum selected value. 
max(arg1: float) → float  Identifies the maximum selected value. 
max(arg1: inet) → inet  Identifies the maximum selected value. 
max(arg1: int) → int  Identifies the maximum selected value. 
max(arg1: interval) → interval  Identifies the maximum selected value. 
max(arg1: string) → string  Identifies the maximum selected value. 
max(arg1: time) → time  Identifies the maximum selected value. 
max(arg1: timestamp) → timestamp  Identifies the maximum selected value. 
max(arg1: timestamptz) → timestamptz  Identifies the maximum selected value. 
max(arg1: uuid) → uuid  Identifies the maximum selected value. 
max(arg1: anyenum) → anyelement  Identifies the maximum selected value. 
max(arg1: box2d) → box2d  Identifies the maximum selected value. 
max(arg1: collatedstring{*}) → anyelement  Identifies the maximum selected value. 
max(arg1: geography) → geography  Identifies the maximum selected value. 
max(arg1: geometry) → geometry  Identifies the maximum selected value. 
max(arg1: jsonb) → jsonb  Identifies the maximum selected value. 
max(arg1: oid) → oid  Identifies the maximum selected value. 
max(arg1: timetz) → timetz  Identifies the maximum selected value. 
max(arg1: varbit) → varbit  Identifies the maximum selected value. 
min(arg1: bool) → bool  Identifies the minimum selected value. 
min(arg1: bytes) → bytes  Identifies the minimum selected value. 
min(arg1: date) → date  Identifies the minimum selected value. 
min(arg1: decimal) → decimal  Identifies the minimum selected value. 
min(arg1: float) → float  Identifies the minimum selected value. 
min(arg1: inet) → inet  Identifies the minimum selected value. 
min(arg1: int) → int  Identifies the minimum selected value. 
min(arg1: interval) → interval  Identifies the minimum selected value. 
min(arg1: string) → string  Identifies the minimum selected value. 
min(arg1: time) → time  Identifies the minimum selected value. 
min(arg1: timestamp) → timestamp  Identifies the minimum selected value. 
min(arg1: timestamptz) → timestamptz  Identifies the minimum selected value. 
min(arg1: uuid) → uuid  Identifies the minimum selected value. 
min(arg1: anyenum) → anyelement  Identifies the minimum selected value. 
min(arg1: box2d) → box2d  Identifies the minimum selected value. 
min(arg1: collatedstring{*}) → anyelement  Identifies the minimum selected value. 
min(arg1: geography) → geography  Identifies the minimum selected value. 
min(arg1: geometry) → geometry  Identifies the minimum selected value. 
min(arg1: jsonb) → jsonb  Identifies the minimum selected value. 
min(arg1: oid) → oid  Identifies the minimum selected value. 
min(arg1: timetz) → timetz  Identifies the minimum selected value. 
min(arg1: varbit) → varbit  Identifies the minimum selected value. 
percentile_cont(arg1: float) → float  Continuous percentile: returns a float corresponding to the specified fraction in the ordering, interpolating between adjacent input floats if needed. 
percentile_cont(arg1: float) → interval  Continuous percentile: returns an interval corresponding to the specified fraction in the ordering, interpolating between adjacent input intervals if needed. 
percentile_cont(arg1: float[]) → float[]  Continuous percentile: returns floats corresponding to the specified fractions in the ordering, interpolating between adjacent input floats if needed. 
percentile_cont(arg1: float[]) → interval[]  Continuous percentile: returns intervals corresponding to the specified fractions in the ordering, interpolating between adjacent input intervals if needed. 
percentile_disc(arg1: float) → anyelement  Discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction. 
percentile_disc(arg1: float[]) → anyelement  Discrete percentile: returns input values whose position in the ordering equals or exceeds the specified fractions. 
sqrdiff(arg1: decimal) → decimal  Calculates the sum of squared differences from the mean of the selected values. 
sqrdiff(arg1: float) → float  Calculates the sum of squared differences from the mean of the selected values. 
sqrdiff(arg1: int) → decimal  Calculates the sum of squared differences from the mean of the selected values. 
st_collect(arg1: geometry) → geometry  Collects geometries into a GeometryCollection or multitype as appropriate. 
st_extent(arg1: geometry) → box2d  Forms a Box2D that encapsulates all provided geometries. 
st_makeline(arg1: geometry) → geometry  Forms a LineString from Point, MultiPoint or LineStrings. Other shapes will be ignored. 
st_memcollect(arg1: geometry) → geometry  Collects geometries into a GeometryCollection or multitype as appropriate. 
st_memunion(arg1: geometry) → geometry  Applies a spatial union to the geometries provided. 
st_union(arg1: geometry) → geometry  Applies a spatial union to the geometries provided. 
stddev(arg1: decimal) → decimal  Calculates the standard deviation of the selected values. 
stddev(arg1: float) → float  Calculates the standard deviation of the selected values. 
stddev(arg1: int) → decimal  Calculates the standard deviation of the selected values. 
stddev_pop(arg1: decimal) → decimal  Calculates the population standard deviation of the selected values. 
stddev_pop(arg1: float) → float  Calculates the population standard deviation of the selected values. 
stddev_pop(arg1: int) → decimal  Calculates the population standard deviation of the selected values. 
stddev_samp(arg1: decimal) → decimal  Calculates the standard deviation of the selected values. 
stddev_samp(arg1: float) → float  Calculates the standard deviation of the selected values. 
stddev_samp(arg1: int) → decimal  Calculates the standard deviation of the selected values. 
string_agg(arg1: bytes, arg2: bytes) → bytes  Concatenates all selected values using the provided delimiter. 
string_agg(arg1: string, arg2: string) → string  Concatenates all selected values using the provided delimiter. 
sum(arg1: decimal) → decimal  Calculates the sum of the selected values. 
sum(arg1: float) → float  Calculates the sum of the selected values. 
sum(arg1: int) → decimal  Calculates the sum of the selected values. 
sum(arg1: interval) → interval  Calculates the sum of the selected values. 
sum_int(arg1: int) → int  Calculates the sum of the selected values. 
var_pop(arg1: decimal) → decimal  Calculates the population variance of the selected values. 
var_pop(arg1: float) → float  Calculates the population variance of the selected values. 
var_pop(arg1: int) → decimal  Calculates the population variance of the selected values. 
var_samp(arg1: decimal) → decimal  Calculates the variance of the selected values. 
var_samp(arg1: float) → float  Calculates the variance of the selected values. 
var_samp(arg1: int) → decimal  Calculates the variance of the selected values. 
variance(arg1: decimal) → decimal  Calculates the variance of the selected values. 
variance(arg1: float) → float  Calculates the variance of the selected values. 
variance(arg1: int) → decimal  Calculates the variance of the selected values. 
xor_agg(arg1: bytes) → bytes  Calculates the bitwise XOR of the selected values. 
xor_agg(arg1: int) → int  Calculates the bitwise XOR of the selected values. 
Window functions
Function → Returns  Description 

cume_dist() → float  Calculates the relative rank of the current row: (number of rows preceding or peer with current row) / (total rows). 
dense_rank() → int  Calculates the rank of the current row without gaps; this function counts peer groups. 
first_value(val: bool) → bool  Returns 
first_value(val: bytes) → bytes  Returns 
first_value(val: date) → date  Returns 
first_value(val: decimal) → decimal  Returns 
first_value(val: float) → float  Returns 
first_value(val: inet) → inet  Returns 
first_value(val: int) → int  Returns 
first_value(val: interval) → interval  Returns 
first_value(val: string) → string  Returns 
first_value(val: time) → time  Returns 
first_value(val: timestamp) → timestamp  Returns 
first_value(val: timestamptz) → timestamptz  Returns 
first_value(val: uuid) → uuid  Returns 
first_value(val: box2d) → box2d  Returns 
first_value(val: geography) → geography  Returns 
first_value(val: geometry) → geometry  Returns 
first_value(val: jsonb) → jsonb  Returns 
first_value(val: oid) → oid  Returns 
first_value(val: timetz) → timetz  Returns 
first_value(val: varbit) → varbit  Returns 
lag(val: bool) → bool  Returns 
lag(val: bool, n: int) → bool  Returns 
lag(val: bool, n: int, default: bool) → bool  Returns 
lag(val: bytes) → bytes  Returns 
lag(val: bytes, n: int) → bytes  Returns 
lag(val: bytes, n: int, default: bytes) → bytes  Returns 
lag(val: date) → date  Returns 
lag(val: date, n: int) → date  Returns 
lag(val: date, n: int, default: date) → date  Returns 
lag(val: decimal) → decimal  Returns 
lag(val: decimal, n: int) → decimal  Returns 
lag(val: decimal, n: int, default: decimal) → decimal  Returns 
lag(val: float) → float  Returns 
lag(val: float, n: int) → float  Returns 
lag(val: float, n: int, default: float) → float  Returns 
lag(val: inet) → inet  Returns 
lag(val: inet, n: int) → inet  Returns 
lag(val: inet, n: int, default: inet) → inet  Returns 
lag(val: int) → int  Returns 
lag(val: int, n: int) → int  Returns 
lag(val: int, n: int, default: int) → int  Returns 
lag(val: interval) → interval  Returns 
lag(val: interval, n: int) → interval  Returns 
lag(val: interval, n: int, default: interval) → interval  Returns 
lag(val: string) → string  Returns 
lag(val: string, n: int) → string  Returns 
lag(val: string, n: int, default: string) → string  Returns 
lag(val: time) → time  Returns 
lag(val: time, n: int) → time  Returns 
lag(val: time, n: int, default: time) → time  Returns 
lag(val: timestamp) → timestamp  Returns 
lag(val: timestamp, n: int) → timestamp  Returns 
lag(val: timestamp, n: int, default: timestamp) → timestamp  Returns 
lag(val: timestamptz) → timestamptz  Returns 
lag(val: timestamptz, n: int) → timestamptz  Returns 
lag(val: timestamptz, n: int, default: timestamptz) → timestamptz  Returns 
lag(val: uuid) → uuid  Returns 
lag(val: uuid, n: int) → uuid  Returns 
lag(val: uuid, n: int, default: uuid) → uuid  Returns 
lag(val: box2d) → box2d  Returns 
lag(val: box2d, n: int) → box2d  Returns 
lag(val: box2d, n: int, default: box2d) → box2d  Returns 
lag(val: geography) → geography  Returns 
lag(val: geography, n: int) → geography  Returns 
lag(val: geography, n: int, default: geography) → geography  Returns 
lag(val: geometry) → geometry  Returns 
lag(val: geometry, n: int) → geometry  Returns 
lag(val: geometry, n: int, default: geometry) → geometry  Returns 
lag(val: jsonb) → jsonb  Returns 
lag(val: jsonb, n: int) → jsonb  Returns 
lag(val: jsonb, n: int, default: jsonb) → jsonb  Returns 
lag(val: oid) → oid  Returns 
lag(val: oid, n: int) → oid  Returns 
lag(val: oid, n: int, default: oid) → oid  Returns 
lag(val: timetz) → timetz  Returns 
lag(val: timetz, n: int) → timetz  Returns 
lag(val: timetz, n: int, default: timetz) → timetz  Returns 
lag(val: varbit) → varbit  Returns 
lag(val: varbit, n: int) → varbit  Returns 
lag(val: varbit, n: int, default: varbit) → varbit  Returns 
last_value(val: bool) → bool  Returns 
last_value(val: bytes) → bytes  Returns 
last_value(val: date) → date  Returns 
last_value(val: decimal) → decimal  Returns 
last_value(val: float) → float  Returns 
last_value(val: inet) → inet  Returns 
last_value(val: int) → int  Returns 
last_value(val: interval) → interval  Returns 
last_value(val: string) → string  Returns 
last_value(val: time) → time  Returns 
last_value(val: timestamp) → timestamp  Returns 
last_value(val: timestamptz) → timestamptz  Returns 
last_value(val: uuid) → uuid  Returns 
last_value(val: box2d) → box2d  Returns 
last_value(val: geography) → geography  Returns 
last_value(val: geometry) → geometry  Returns 
last_value(val: jsonb) → jsonb  Returns 
last_value(val: oid) → oid  Returns 
last_value(val: timetz) → timetz  Returns 
last_value(val: varbit) → varbit  Returns 
lead(val: bool) → bool  Returns 
lead(val: bool, n: int) → bool  Returns 
lead(val: bool, n: int, default: bool) → bool  Returns 
lead(val: bytes) → bytes  Returns 
lead(val: bytes, n: int) → bytes  Returns 
lead(val: bytes, n: int, default: bytes) → bytes  Returns 
lead(val: date) → date  Returns 
lead(val: date, n: int) → date  Returns 
lead(val: date, n: int, default: date) → date  Returns 
lead(val: decimal) → decimal  Returns 
lead(val: decimal, n: int) → decimal  Returns 
lead(val: decimal, n: int, default: decimal) → decimal  Returns 
lead(val: float) → float  Returns 
lead(val: float, n: int) → float  Returns 
lead(val: float, n: int, default: float) → float  Returns 
lead(val: inet) → inet  Returns 
lead(val: inet, n: int) → inet  Returns 
lead(val: inet, n: int, default: inet) → inet  Returns 
lead(val: int) → int  Returns 
lead(val: int, n: int) → int  Returns 
lead(val: int, n: int, default: int) → int  Returns 
lead(val: interval) → interval  Returns 
lead(val: interval, n: int) → interval  Returns 
lead(val: interval, n: int, default: interval) → interval  Returns 
lead(val: string) → string  Returns 
lead(val: string, n: int) → string  Returns 
lead(val: string, n: int, default: string) → string  Returns 
lead(val: time) → time  Returns 
lead(val: time, n: int) → time  Returns 
lead(val: time, n: int, default: time) → time  Returns 
lead(val: timestamp) → timestamp  Returns 
lead(val: timestamp, n: int) → timestamp  Returns 
lead(val: timestamp, n: int, default: timestamp) → timestamp  Returns 
lead(val: timestamptz) → timestamptz  Returns 
lead(val: timestamptz, n: int) → timestamptz  Returns 
lead(val: timestamptz, n: int, default: timestamptz) → timestamptz  Returns 
lead(val: uuid) → uuid  Returns 
lead(val: uuid, n: int) → uuid  Returns 
lead(val: uuid, n: int, default: uuid) → uuid  Returns 
lead(val: box2d) → box2d  Returns 
lead(val: box2d, n: int) → box2d  Returns 
lead(val: box2d, n: int, default: box2d) → box2d  Returns 
lead(val: geography) → geography  Returns 
lead(val: geography, n: int) → geography  Returns 
lead(val: geography, n: int, default: geography) → geography  Returns 
lead(val: geometry) → geometry  Returns 
lead(val: geometry, n: int) → geometry  Returns 
lead(val: geometry, n: int, default: geometry) → geometry  Returns 
lead(val: jsonb) → jsonb  Returns 
lead(val: jsonb, n: int) → jsonb  Returns 
lead(val: jsonb, n: int, default: jsonb) → jsonb  Returns 
lead(val: oid) → oid  Returns 
lead(val: oid, n: int) → oid  Returns 
lead(val: oid, n: int, default: oid) → oid  Returns 
lead(val: timetz) → timetz  Returns 
lead(val: timetz, n: int) → timetz  Returns 
lead(val: timetz, n: int, default: timetz) → timetz  Returns 
lead(val: varbit) → varbit  Returns 
lead(val: varbit, n: int) → varbit  Returns 
lead(val: varbit, n: int, default: varbit) → varbit  Returns 
nth_value(val: bool, n: int) → bool  Returns 
nth_value(val: bytes, n: int) → bytes  Returns 
nth_value(val: date, n: int) → date  Returns 
nth_value(val: decimal, n: int) → decimal  Returns 
nth_value(val: float, n: int) → float  Returns 
nth_value(val: inet, n: int) → inet  Returns 
nth_value(val: int, n: int) → int  Returns 
nth_value(val: interval, n: int) → interval  Returns 
nth_value(val: string, n: int) → string  Returns 
nth_value(val: time, n: int) → time  Returns 
nth_value(val: timestamp, n: int) → timestamp  Returns 
nth_value(val: timestamptz, n: int) → timestamptz  Returns 
nth_value(val: uuid, n: int) → uuid  Returns 
nth_value(val: box2d, n: int) → box2d  Returns 
nth_value(val: geography, n: int) → geography  Returns 
nth_value(val: geometry, n: int) → geometry  Returns 
nth_value(val: jsonb, n: int) → jsonb  Returns 
nth_value(val: oid, n: int) → oid  Returns 
nth_value(val: timetz, n: int) → timetz  Returns 
nth_value(val: varbit, n: int) → varbit  Returns 
ntile(n: int) → int  Calculates an integer ranging from 1 to 
percent_rank() → float  Calculates the relative rank of the current row: (rank  1) / (total rows  1). 
rank() → int  Calculates the rank of the current row with gaps; same as row_number of its first peer. 
row_number() → int  Calculates the number of the current row within its partition, counting from 1. 
Operators
The following table lists all CockroachDB operators from highest to lowest precedence, i.e., the order in which they will be evaluated within a statement. Operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right.
Order of Precedence  Operator  Name  Operator Arity 

1  . 
Member field access operator  binary 
2  :: 
Type cast  binary 
3   
Unary minus  unary (prefix) 
~ 
Bitwise not  unary (prefix)  
4  ^ 
Exponentiation  binary 
5  * 
Multiplication  binary 
/ 
Division  binary  
// 
Floor division  binary  
% 
Modulo  binary  
6  + 
Addition  binary 
 
Subtraction  binary  
7  << 
Bitwise leftshift  binary 
>> 
Bitwise rightshift  binary  
8  & 
Bitwise AND  binary 
9  # 
Bitwise XOR  binary 
10   
Bitwise OR  binary 
11   
Concatenation  binary 
< ANY , SOME , ALL 
Multivalued "less than" comparison  binary  
> ANY , SOME , ALL 
Multivalued "greater than" comparison  binary  
= ANY , SOME , ALL 
Multivalued "equal" comparison  binary  
<= ANY , SOME , ALL 
Multivalued "less than or equal" comparison  binary  
>= ANY , SOME , ALL 
Multivalued "greater than or equal" comparison  binary  
<> ANY / != ANY , <> SOME / != SOME , <> ALL / != ALL 
Multivalued "not equal" comparison  binary  
[NOT] LIKE ANY , [NOT] LIKE SOME , [NOT] LIKE ALL 
Multivalued LIKE comparison 
binary  
[NOT] ILIKE ANY , [NOT] ILIKE SOME , [NOT] ILIKE ALL 
Multivalued ILIKE comparison 
binary  
12  [NOT] BETWEEN 
Value is [not] within the range specified  binary 
[NOT] BETWEEN SYMMETRIC 
Like [NOT] BETWEEN , but in nonsorted 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 floatingpoint NaN value  unary (postfix)  
IS OF(...) 
Type predicate  unary (postfix)  
15  NOT 
Logical NOT  unary 
16  AND 
Logical AND  binary 
17  OR 
Logical OR  binary 
Supported operations
#  Return 
int # int  int 
varbit # varbit  varbit 
#>  Return 
jsonb #> string[]  jsonb 
#>>  Return 
jsonb #>> string[]  string 
%  Return 
decimal % decimal  decimal 
decimal % int  decimal 
float % float  float 
int % decimal  decimal 
int % int  int 
&  Return 
inet & inet  inet 
int & int  int 
varbit & varbit  varbit 
&&  Return 
anyelement && anyelement  bool 
box2d && box2d  bool 
box2d && geometry  bool 
geometry && box2d  bool 
geometry && geometry  bool 
inet && inet  bool 
+  Return 
date + int  date 
date + interval  timestamp 
date + time  timestamp 
date + timetz  timestamptz 
decimal + decimal  decimal 
decimal + int  decimal 
float + float  float 
inet + int  inet 
int + date  date 
int + decimal  decimal 
int + inet  inet 
int + int  int 
interval + date  timestamp 
interval + interval  interval 
interval + time  time 
interval + timestamp  timestamp 
interval + timestamptz  timestamptz 
interval + timetz  timetz 
time + date  timestamp 
time + interval  time 
timestamp + interval  timestamp 
timestamptz + interval  timestamptz 
timetz + date  timestamptz 
timetz + interval  timetz 
  Return 
 decimal  decimal 
 float  float 
 int  int 
 interval  interval 
date  date  int 
date  int  date 
date  interval  timestamp 
date  time  timestamp 
decimal  decimal  decimal 
decimal  int  decimal 
float  float  float 
inet  inet  int 
inet  int  inet 
int  decimal  decimal 
int  int  int 
interval  interval  interval 
jsonb  int  jsonb 
jsonb  string  jsonb 
jsonb  string[]  jsonb 
time  interval  time 
time  time  interval 
timestamp  interval  timestamp 
timestamp  timestamp  interval 
timestamp  timestamptz  interval 
timestamptz  interval  timestamptz 
timestamptz  timestamp  interval 
timestamptz  timestamptz  interval 
timetz  interval  timetz 
>  Return 
jsonb > int  jsonb 
jsonb > string  jsonb 
>>  Return 
jsonb >> int  string 
jsonb >> string  string 
/  Return 
decimal / decimal  decimal 
decimal / int  decimal 
float / float  float 
int / decimal  decimal 
int / int  decimal 
interval / float  interval 
interval / int  interval 
//  Return 
decimal // decimal  decimal 
decimal // int  decimal 
float // float  float 
int // decimal  decimal 
int // int  int 
<<  Return 
inet << inet  bool 
int << int  int 
varbit << int  varbit 
<@  Return 
anyelement <@ anyelement  bool 
jsonb <@ jsonb  bool 
>>  Return 
inet >> inet  bool 
int >> int  int 
varbit >> int  varbit 
?  Return 
jsonb ? string  bool 
?&  Return 
jsonb ?& string[]  bool 
?  Return 
jsonb ? string[]  bool 
@>  Return 
anyelement @> anyelement  bool 
jsonb @> jsonb  bool 
ILIKE  Return 
string ILIKE string  bool 
IN  Return 
anyenum IN tuple  bool 
bool IN tuple  bool 
box2d IN tuple  bool 
bytes IN tuple  bool 
collatedstring IN tuple  bool 
date IN tuple  bool 
decimal IN tuple  bool 
float IN tuple  bool 
geography IN tuple  bool 
geometry IN tuple  bool 
inet IN tuple  bool 
int IN tuple  bool 
interval IN tuple  bool 
jsonb IN tuple  bool 
oid IN tuple  bool 
string IN tuple  bool 
time IN tuple  bool 
timestamp IN tuple  bool 
timestamptz IN tuple  bool 
timetz IN tuple  bool 
tuple IN tuple  bool 
uuid IN tuple  bool 
varbit IN tuple  bool 
IS NOT DISTINCT FROM  Return 
anyenum IS NOT DISTINCT FROM anyenum  bool 
bool IS NOT DISTINCT FROM bool  bool 
bool[] IS NOT DISTINCT FROM bool[]  bool 
box2d IS NOT DISTINCT FROM box2d  bool 
bytes IS NOT DISTINCT FROM bytes  bool 
bytes[] IS NOT DISTINCT FROM bytes[]  bool 
collatedstring IS NOT DISTINCT FROM collatedstring  bool 
date IS NOT DISTINCT FROM date  bool 
date IS NOT DISTINCT FROM timestamp  bool 
date IS NOT DISTINCT FROM timestamptz  bool 
date[] IS NOT DISTINCT FROM date[]  bool 
decimal IS NOT DISTINCT FROM decimal  bool 
decimal IS NOT DISTINCT FROM float  bool 
decimal IS NOT DISTINCT FROM int  bool 
decimal[] IS NOT DISTINCT FROM decimal[]  bool 
float IS NOT DISTINCT FROM decimal  bool 
float IS NOT DISTINCT FROM float  bool 
float IS NOT DISTINCT FROM int  bool 
float[] IS NOT DISTINCT FROM float[]  bool 
geography IS NOT DISTINCT FROM geography  bool 
geometry IS NOT DISTINCT FROM geometry  bool 
inet IS NOT DISTINCT FROM inet  bool 
inet[] IS NOT DISTINCT FROM inet[]  bool 
int IS NOT DISTINCT FROM decimal  bool 
int IS NOT DISTINCT FROM float  bool 
int IS NOT DISTINCT FROM int  bool 
int IS NOT DISTINCT FROM oid  bool 
int[] IS NOT DISTINCT FROM int[]  bool 
interval IS NOT DISTINCT FROM interval  bool 
interval[] IS NOT DISTINCT FROM interval[]  bool 
jsonb IS NOT DISTINCT FROM jsonb  bool 
oid IS NOT DISTINCT FROM int  bool 
oid IS NOT DISTINCT FROM oid  bool 
string IS NOT DISTINCT FROM string  bool 
string[] IS NOT DISTINCT FROM string[]  bool 
time IS NOT DISTINCT FROM time  bool 
time IS NOT DISTINCT FROM timetz  bool 
time[] IS NOT DISTINCT FROM time[]  bool 
timestamp IS NOT DISTINCT FROM date  bool 
timestamp IS NOT DISTINCT FROM timestamp  bool 
timestamp IS NOT DISTINCT FROM timestamptz  bool 
timestamp[] IS NOT DISTINCT FROM timestamp[]  bool 
timestamptz IS NOT DISTINCT FROM date  bool 
timestamptz IS NOT DISTINCT FROM timestamp  bool 
timestamptz IS NOT DISTINCT FROM timestamptz  bool 
timestamptz IS NOT DISTINCT FROM timestamptz  bool 
timetz IS NOT DISTINCT FROM time  bool 
timetz IS NOT DISTINCT FROM timetz  bool 
tuple IS NOT DISTINCT FROM tuple  bool 
unknown IS NOT DISTINCT FROM unknown  bool 
uuid IS NOT DISTINCT FROM uuid  bool 
uuid[] IS NOT DISTINCT FROM uuid[]  bool 
varbit IS NOT DISTINCT FROM varbit  bool 
LIKE  Return 
string LIKE string  bool 
SIMILAR TO  Return 
string SIMILAR TO string  bool 
^  Return 
decimal ^ decimal  decimal 
decimal ^ int  decimal 
float ^ float  float 
int ^ decimal  decimal 
int ^ int  int 
  Return 
inet  inet  inet 
int  int  int 
varbit  varbit  varbit 
/  Return 
/ decimal  decimal 
/ float  float 
/  Return 
/ decimal  decimal 
/ float  float 
~  Return 
~ inet  inet 
~ int  int 
~ varbit  varbit 
box2d ~ box2d  bool 
box2d ~ geometry  bool 
geometry ~ box2d  bool 
geometry ~ geometry  bool 
string ~ string  bool 
~*  Return 
string ~* string  bool 