ANY_VALUE Aggregate Functions Returns an arbitrary, non-NULL value from the input column. APPROX_COUNT_DISTINCT Aggregate Functions Approximate distinct count by using hyper-log-log (95% confidence interval that the value is within 4.5%). APPROX_SUM Aggregate Functions Allows the aggregation engine to use a faster, non-deterministic ordering to summate floating-point columns. AVG Aggregate Functions Average, or arithmetic mean, over the set. CORR Aggregate Functions Alias for CORRELATION. CORRELATION Aggregate Functions Sample correlation. CORRELATIONP Aggregate Functions Population correlation. COUNT Aggregate Functions Number of rows in the set. COVAR_POP Aggregate Functions Alias for COVARIANCEP. COVAR_SAMP Aggregate Functions Alias for COVARIANCE. COVARIANCE Aggregate Functions Sample covariance. COVARIANCEP Aggregate Functions Population covariance. KURTOSIS Aggregate Functions The sample over the set. KURTOSISP Aggregate Functions The population over the set. MAX Aggregate Functions Maximum value in the specified column. MIN Aggregate Functions Minimum value in the specified column. PRODUCT Aggregate Functions Product over the set. SKEW Aggregate Functions The sample over the set. STDDEV Aggregate Functions Alias for STDEV. STDDEV_POP Aggregate Functions Alias for STDEVP. STDDEV_SAMP Aggregate Functions Alias for STDEV. STDEV Aggregate Functions Sample standard deviation. STDEVP Aggregate Functions Population standard deviation. SUM Aggregate Functions Sum over the set. SWEP Aggregate Functions The population over the set. VAR_POP Aggregate Functions Alias for VARIANCEP. VAR_SAMP Aggregate Functions Alias for VARIANCE. VARIANCE Aggregate Functions Sample variance. VARIANCEP Aggregate Functions Population variance. ARRAY[] Array Functions PostgreSQL®-compliant constructor. The type of the array is deduced from the elements. [ARRAY_APPEND ](Array Functions and Operators) Array Functions Add value to the back of an array. [ARRAY_ARGMAX ](Array Functions and Operators) Array Functions Returns the corresponding argmax of the array as a BIGINT index. [ARRAY_ARGMIN ](Array Functions and Operators) Array Functions Returns the corresponding argmin of the array as a BIGINT index. [ARRAY_CAT ](Array Functions and Operators) Array Functions Concatenate 2 arrays into a new one. [ARRAY_CAT_DISTINCT ](Array Functions and Operators) Array Functions Concatenates two or more arrays in the order of the input arguments. [ARRAY_DISTINCT ](Array Functions and Operators) Array Functions Removes duplicates from an array while preserving the first occurrence order. [ARRAY_LENGTH ](Array Functions and Operators) Array Functions Return the number of elements of a given array. [ARRAY_MAX ](Array Functions and Operators) Array Functions Returns the corresponding maximum of the array. [ARRAY_MIN ](Array Functions and Operators) Array Functions Returns the corresponding minimum of the array. [ARRAY_POSITION ](Array Functions and Operators) Array Functions Returns the position of the first matching scalar in the array. [ARRAY_POSITIONS ](Array Functions and Operators) Array Functions Returns all elements stored in the right array, and return their respective positions in the left array. [ARRAY_PREPEND ](Array Functions and Operators) Array Functions Add value to the front of an array. [ARRAY_REMOVE ](Array Functions and Operators) Array Functions Remove value from the array. [ARRAY_REPLACE ](Array Functions and Operators) Array Functions Replace value by another in an array. [ARRAY_SUM ](Array Functions and Operators) Array Functions Returns the sum of the array. The array must be one-dimensional and contain numeric values. NULL values do not contribute to the sum. [ARRAY_TO_STRING ](Array Functions and Operators) Array Functions Converts array to a list of elements separated by ‘delimiter’. [CAST_TO_ARRAY ](Array Functions and Operators) Array Functions Casts the elements of the array to another type. [CHAR ](Array Functions and Operators) Array Functions Converts array to its string representation. [CROSS_ENTROPY_LOSS ](Array Functions and Operators) Array Functions Returns the cross entropy loss of two arrays. [HINGE_LOSS ](Array Functions and Operators) Array Functions Returns the hinge loss of two arrays. [LOG_LOSS ](Array Functions and Operators) Array Functions Returns the log loss of two arrays. [LOGITS_LOSS ](Array Functions and Operators) Array Functions Returns the logits loss of two arrays. [SOFTMAX ](Array Functions and Operators) Array Functions Returns the softmax of the array. [STRING_TO_ARRAY ](Array Functions and Operators) Array Functions Converts the string representation of an array (e.g., 'int[1,2,NULL]') into an array. TYPE[] Array Functions Construct an array of SQL type TYPE giving the elements. [UNNEST ](Array Functions and Operators) Array Functions Expand each element in an input array out to an individual row. ASCII Character and Binary Functions Returns the ASCII code value of the leftmost character of the character value. BIT_LENGTH Character and Binary Functions Returns the length of the character value in bits. BTRIM Character and Binary Functions Alias for TRIM. CHAR_LENGTH Character and Binary Functions Alias for LENGTH. CHARACTER_LENGTH Character and Binary Functions Alias for LENGTH. CHR Character and Binary Functions Converts an integer value to a string. CONCAT Character and Binary Functions Concatenates two values, which must both be either binary, hash, or string data types. This function is equivalent to the || operator. ENDSWITH Character and Binary Functions Returns true if x ends with y and false otherwise. INITCAP Character and Binary Functions For each word in the provided string, capitalize the first character if it is alphabetic. INSTR Character and Binary Functions Returns the index position of the first occurrence where the character value char_substring appears in the character value char by ignoring the case. JSON_EXTRACT_PATH_TEXT Character and Binary Functions Returns the value for the key-value pair referenced by a series of path elements in a JSON string. LCASE Character and Binary Functions Alias for LOWER. LEFT Character and Binary Functions Return the number of characters in the string equal to the value integer. If the integer is negative, the function returns all but the last integer characters. LENGTH Character and Binary Functions For character data types, this value is in terms of characters. For binary data types, this value is in terms of bytes. LOCATE Character and Binary Functions Alias for POSITION. Returns the index position of the first occurrence of the character value substring in character value string. LOWER Character and Binary Functions Alias for LCASE. Convert string to lowercase. LPAD Character and Binary Functions Pad the input text to the specified length with the pad string on the left side. LTRIM Character and Binary Functions Removes leading blanks from the string value string. MD5 Character and Binary Functions Returns the hexadecimal string (all lowercase) representing the md5 hash of char. MID Character and Binary Functions Alias for SUBSTRING. OCTET_LENGTH Character and Binary Functions Returns the length in bytes of a character or binary value. POSITION Character and Binary Functions Alias for LOCATE. REGEXP_COUNT Character and Binary Functions Searches a string for all occurrences of a regular expression pattern. REGEXP_INSTR Character and Binary Functions Searches a string using a regular expression pattern and returns an integer representing the start position or end position of the substring that matches. REGEXP_REPLACE Character and Binary Functions Searches a string for all occurrences of a regular expression pattern. REGEXP_SUBSTR Character and Binary Functions Returns one substring from a string that matches a specified regular expression pattern. REPEAT Character and Binary Functions Repeats the character value char a number of times equal to num. REPLACE Character and Binary Functions Replaces all occurrences of substr_to_remove in the character value string with substr_to_replace. REVERSE Character and Binary Functions Reverse the input string. RIGHT Character and Binary Functions Return the number of trailing characters in the string equal to the value integer. RPAD Character and Binary Functions Pad the input text to the specified length with the pad string on the right side. RSUBSTRING Character and Binary Functions Returns the substring from the right side of a string based on a specified length. RTRIM Character and Binary Functions Removes leading blanks from the string value string. SHA1 Character and Binary Functions Uses the SHA-1 SPACE Character and Binary Functions Returns a string of repeated spaces equal to the number value, repeat. SPLIT_PART Character and Binary Functions Split the value string based on the delimiter value. The function returns a substring from the split operation based on the index value (starting from 1). SPLIT_TO_ARRAY Character and Binary Functions Splits a string into an array of substrings. STARTSWITH Character and Binary Functions Returns true if string starts with substring and false otherwise. STRPOS Character and Binary Functions Equivalent to using LOCATE as LOCATE(substring, string). Note the reversed argument order. SUBSTR Character and Binary Functions Alias for SUBSTRING. SUBSTRING Character and Binary Functions Returns the substring of a character or binary value. TO_CHAR Character and Binary Functions Converts a numeric, date, or timestamp value into a CHAR date type. TRANSLATE Character and Binary Functions Replaces specified characters in a provided string with a separate set of characters. TRIM Character and Binary Functions Alias for BTRIM. Trim leading and trailing blanks from the string. UCASE Character and Binary Functions Alias for UPPER. UPPER Character and Binary Functions Convert string to upper case. CASE Conditional Functions CASE operates similarly to conditional scripting in other programming languages, allowing it to function like an if / then / else statement or as a switch statement. COALESCE Conditional Functions Evaluates to the first argument that is not NULL, or NULL if all arguments are NULL. GREATEST Conditional Functions Returns the largest non-NULL value of all the arguments, or NULL if all the arguments are NULL. IF_NULL Conditional Functions Alias for COALESCE. LEAST Conditional Functions Returns the smallest non-NULL value of all arguments, or NULL if all arguments are NULL. MURMUR3 Conditional Functions Returns a 32-bit MurmurHash3 hash of the input value as an INTEGER data type. NULL_IF Conditional Functions Returns the NULL value if two arguments are equal; otherwise, returns the first argument. ZN Conditional Functions If x is NULL, returns 0. Otherwise, returns x. LAG_VECTORS Data Preparation Groups lagged columns generated by the MULTI_LAGS or MULTI_LAGS_ZEROFILL functions into vector columns. LAGS Data Preparation Generates a series of lagged columns for a single variable in one statement. LAGS_ZEROFILL Data Preparation Generates lagged columns for a single variable and replaces NULL values with 0. MULTI_LAGS Data Preparation Generates lagged columns for multiple variables at once. MULTI_LAGS_ZEROFILL Data Preparation Generates lagged columns for multiple variables and replaces NULL values with 0. ADD_MONTHS Date and Time Functions Adds the specified number of months to the date. CENTURY Date and Time Functions Returns the number of centuries. CURDATE Date and Time Functions Alias for CURRENT_DATE. CURRENT_DATE Date and Time Functions Returns the current date in the format YYYY-MM-DD. CURRENT_TIME Date and Time Functions Returns the current time as a TIME value (e.g., hh:mm:ss.mm). CURRENT_TIMESTAMP Date and Time Functions Returns the current date and time as a TIMESTAMP value (e.g., YYYY-MM-DD hh🇲🇲ss.mmm). DATE_PART Date and Time Functions Alias for EXTRACT. DATE_TRUNC Date and Time Functions Returns the date or timestamp entered, truncated to the specified precision. DATEADD Date and Time Functions Adds a specified number value (as a signed integer) to a specified date part of an input date value, and then returns that modified value. DATEDIFF Date and Time Functions This function returns an INT representing the difference between two date or time values in a specified date or time unit. DAY Date and Time Functions Alias for DAY_OF_MONTH. DAY_OF_MONTH Date and Time Functions Extracts the day-of-month portion of a timestamp or date as an integer. DAY_OF_WEEK Date and Time Functions Returns an integer, in the range of 1 to 7, that represents the day of the week. DAY_OF_YEAR Date and Time Functions Returns an integer in the range 1 to 366 that represents the day of the year. DECADE Date and Time Functions The decade is the year divided by 10. DOW Date and Time Functions Alias for DAY_OF_WEEK. DOY Date and Time Functions Alias for DAY_OF_YEAR. EOMONTH Date and Time Functions Returns the last day of the timestamp or date. EPOCH Date and Time Functions The number of seconds after 1970-01-01 00:00:00 UTC. EXTRACT Date and Time Functions Extract a component from a timestamp or date. HOUR Date and Time Functions Extracts the hour portion of a timestamp as an integer. ISDATE Date and Time Functions Returns TRUE if the input argument can be successfully cast to a date. ISODOW Date and Time Functions Extracts the day of the week based on ISO 8601, which ranges from Monday (1) to Sunday (7). MAKEDATETIME Date and Time Functions Returns a timestamp consisting of the specified date and time. MILLISECOND Date and Time Functions Extracts the millisecond portion of a timestamp as an integer. MINUTE Date and Time Functions Extracts the minute portion of a timestamp or date as an integer. MONTH Date and Time Functions Extracts the month portion of a timestamp or date as an integer. MONTH_NAME Date and Time Functions Returns the calendar name in English of the month for the specified date. MONTHS_BETWEEN Date and Time Functions Returns the difference between the two dates or timestamps in months as a DOUBLE. MSECS Date and Time Functions The seconds field, including fractional parts. The function multiplies the seconds part of the value by 1,000. NANOS_TO_TIMESTAMP Date and Time Functions Convert a number of nanoseconds into a timestamp equivalent to the duration after the epoch time. NEXT_DAY Date and Time Functions Returns the closest date after a specified date that lies on a specific day of the week. NOW Date and Time Functions Alias for CURRENT_TIMESTAMP. QUARTER Date and Time Functions Returns an integer between 1 and 4 that represents the quarter of the year in which the specified date falls. ROUND Date and Time Functions Returns the specified date or timestamp, rounded to the specified precision. SECOND Date and Time Functions Extracts the seconds portion of a timestamp as an integer. TIMESTAMP_TO_NANOS Date and Time Functions Convert timestamp into nanoseconds after epoch as BIGINT. USECS Date and Time Functions The seconds part of a time value, including fractional parts, returned as an integer. The function multiplies the seconds part of the value by 1,000,000. WEEK Date and Time Functions Returns the ISO-8601 week number, as an integer, of the specified timestamp or date value. YEAR Date and Time Functions Extracts the year portion of a timestamp or date as an integer. TO_DATE Formatting Functions Converts a character value with the specified format to a DATE type. TO_NUMBER Formatting Functions Converts a character value with the specified format to a DECIMAL type. TO_TIMESTAMP Formatting Functions Converts a character value with the specified format to a TIMESTAMP type. ST_COORDDIM Geospatial Attribute Functions Alias for ST_NDIMS or ST_NDIMENSION. Returns an INTEGER of the coordinate dimension of the specified geography. ST_DIMENSION Geospatial Attribute Functions Returns an INTEGER that represents the dimension of the specified geography. ST_GEOMETRYTYPE Geospatial Attribute Functions Returns a string representing the geometry type of the input value. ST_ISEMPTY Geospatial Attribute Functions Returns TRUE if the specified geography value is empty, such as ‘POLYGON EMPTY’. ST_MEMSIZE Geospatial Attribute Functions Returns an INTEGER representing the number of bytes in memory required to store the specified geography. ST_NDIMENSION Geospatial Attribute Functions Alias for ST_COORDDIM. ST_NDIMS Geospatial Attribute Functions Alias for ST_COORDDIM. ST_NPOINTS Geospatial Attribute Functions Returns an INTEGER representing the number of POINT values in a specified geography. ST_NUMPOINTS Geospatial Attribute Functions Returns an INTEGER representing the number of POINT values in a specified geography. ST_SRID Geospatial Attribute Functions Returns the EPSG code of the spatial reference identifier (SRID) of the input geography. ST_X Geospatial Attribute Functions Returns the x value of the specified POINT. ST_XMAX Geospatial Attribute Functions Returns the maximum x value of the specified geography. ST_XMIN Geospatial Attribute Functions Returns the minimum x value of the specified geography. ST_Y Geospatial Attribute Functions Returns the y value of the specified POINT. ST_YMAX Geospatial Attribute Functions Returns the maximum y value of specified geography. ST_YMIN Geospatial Attribute Functions Returns the minimum y value of specified geography. ST_ASBINARY Geospatial Conversion Functions Returns the well-known binary (WKB) representation of the specified geography. Alias of ST_ASWKB. ST_ASEWKT Geospatial Conversion Functions Returns a string that represents geographic coordinates of a specified POINT in the specified format. ST_ASGEOJSON Geospatial Conversion Functions Alias of ST_ASBINARY. ST_ASLATLONTEXT Geospatial Conversion Functions Returns the GeoJSON representation of the specified geography using the IETF standards. ST_ASTEXT Geospatial Conversion Functions Alias of ST_ASTEXT. ST_ASWKB Geospatial Conversion Functions Alias of ST_ASTEXT. ST_ASWKT Geospatial Conversion Functions Alias of ST_ASWKT and ST_EWKT. Returns the WKT representation of the specified geography. ST_GEOHASH Geospatial Conversion Functions Returns a string that represents the geohash of the input POINT. ST_ADDPOINT Geospatial Linestring Function Adds a POINT to the specified LINESTRING at the specified 0-indexed location. ST_ENDPOINT Geospatial Linestring Function Returns the endpoint of a specified LINESTRING. The returned value is a POINT. ST_LINEFROMEWKT Geospatial Linestring Constructor Creates a LINESTRING from the specified CHAR. ST_LINEFROMGEOJSON Geospatial Linestring Constructor Creates a LINESTRING represented by the specified GeoJSON. ST_LINEFROMTEXT Geospatial Linestring Constructor Creates a LINESTRING from a specified CHAR. The CHAR must be a LINESTRING value in WKT format. ST_LINEFROMWKB Geospatial Linestring Constructor Creates a LINESTRING from the specified BINARY. The BINARY value must be a LINESTRING in WKB format. ST_LINEINTERPOLATEPOINT Geospatial Linestring Function Returns a POINT along a LINESTRING based on a specified fraction of its total length. ST_LINELOCATEPOINT Geospatial Linestring Function Similar to ST_LINEINTERPOLATEPOINT, this function computes a fraction based on where a specified POINT is located along the length of a specified LINESTRING. ST_LINESTRING Geospatial Linestring Constructor Creates a LINESTRING based on the specified inputs. ST_LINESUBSTRING Geospatial Linestring Function Returns a LINESTRING that is a substring of a specified line that starts and ends at the specified fractions of its total length. ST_MAKELINE Geospatial Linestring Constructor Alias for ST_LINESTRING. ST_POINTN Geospatial Linestring Function Returns the POINT value at a specified index of the specified LINESTRING. ST_REMOVEPOINT Geospatial Linestring Function Removes a POINT value at a specified index from the specified line. ST_SETPOINT Geospatial Linestring Function Replaces a POINT value in a specified LINESTRING at a specified index. The function returns the altered LINESTRING with the replaced point. ST_STARTPOINT Geospatial Linestring Function Returns the starting POINT value of the line. ST_CENTROID Geospatial Point Constructors The geographic center of mass is calculated by taking the average of all points on a three-dimensional sphere, projecting the resultant point onto the sphere, and converting it back to latitude and longitude coordinates. ST_GEOGPOINT Geospatial Point Constructors Creates and returns a POLYGON geography with a single point, defined by the longitude and latitude specified for the function. ST_MAKEPOINT Geospatial Point Constructors Alias for ST_POINT. ST_POINT Geospatial Point Constructors Creates a POINT from the specified input arguments. ST_POINTFROMEWKT Geospatial Point Constructors Alias for ST_POINT. Creates a POINT using an EWKT-formatted CHAR as an input argument. ST_POINTFROMGEOHASH Geospatial Point Constructors Creates a POINT represented by the specified geohash. ST_POINTFROMGEOJSON Geospatial Point Constructors Creates a POINT represented by the specified GeoJSON value as an input argument. ST_POINTFROMTEXT Geospatial Point Constructors Alias for ST_POINT(char). ST_POINTFROMWKB Geospatial Point Constructors Alias for ST_POINT(binary). ST_FORCECCW Geospatial Polygon Constructors Creates a standardized polygon from an existing one. Ocient® defines standardized as the exterior being counterclockwise (CCW) and all holes being clockwise (CW) oriented. ST_MAKEPOLYGON Geospatial Polygon Constructors Alias for ST_POLYGON. ST_POLYGON Geospatial Polygon Constructors Creates a POLYGON. ST_POLYGONFROMEWKT Geospatial Polygon Constructors Creates a POLYGON using an EWKT-formatted CHAR as an input argument. Alias for the ST_POLYGON constructor. ST_POLYGONFROMGEOJSON Geospatial Polygon Constructors Creates a POLYGON from the specified POINT, POINT array, LINESTRING, or POLYGON geography. ST_POLYGONFROMTEXT Geospatial Polygon Constructors Alias for ST_POLYGON(char). ST_POLYGONFROMWKB Geospatial Polygon Constructors Alias for ST_POLYGON(binary). ST_WHOLEEARTH Geospatial Polygon Constructors Returns the database internal representation of the whole earth polygon. ST_ANGLE Geospatial Spatial Measurement Calculates the angle between two lines. ST_AREA Geospatial Spatial Measurement Returns the area of the specified geospatial object in the specified unit of measurement. ST_AZIMUTH Geospatial Spatial Measurement Returns the azimuth of the line from point1 to point2 in radians. ST_DISTANCE Geospatial Spatial Measurement Returns the minimum distance between the specified arguments. ST_DISTANCESPHERE Geospatial Spatial Measurement Returns the minimum distance between the specified arguments using a spherical computation. ST_DISTANCESPHEROID Geospatial Spatial Measurement Returns the minimum distance between the specified arguments using a spheroid computation. ST_EUCLIDEANDISTANCE3D Geospatial Spatial Measurement Returns the minimum distance between two geospatial points with altitude values. ST_HAUSDORFFDISTANCE Geospatial Spatial Measurement Returns the Hausdorff distance between two geographies in a specified measurement. ST_LENGTH Geospatial Spatial Measurement Returns the length of the specified line in the specified measurement unit. ST_LENGTH2D Geospatial Spatial Measurement Alias for ST_LENGTH. ST_MAXDISTANCE Geospatial Spatial Measurement Returns maximum distance between the specified arguments. ST_MINIMUMDISTANCETOSURFACE Geospatial Spatial Measurement Calculates the shortest distance between any point along a Euclidean line segment in three-dimensional space and the surface of the Earth. ST_PERIMETER Geospatial Spatial Measurement Returns the length of the exterior (outer ring) of the POLYGON in the specified unit of measurement. ST_PERIMETER2D Geospatial Spatial Measurement Alias for ST_PERIMETER. ST_BOUNDINGDIAGONAL Geospatial Spatial Operators Returns the diagonal LINESTRING from the minimum point to the maximum point of the bounding box that ST_ENVELOPE returns. ST_BUFFER Geospatial Spatial Operators Returns a geography that contains all points where the distance from the geography is less than or equal to the specified distance. ST_CLOSESTPOINT Geospatial Spatial Operators Returns the two-dimensional POINT of one specified geospatial object that is closest to a second specified geospatial object. ST_CONVEXHULL Geospatial Spatial Operators The convex hull is the smallest convex geometry that encloses the input geometry. ST_DIFFERENCEARRAY Geospatial Spatial Operators Returns an array containing any geospatial objects that are present in the first specified geospatial argument that are not found in the second geospatial argument. ST_ENVELOPE Geospatial Spatial Operators Returns a POLYGON that represents the minimum bounding box for the specified geography. ST_EXPAND Geospatial Spatial Operators Returns the bounding box of a specified geospatial value, which is expanded by a specified length. ST_EXTERIORRING Geospatial Spatial Operators Returns a LINESTRING that represents the exterior ring of a provided POLYGON value. ST_FLIPCOORDINATES Geospatial Spatial Operators Returns a new geographic object with the X and Y coordinates switched using the specified argument. ST_FORCE2D Geospatial Spatial Operators Convert a geographic object into a two-dimensional geography. ST_INTERIORRINGN Geospatial Spatial Operators Returns a LINESTRING representing the interior ring of the specified POLYGON, which is specified by its index. (1-indexed) ST_INTERSECTALL Geospatial Spatial Operators Returns the intersection of all geographies in the specified array. All geographies in the array must be the same type. ST_INTERSECTIONARRAY Geospatial Spatial Operators Returns a geography that represents the point-set intersection of two geographies. ST_LONGESTLINE Geospatial Spatial Operators Returns the longest LINESTRING between two specified geospatial arguments. ST_MAKEENVELOPE Geospatial Spatial Operators Returns a POLYGON with vertices that represent the minimum bounding box for the specified coordinates. ST_MINIMUMBOUNDINGCIRCLE Geospatial Spatial Operators Returns the smallest circle POLYGON that contains the specified geographic object. ST_MULTIDIFFERENCEARRAY Geospatial Spatial Operators Returns an array of geographies that represents the parts of the union of the geographies in the first array that do not intersect with the union of geographies in the second array. ST_MULTIINTERSECTIONARRAY Geospatial Spatial Operators When you specify two arrays of geospatial objects, this function returns an array of any intersections. ST_MULTISYMDIFFERENCEARRAY Geospatial Spatial Operators When you specify two arrays of geospatial objects, this function returns an array of any geospatial values that do not intersect. ST_MULTIUNIONARRAY Geospatial Spatial Operators When you specify two arrays of geospatial objects, this function returns one array that represents a union of all geographies in both arrays. ST_NRINGS Geospatial Spatial Operators Returns the number of rings of the specified POLYGON, including both interior and exterior rings. ST_NUMINTERIORRING Geospatial Spatial Operators Returns the number of interior rings of the specified POLYGON. ST_NUMINTERIORRINGS Geospatial Spatial Operators Returns the number of interior rings of the specified POLYGON. ST_POINTONSURFACE Geospatial Spatial Operators Returns a POINT guaranteed to intersect the specified geospatial object. ST_PROJECT Geospatial Spatial Operators Returns a POINT by projecting a distance and an azimuth value from the specified starting POINT value. ST_REDUCEPRECISION Geospatial Spatial Operators Returns a new geospatial object with all POINT values rounded to the specified decimal precision. ST_REMOVEREPEATEDPOINTS Geospatial Spatial Operators Returns a new geospatial object with no repeated POINT values. ST_REVERSE Geospatial Spatial Operators Returns a new geospatial object with the vertexes reversed. ST_SEGMENTIZE Geospatial Spatial Operators Returns a geospatial object that the function modifies to have no segment longer than the specified max_segment_length in meters. ST_SHORTESTLINE Geospatial Spatial Operators Returns the shortest LINESTRING between two specified geospatial arguments. ST_SIMPLIFY Geospatial Spatial Operators Returns a simplified version of the specified geography, which is either a POINT or LINESTRING. ST_SIMPLIFYARRAY Geospatial Spatial Operators Returns a POLYGON array that represents a simplified version of the specified geography, which is either a POINT, LINESTRING, or POLYGON. ST_SNAPTOGRID Geospatial Spatial Operators Returns a new geography value with all POINT values rounded to the specified precisions. ST_SYMDIFFERENCEARRAY Geospatial Spatial Operators Returns a geographic array that contains the parts that are not common between two geographic objects, geo1 and geo2. ST_UNIONARRAY Geospatial Spatial Operators Performs a union of the input geography values to produce a geographic array. ST_CLUSTERDBSCAN Geospatial Spatial Relationships Returns the cluster number for each input geography, based on a two-dimensional implementation of the density-based spatial clustering of applications with noise (DBSCAN) algorithm. ST_CONTAINS Geospatial Spatial Relationships Returns TRUE if the first geographic argument, geo1, contains the second geographic argument, geo2. ST_CONTAINSPROPERLY Geospatial Spatial Relationships Returns true if geo2 lies entirely in the interior of geo1 and does not intersect or touch the boundary or exterior points. ST_COVEREDBY Geospatial Spatial Relationships Returns TRUE if no POINT in geo1 is outside of geo2. ST_COVERS Geospatial Spatial Relationships Returns TRUE if no POINT in geo2 is outside of geo1. ST_CROSSES Geospatial Spatial Relationships Returns TRUE if two geospatial objects meet these criteria: The intersection of the geospatial interiors is not empty. The intersection is not equal to geo1 or geo2. Neither geospatial object is a single POINT. ST_DISJOINT Geospatial Spatial Relationships Returns true if the specified geographies have no intersection, including boundaries. Both geographic arguments can be different types. ST_DWITHIN Geospatial Spatial Relationships Returns TRUE if the geographies are within a specified distance in meters. ST_EQUALS Geospatial Spatial Relationships Returns TRUE if both geographies are spatially equal. ST_INTERSECTS Geospatial Spatial Relationships Returns TRUE if the specified geographies have intersection, including boundaries. ST_ISCCW Geospatial Spatial Relationships Alias for ST_ISPOLYGONCCW. ST_ISCLOSED Geospatial Spatial Relationships Returns TRUE if an input POLYGON has an exterior that is counter-clockwise. ST_ISPOLYGONCCW Geospatial Spatial Relationships Returns TRUE if an input LINESTRING has starting and ending points that are equal. ST_ISPOLYGONCW Geospatial Spatial Relationships Returns TRUE if an input POLYGON has an exterior that is clockwise. ST_ISRING Geospatial Spatial Relationships Returns TRUE if the specified LINESTRING is closed and does not intersect itself. ST_ISSIMPLE Geospatial Spatial Relationships For LINESTRING values, this function returns FALSE if any line segments intersect anywhere besides the endpoints. For POLYGON values, the function returns FALSE if either the exterior ring or any interior hole is not simple. ST_ISVALID Geospatial Spatial Relationships Returns TRUE if the specified geospatial value is a well-formed and valid geography according to the OGC standards. ST_OVERLAPS Geospatial Spatial Relationships Returns TRUE if both geographic arguments are of the same dimension and they intersect each other, but neither contains the other. ST_POINTINSIDECIRCLE Geospatial Spatial Relationships Returns TRUE if the geographic object is inside a circle that is centered at the specified point coordinates and with the specified radius. ST_RELATE Geospatial Spatial Relationships Returns the DE-9IM ST_TOUCHES Geospatial Spatial Relationships Returns TRUE if the only POINT values in common between the two geographic arguments lie in the union of their boundaries. ST_WITHIN Geospatial Spatial Relationships Alias for ST_CONTAINS. ST_DISTANCE Geospatial Spatiotemporal Measurement Returns the two-dimensional interpolated minimum simultaneous distance between two LINESTRING-TIMESTAMP array pairs in the specified unit of measurement. ST_MAXDISTANCE Geospatial Spatiotemporal Measurement Returns the two-dimensional interpolated maximum cotemporal distance between two LINESTRING-TIMESTAMP array pairs in the specified unit of measurement. ST_TOTALSECONDSININTERSECTION Geospatial Spatiotemporal Measurement Returns the total number of seconds spent in the intersection result calculated by the spatiotemporal version of ST_INTERSECTION. ST_INTERSECTION Geospatial Spatiotemporal Operators Returns a tuple that represents the intersection of a spatiotemporal LINESTRING with a static geography. ST_LINEGETALLTIMESATPOINT Geospatial Spatiotemporal Operators Returns a timestamp array of all times when the specified LINESTRING value intersects the specified POINT value. ST_LINEGETPOINTATTIME Geospatial Spatiotemporal Operators Returns a POINT within the bounds of the specified LINESTRING that corresponds to the interpolated point at the specified TIMESTAMP value. ST_LINEGETTIMEATPOINT Geospatial Spatiotemporal Operators Returns the interpolated time of the specified POINT on the specified LINESTRING that is paired with a TIMESTAMP ARRAY. ST_LONGESTLINE Geospatial Spatiotemporal Operators With the specified two LINESTRING-TIMESTAMP ARRAY pairs, this function returns a two-point LINESTRING that represents the maximum distance between points at a concurrent time. ST_SHORTESTLINE Geospatial Spatiotemporal Operators When you specify two LINESTRING-TIMESTAMP ARRAY pairs, this function returns a LINESTRING with two points that represents the minimum distance between points at a concurrent time. HLL_SKETCH_CREATE HyperLogLog Functions Creates an HLL sketch from the data on a specified aggregated column. Returns a HASH((2^log2k) + 8) data representation of the sketch that you can store in a separate column. HLL_SKETCH_GET_ESTIMATE HyperLogLog Functions The scalar function converts a sketch into a distinct count estimate of a sketch value. Returns the distinct count estimate as a BIGINT. HLL_SKETCH_GET_ESTIMATE_BOUND HyperLogLog Functions Takes a HLL_SKETCH column or an integral log2k literal value and returns the resulting bounding 95-percent confidence interval error proportion as a DOUBLE. HLL_SKETCH_TO_STRING HyperLogLog Functions The HLL_SKETCH_TO_STRING scalar function takes a HLL_SKETCH column or value and returns a string summary of the sketch. HLL_SKETCH_UNION (aggregate function) HyperLogLog Functions Merges multiple sketches in a single column into a unified sketch. All sketches must have the same precision. This function is an aggregate function and operates on a column. HLL_SKETCH_UNION (scalar function) HyperLogLog Functions Merges two sketches into a new combined sketch. This function is a scalar function and operates row-wise. The scalar function merges two sketch columns with heterogeneous precisions into a sketch with the lower of the two precisions. ABS Math Functions Returns the absolute value of a specified floating-point number. ACOS Math Functions Returns the inverse cosine of a specified floating-point number. ACOSH Math Functions Returns the hyperbolic arc-cosine of a specified floating-point number. ASIN Math Functions Returns the inverse sine of a specified floating-point number. ASINH Math Functions Returns the hyperbolic arc-sine of a specified floating-point number. ATAN Math Functions Returns the inverse tangent of a specified floating-point number. ATAN2 Math Functions Returns the inverse tangent of two numeric, floating-point values. ATANH Math Functions Returns the hyperbolic arc-tangent of a specified floating-point number. BICDF Math Functions The cumulative distribution function of the standard bivariate normal distribution. BIPDF Math Functions The probability density function of the standard bivariate normal distribution. BITAND Math Functions Alias for the BITFUNC syntax BITFUNC('AND', x, y). BITFUNC Math Functions Performs a variety of bit operations. Can be any of these string literals: 'AND', 'OR', or 'XOR'. BITNOT Math Functions Returns the bitwise negation of integral_x. BITOR Math Functions Alias for the BITFUNC syntax BITFUNC(‘OR’, x, y). BITXOR Math Functions Alias for the BITFUNC syntax BITFUNC(‘XOR’, x, y). BOOLAND Math Functions Alias for the BOOLFUNC syntax BOOLFUNC(‘AND’, x, y). BOOLFUNC Math Functions Performs a Boolean logical evaluation on arguments x and y. Can be any of these string literals: 'AND', 'OR', or 'XOR'. BOOLNOT Math Functions Returns the logical negation of the BOOLFUNC function. BOOLOR Math Functions Alias for the BOOLFUNC syntax BOOLFUNC(‘OR’, x, y). BOOLXOR Math Functions Alias for the BOOLFUNC syntax BOOLFUNC(‘XOR’, x, y). CBRT Math Functions Returns the cube root of the numeric value x. CDF Math Functions The cumulative distribution function of the standard normal distribution. Returns the probability that a random sample is less than or equal to the specified value. CEIL Math Functions Returns the nearest integer greater than or equal to x. CEILING Math Functions Alias for CEIL. COS Math Functions Returns the cosine of x. COSH Math Functions Returns the hyperbolic cosine of x. COT Math Functions Returns the cotangent of x. DEGREES Math Functions Returns the corresponding angle in degrees for x in radians. DIV Math Functions Returns the result of x divided by y. If y is zero, returns NULL. ERF Math Functions The error function is used for measurements that follow a normal distribution. ERFC Math Functions The complement of the error function. ERFC(x) = 1 - ERF(x). EXP Math Functions Returns the exponential of x (e raised to the power of x). FLOOR Math Functions Returns the nearest integer less than or equal to x. GAMMA Math Functions Gamma function. HEXBINX Math Functions Returns the x-coordinate of the center of the nearest hexagonal bin to the point (x, y). HEXBINY Math Functions Returns the y-coordinate of the center of the nearest hexagonal bin to the point (x, y). IERF Math Functions The inverse of the ERF error function. IERFC Math Functions The inverse of the complement of the error function. LEAKYRELU Math Functions Returns the leaky rectified linear unit function of x. LEFT_SHIFT Math Functions Returns x shifted to the left by y bits. LN Math Functions Returns the natural logarithm of x. LOG Math Functions Returns the base 10 logarithm of x. The optional base argument specifies the numeral system to use. If unspecified, the function defaults to base 10. LOG_GAMMA Math Functions The natural logarithm of the absolute value of the gamma function. LOG2 Math Functions Returns the base 2 logarithm of x. MOD Math Functions Returns the remainder from x divided by y. PI Math Functions Returns the constant value of π. PMOD Math Functions Returns the smallest non-negative equivalence class of x % y. POWER Math Functions Returns x raised to the power of y. PROBIT Math Functions The inverse of the cumulative distribution function. RADIANS Math Functions Returns the corresponding angle in radians for x in degrees. RAND Math Functions Takes no argument and returns a random DOUBLE value in the range [0, 1). RELU Math Functions Returns the rectified linear unit function of x. RIGHT_SHIFT Math Functions Returns x shifted to the right by y bits. ROUND Math Functions Returns x rounded to the nearest integer. SIGN Math Functions Returns the positive (+1), zero (0), or negative (-1) sign of x. SIN Math Functions Returns the sine of x. SINH Math Functions Returns the hyperbolic sine of x. SQRT Math Functions Returns the square root of x. SQUARE Math Functions Returns the square of x. TAN Math Functions Returns the tangent of x. TANH Math Functions Returns the hyperbolic tangent of x. TO_BASE Math Functions Converts an integer value to its string representation in a specified base (radix). TRUNC Math Functions Returns x truncated to y decimal places. TRUNCATE Math Functions Alias for TRUNC. ABS Matrix Functions Returns magnitude of one-dimensional matrix or vector. CROSS_ENTROPY_LOSS Matrix Functions Returns the cross entropy loss of two one-dimensional matrixes or vectors. DET Matrix Functions Returns the determinant of the matrix as a double. DOT Matrix Functions Returns dot product of two one-dimensional matrixes/vectors. EIGEN Matrix Functions Returns eigenvalues and eigenvalues of a square matrix as a vector of pairs. FROBENIUS Matrix Functions Returns the Frobenius norm of a matrix. HINGE_LOSS Matrix Functions Returns the hinge loss of two one-dimensional matrixes or vectors. IDENTITY_MATRIX Matrix Functions Returns an identity matrix of the specified dimension. INVERSE Matrix Functions Returns inverse of a square, invertible matrix. LOG_LOSS Matrix Functions Returns the log loss of two one-dimensional matrixes or vectors. LOGITS_LOSS Matrix Functions Returns the logits loss of two one-dimensional matrixes or vectors. LUPQ_DECOMP Matrix Functions Returns LUPQ decomposition of a square matrix A as a tuple of 4 matrixes where PAQ = LU. MATRIX_DIM Matrix Functions Returns the dimensions of the specified matrix as a tuple of (row, col) integers. MAKE_MATRIX_IXJ Matrix Functions Creates an ixj matrix with elements e_00, …, e_ij. MATRIX_FROM_TEXT Matrix Functions Creates a matrix from the specified string. MATRIX_TRACE Matrix Functions Returns trace of a square matrix as a double. NULL_MATRIX Matrix Functions Returns a NULL matrix of the specified (row, col) dimensions. QR_DECOMP Matrix Functions Returns QR decomposition of a matrix as a tuple of 2 matrixes. SOFTMAX Matrix Functions Returns the softmax of a one-dimensional matrix or vector. SVD_DECOMP Matrix Functions Returns SVD decomposition of a matrix as a tuple of 3 matrixes. TRANSPOSE Matrix Functions Returns transpose of the matrix. VECTOR_ARGMAX Matrix Functions Returns the argmax of a one-dimensional matrix or vector. VECTOR_ARGMIN Matrix Functions Returns the argmin of a one-dimensional matrix or vector. VECTOR_MAX Matrix Functions Returns the maximum of elements in a one-dimensional matrix/vector. VECTOR_MIN Matrix Functions Returns the minimum of elements in a one-dimensional matrix/vector. VECTOR_SUM Matrix Functions Returns the sum of elements in a one-dimensional matrix/vector. ZERO_MATRIX Matrix Functions Returns a zero matrix of the given (row, col) dimensions. IP Network Type Functions Casts an IP data type from an IPV4 data type expression. IPV4 Network Type Functions Casts an IPV4 address from an IPV6 address. IS_IPV4 Network Type Functions Tests whether the database can convert the IP value to the IPV4 data type. SUBNET Network Type Functions Computes the prefix from an IP or IPV4 value and the size of the prefix. CANCEL Query Management Cancels a running query based on its specific query identifier. KILL Query Management Kills a running query identified by its UUID. BIGINT Scalar Data Conversion Functions Casts the argument to a value of type bigint. BINARY Scalar Data Conversion Functions Parses a hexadecimal string (such as 0x54ab) to create a binary value. Letters can be of either case. BOOLEAN Scalar Data Conversion Functions Parses a string to create a Boolean value. The string must contain either true or false. It is case-insensitive. BYTE Scalar Data Conversion Functions Casts the argument to a value of type byte. CHAR Scalar Data Conversion Functions Creates a string version of the numeric value. DATE Scalar Data Conversion Functions Parses a string in the form ‘YYYY-MM-DD’ to create a date. Extra characters are ignored. DAYS Scalar Data Conversion Functions Converts an integral value to an interval value of type days to be used in date calculations. DECIMAL Scalar Data Conversion Functions Casts the argument to a value of type decimal. DOUBLE Scalar Data Conversion Functions Casts the argument to a value of type double. FLOAT Scalar Data Conversion Functions Casts the argument to a value of type float. HASH Scalar Data Conversion Functions Creates a fixed length binary value, with length <length>, from a string, i.e. 0x1234abcd. Zero extended if the string does not have enough bytes, truncated if it has too many. HOURS Scalar Data Conversion Functions Converts an integral value to an interval value of type hours. INTEGER Scalar Data Conversion Functions Casts the argument to a value of type integer. MICROSECONDS Scalar Data Conversion Functions Converts an integral value to an interval value of type microseconds. MILLISECONDS Scalar Data Conversion Functions Converts an integral value to an interval value of type milliseconds. MINUTES Scalar Data Conversion Functions Converts an integral value to an interval value of type minutes. MONTHS Scalar Data Conversion Functions Converts an integral value to an interval value of type months to be used in date calculations. NANOSECONDS Scalar Data Conversion Functions Converts an integral value to an interval value of type nanoseconds. SECONDS Scalar Data Conversion Functions Converts an integral value to an interval value of type seconds. SMALLINT Scalar Data Conversion Functions Casts the argument to a value of type smallint. TIME Scalar Data Conversion Functions Parses the string to create a time value. The string must be in the form 'HH:MM[.SSSSSSSSS]'. TIMESTAMP Scalar Data Conversion Functions Parses a string and makes a timestamp value. The string must be in the format 'YYYY-MM-DD[ HH:MM][.SSSSSSSSS]'. UUID Scalar Data Conversion Functions Parses the string and makes a UUID value. The string must be a valid UUID. WEEKS Scalar Data Conversion Functions Converts an integral value to an interval value of type weeks to be used in date calculations. YEARS Scalar Data Conversion Functions Converts an integral value to an interval value of type years. ARRAY_AGG Sorted Aggregate Functions Returns an array containing every row from the expression. ARRAY_CONCAT_AGG Sorted Aggregate Functions Returns an array that concatenates arrays across rows. STRING_AGG Sorted Aggregate Functions Returns a string concatenated from every row from the expression. The delimiter argument is optional. ARRAY_CAP Special Data Pipeline Transformation Functions Restrict the length of an array to a maximum number of elements. ARRAY_COMPACT Special Data Pipeline Transformation Functions Removes NULL values from the array. ARRAY_CONTAINS Special Data Pipeline Transformation Functions Returns true if the array contains the specified value. ARRAY_SORT Special Data Pipeline Transformation Functions Sort and return the input array based on the natural ordering of its elements or the specified Lambda function. CASE WHEN Special Data Pipeline Transformation Functions Returns the result value based on whether an expression is true. ELEMENT_AT Special Data Pipeline Transformation Functions Returns the element value of the array or tuple at the specified index. EXPLODE_OUTER Special Data Pipeline Transformation Functions Expands a one-dimensional or multidimensional array into its elements with one element per row of output from the system. FILTER Special Data Pipeline Transformation Functions Filters elements in an array based on the logic in a lambda expression. FLATTEN Special Data Pipeline Transformation Functions Transforms an N-dimensional array into an N-1-dimensional array. IF Special Data Pipeline Transformation Functions Returns T if the expression X evaluates to true, or the function returns F if X evaluates to false. LOOKUP Special Data Pipeline Transformation Functions Look up and load data in an external data source. MAP_KEYS Special Data Pipeline Transformation Functions Returns the keys in the specified JSON string. MAP_VALUES Special Data Pipeline Transformation Functions Returns the values in the specified JSON string. METADATA Special Data Pipeline Transformation Functions Extracts the metadata value for the specified key from available metadata for the pipeline. PARSE_DELIMITED_ARRAY Special Data Pipeline Transformation Functions Converts a string of text data representing an array into a CHAR[]. REDUCE Special Data Pipeline Transformation Functions Applies a merge function to a starting value and all elements in the array, and then reduces the array to a single value. Optionally, specify a finish function for the returned single value. TO_ARRAY_LENGTH Special Data Pipeline Transformation Functions Create the specified number of copies of any JSON object in an array. TRANSFORM Special Data Pipeline Transformation Functions Transforms an array based on the logic in a lambda expression. WIDTH_BUCKET Special Data Pipeline Transformation Functions Creates N equal-width buckets in the range [min,max) as a histogram. ZIP_WITH Special Data Pipeline Transformation Functions Merges two or more input arrays, element-wise, into a single array using the trailing combining function. COMMIT Other Functions and Expressions Returns the most recent commit hash of the database to which the client is currently connected. RAND_UUID Other Functions and Expressions Generates a random UUID value (version 4). UUID_GENERATE Other Functions and Expressions Generates a random UUID value (version 4). CURRENT_DATABASE System Functions Alias for DATABASE. CURRENT_GROUPS System Functions Returns the fully-qualified names of groups in the database. CURRENT_NODE System Functions Returns the name of the SQL Node where the current query executes. The name of the node corresponds to the name column in the sys.nodes system catalog table. CURRENT_NODE_ID System Functions Returns the identifier of the SQL Node where the current query executes. CURRENT_SCHEMA System Functions Returns the name of the current schema. CURRENT_SESSION_ID System Functions Returns the Universally Unique IDentifier (UUID) of the current session. CURRENT_SYSTEM System Functions Returns the name of the system. CURRENT_USER System Functions Returns the user for the current connection. DATABASE System Functions Returns the name of the database to which the client is currently connected. SHOW System Functions The SHOW function enables you to explore the database and its metadata for user-defined items. VERSION System Functions Returns the version of the database to which the client is currently connected. CONVERT_LOCAL_TIMESTAMP_TO_UTC Time Zone Functions The function converts a timestamp in a specified local time zone to the UTC time zone. CONVERT_UTC_TIMESTAMP_TO_LOCAL Time Zone Functions The function converts a timestamp from the UTC time zone to a specified local time zone. [CAST_TO_TUPLE ](Tuple Functions and Operators) Tuple Functions Converts a tuple into another tuple of a different type. [CHAR ](Tuple Functions and Operators) Tuple Functions Converts a tuple to its string representation. [STRING_TO_TUPLE ](Tuple Functions and Operators) Tuple Functions Converts the string representation of a tuple (e.g ‘tuple<<INT,BIGINT,CHAR>>(1,2,NULL)’) into a tuple. [TUPLE() ](Tuple Functions and Operators) Tuple Functions Construct a tuple of specified elements. Types of the tuple are inferred from the inner elements. [TUPLE<<>> ](Tuple Functions and Operators) Tuple Functions Construct a tuple of specified elements. NULL is also supported as an element. CUME_DIST Window Aggregate Functions Returns a number 0 < n 1 and can be used to calculate the percentage of values less than or equal to the current value in the group. DELTA Window Aggregate Functions Computes the finite difference between successive values of expression under the specified ordering. This is a backwards difference, which means that, at degree one, the value for a given row is the difference between the value of expression for that row and the previous row. DENSE_RANK Window Aggregate Functions Assigns a number to each row in the result set with equal values having the same number. There will be no gaps between ranks. DERIVATIVE Window Aggregate Functions Computes the difference quotient between successive values of expression with respect to expression2. FIRST_VALUE Window Aggregate Functions Returns the first value in the ordered result set. LAG Window Aggregate Functions Returns the row, which is the specified number backward from the current row. Default is 1 if offset is omitted. LAST_VALUE Window Aggregate Functions Returns the last value in the ordered result set. LEAD Window Aggregate Functions Returns the row, which is the specified number forward from the current row. Default is 1 if offset is omitted. NTH_VALUE Window Aggregate Functions Returns the nth value in the ordered result set. PERCENT_RANK Window Aggregate Functions The value returned is 0 < n ≤ 1 and can be used to calculate the percentage of values less than the current group, excluding the highest value. PERCENTILE Window Aggregate Functions Returns the value that corresponds to the specified percentile (0 ≤ n ≤ 1) within the group. RANK Window Aggregate Functions Assigns a number to each row in the result set with equal values having the same number. There can be gaps between ranks. RATIO_TO_REPORT Window Aggregate Functions Computes the ratio of a value to the sum of the set of values. ROW_NUMBER Window Aggregate Functions Assigns a unique number to each row in the result set. ZSCORE Window Aggregate Functions Zscore of the sample based on the stddev() function. ZSCOREP Window Aggregate Functions Zscore of the sample based on the stddevp() function. 
