Functions

Returns an arbitrary value from the non-null input values.

Collects all the input values, including nulls, into an array.

Computes the average (arithmetic mean) of all the non-null input values.

Computes the bitwise AND of all non-null input values.

Computes the bitwise OR of all non-null input values.

Computes the bitwise exclusive OR of all non-null input values. Can be useful as a checksum for an unordered set of values.

Returns true if all non-null input values are true, otherwise false.

Returns true if any non-null input value is true, otherwise false.

Computes the correlation coefficient.

Computes the number of input rows.

Computes the population covariance.

Computes the sample covariance.

Computes the cumulative distribution, that is (number of rows preceding or peers with hypothetical row) / (total rows). The value thus ranges from 1/N to 1.

Computes the rank of the hypothetical row, without gaps; this function effectively counts peer groups.

This is the SQL standard's equivalent to bool_and.

Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is include…

Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.

Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.

Behaves in the same way as json_array but as an aggregate function so it only takes one value_expression parameter. If ABSENT ON NULL is specified, any NULL values are omitted. If ORDER BY is specified, the elements will appear in the array in that …

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot.

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped,

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot. If there is a duplicate key an error is thrown.

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped. If there is a duplicate key an…

Behaves like json_object, but as an aggregate function, so it only takes one key_expression and one value_expression parameter.

Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.

Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot.

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped,

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot. If there is a duplicate key an error is thrown.

Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped. If there is a duplicate key an…

Computes the maximum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as bytea, inet, interval, money, oid, pg_lsn, tid, xid8, and also arrays and composite types containing sortable data types.

Computes the minimum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as bytea, inet, interval, money, oid, pg_lsn, tid, xid8, and also arrays and composite types containing sortable data types.

Computes the mode, the most frequent value of the aggregated argument (arbitrarily choosing the first one if there are multiple equally-frequent values). The aggregated argument must be of a sortable type.

Computes the relative rank of the hypothetical row, that is (rank - 1) / (total rows - 1). The value thus ranges from 0 to 1 inclusive.

Computes the continuous percentile, a value corresponding to the specified fraction within the ordered set of aggregated argument values. This will interpolate between adjacent input items if needed.

Computes the discrete percentile, the first value within the ordered set of aggregated argument values whose position in the ordering equals or exceeds the specified fraction. The aggregated argument must be of a sortable type.

Computes the union of the non-null input values.

Computes the intersection of the non-null input values.

Computes the rank of the hypothetical row, with gaps; that is, the row number of the first row in its peer group.

Computes the average of the independent variable, sum(X)/N.

Computes the average of the dependent variable, sum(Y)/N.

Computes the number of rows in which both inputs are non-null.

Computes the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.

Computes the square of the correlation coefficient.

Computes the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.

Computes the “sum of squares” of the independent variable, sum(X^2) - sum(X)^2/N.

Computes the “sum of products” of independent times dependent variables, sum(X*Y) - sum(X) * sum(Y)/N.

Computes the “sum of squares” of the dependent variable, sum(Y^2) - sum(Y)^2/N.

This is a historical alias for stddev_samp.

Computes the population standard deviation of the input values.

Computes the sample standard deviation of the input values.

Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it's not null).

Computes the sum of the non-null input values.

Computes the population variance of the input values (square of the population standard deviation).

Computes the sample variance of the input values (square of the sample standard deviation).

This is a historical alias for var_samp.

Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).

Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator).

Returns a text representation of the array's dimensions.

Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all 1).

Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)

Returns the lower bound of the requested array dimension.

Returns the number of dimensions of the array.

Returns the subscript of the first occurrence of the second argument in the array, or NULL if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using IS NOT…

Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL. NU…

Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator).

Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.

Replaces each array element equal to the second argument with the third argument.

Reverses the first dimension of the array.

Returns an array of n items randomly selected from array. n may not exceed the length of array's first dimension. If array is multi-dimensional, an “item” is a slice having a given first subscript.

Randomly shuffles the first dimension of the array.

Sorts the first dimension of the array. The sort order is determined by the default sort ordering of the array's element type; however, if the element type is collatable, the collation to use can be specified by adding a COLLATE clause to the array …

Converts each array element to its text representation, and concatenates those separated by the delimiter string. If null_string is given and is not NULL, then NULL array entries are represented by that string; otherwise, they are omitted. See also …

Returns the upper bound of the requested array dimension.

Returns the total number of elements in the array, or 0 if the array is empty.

Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.

Returns the number of bits set in the binary string (also known as “popcount”).

Converts a binary string representing text in encoding src_encoding to a binary string in encoding dest_encoding (see Section 23.3.4 for available conversions).

Converts a binary string representing text in encoding src_encoding to text in the database encoding (see Section 23.3.4 for available conversions).

Converts a text string (in the database encoding) to a binary string encoded in encoding dest_encoding (see Section 23.3.4 for available conversions).

Computes the CRC-32 value of the binary string.

Computes the CRC-32C value of the binary string.

Decodes binary data from a textual representation; supported format values are the same as for encode.

Encodes binary data into a textual representation; supported format values are: base64, escape, hex.

Extracts n'th bit from binary string.

Extracts n'th byte from binary string.

Sets n'th bit in binary string to newvalue.

Sets n'th byte in binary string to newvalue.

Computes the SHA-224 hash of the binary string.

Computes the SHA-256 hash of the binary string.

Computes the SHA-384 hash of the binary string.

Computes the SHA-512 hash of the binary string.

Checks if the input is the null value, generating an error if so; otherwise, returns the input.

Returns the number of non-null arguments.

Returns the number of null arguments.

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details).

The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:

Converts time stamp to string according to the given format.

Converts string to date according to the given format.

Converts string to numeric according to the given format.

Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.33.)

Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days

Current date and time (changes during statement execution); see Section 9.9.5

Current date; see Section 9.9.5

Current time of day; see Section 9.9.5

Current date and time (start of current transaction); see Section 9.9.5

Add an interval to a timestamp with time zone, computing times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted. The form with two arguments is equival…

Bin input into specified interval aligned with specified origin; see Section 9.9.3

Get timestamp subfield (equivalent to extract); see Section 9.9.1

Subtract an interval from a timestamp with time zone, computing times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted. The form with two arguments is …

Truncate to specified precision; see Section 9.9.2

Get timestamp subfield; see Section 9.9.1

Test for finite date (not +/-infinity)

Adjust interval, converting 30-day time periods to months

Adjust interval, converting 24-hour time periods to days

Adjust interval using justify_days and justify_hours, with additional sign adjustments

Current time of day; see Section 9.9.5

Current date and time (start of current transaction); see Section 9.9.5

Create date from year, month and day fields (negative years signify BC)

Create interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zero

Create time from hour, minute and seconds fields

Create timestamp from year, month, day, hour, minute and seconds fields (negative years signify BC)

Create timestamp with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If timezone is not specified, the current time zone is used; the examples assume the session time zone is Europe/London

Current date and time (start of current transaction); see Section 9.9.5

pg_sleep makes the current session's process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_unt…

pg_sleep makes the current session's process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_unt…

pg_sleep makes the current session's process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_unt…

Current date and time (start of current statement); see Section 9.9.5

Current date and time (like clock_timestamp, but as a text string); see Section 9.9.5

Current date and time (start of current transaction); see Section 9.9.5

Returns the first value of the input enum type.

Returns the last value of the input enum type.

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

pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger. If called in any other context, an error is raised. pg_event_trigger_ddl_commands retu…

pg_event_trigger_dropped_objects returns a list of all objects dropped by the command in whose sql_drop event it is called. If called in any other context, an error is raised. This function returns the following columns:

Returns the OID of the table about to be rewritten.

Returns a code explaining the reason(s) for rewriting. The value is a bitmap built from the following values: 1 (the table has changed its persistence), 2 (default value of a column has changed), 4 (a column has a new data type) and 8 (the table acc…

Computes area. Available for box, path, circle. A path input must be closed, else NULL is returned. Also, if the path is self-intersecting, the result may be meaningless.

Computes bounding box of two boxes.

Computes box inscribed within the circle.

Computes center point. Available for box, circle.

Computes smallest circle enclosing box.

Extracts box's diagonal as a line segment (same as lseg(box)).

Computes diameter of circle.

Computes vertical size of box.

Is path closed?

Is path open?

Converts two points to the line through them.

Extracts box's diagonal as a line segment.

Returns the number of points. Available for path, polygon.

Converts polygon to a closed path with the same list of points.

Converts path to closed form.

Constructs point from its coordinates.

Converts box to a 4-point polygon.

Converts path to open form.

Computes radius of circle.

Computes slope of a line drawn through the two points.

Computes horizontal size of box.

Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optional boolean parameter is true.

Converts a given expression specified as text or bytea string (in UTF8 encoding) into a JSON value. If expression is NULL, an SQL null value is returned. If WITH UNIQUE is specified, the expression must not contain any duplicate object keys.

Constructs a JSON array from either a series of value_expression parameters or from the results of query_expression, which must be a SELECT query returning a single column. If ABSENT ON NULL is specified, NULL values are ignored. This is always the …

Expands the top-level JSON array into a set of JSON values.

Expands the top-level JSON array into a set of text values.

Returns the number of elements in the top-level JSON array.

Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. Each argument is converted as per to_json or to_jsonb.

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb.

Expands the top-level JSON object into a set of key/value pairs.

Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text.

Extracts JSON sub-object at the specified path. (This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.)

Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)

Constructs a JSON object of all the key/value pairs given, or an empty object if none are given. key_expression is a scalar expression defining the JSON key, which is converted to the text type. It cannot be NULL nor can it belong to a type that has…

Returns the set of keys in the top-level JSON object.

Expands the top-level JSON object to a row having the composite type of the base argument. The JSON object is scanned for fields whose names match column names of the output row type, and their values are inserted into those columns of the output. (…

Expands the top-level JSON array of objects to a set of rows having the composite type of the base argument. Each element of the JSON array is processed as described above for json[b]_populate_record.

Converts a given SQL scalar value into a JSON scalar value. If the input is NULL, an SQL null is returned. If the input is number or a boolean value, a corresponding JSON number or boolean value is returned. For any other value, a JSON string is ret…

Converts an SQL/JSON expression into a character or binary string. The expression can be of any JSON type, any character string type, or bytea in UTF8 encoding. The returned type used in RETURNING can be any character string type or bytea. The defau…

Deletes all object fields that have null values from the given JSON value, recursively. If strip_in_arrays is true (the default is false), null array elements are also stripped. Otherwise they are not stripped. Bare null values are never stripped.

Each syntax element is described below in more detail.

Expands the top-level JSON object to a row having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) The output record is fill…

Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) Each el…

Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null. (The null result should not be confused with an SQL NULL; see the examples.)

Expands the top-level JSON array into a set of JSON values.

Expands the top-level JSON array into a set of text values.

Returns the number of elements in the top-level JSON array.

Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. Each argument is converted as per to_json or to_jsonb.

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb.

Expands the top-level JSON object into a set of key/value pairs.

Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text.

Extracts JSON sub-object at the specified path. (This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.)

Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)

Returns target with new_value inserted. If the item designated by the path is an array element, new_value will be inserted before that item if insert_after is false (which is the default), or after it if insert_after is true. If the item designated …

Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elem…

Returns the set of keys in the top-level JSON object.

Checks whether the JSON path returns any item for the specified JSON value. (This is useful only with SQL-standard JSON path expressions, not predicate check expressions, since those always return a value.) If the vars argument is specified, it must…

These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…

Returns the SQL boolean result of a JSON path predicate check for the specified JSON value. (This is useful only with predicate check expressions, not SQL-standard JSON path expressions, since it will either fail or return NULL if the path result is…

These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…

Returns all JSON items returned by the JSON path for the specified JSON value. For SQL-standard JSON path expressions it returns the JSON values selected from target. For predicate check expressions it returns the result of the predicate check: true…

Returns all JSON items returned by the JSON path for the specified JSON value, as a JSON array. The parameters are the same as for jsonb_path_query.

These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…

Returns the first JSON item returned by the JSON path for the specified JSON value, or NULL if there are no results. The parameters are the same as for jsonb_path_query.

These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…

These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…

Expands the top-level JSON object to a row having the composite type of the base argument. The JSON object is scanned for fields whose names match column names of the output row type, and their values are inserted into those columns of the output. (…

Function for testing jsonb_populate_record. Returns true if the input jsonb_populate_record would finish without an error for the given input JSON object; that is, it's valid input, false otherwise.

Expands the top-level JSON array of objects to a set of rows having the composite type of the base argument. Each element of the JSON array is processed as described above for json[b]_populate_record.

Converts the given JSON value to pretty-printed, indented text.

Returns target with the item designated by path replaced by new_value, or with new_value added if create_if_missing is true (which is the default) and the item designated by path does not exist. All earlier steps in the path must exist, or the targe…

If new_value is not NULL, behaves identically to jsonb_set. Otherwise behaves according to the value of null_value_treatment which must be one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target'. The default is 'use_json_null'.

Deletes all object fields that have null values from the given JSON value, recursively. If strip_in_arrays is true (the default is false), null array elements are also stripped. Otherwise they are not stripped. Bare null values are never stripped.

Expands the top-level JSON object to a row having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) The output record is fill…

Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) Each el…

Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null. (The null result should not be confused with an SQL NULL; see the examples.)

Converts an SQL composite value to a JSON object. The behavior is the same as to_json except that line feeds will be added between top-level elements if the optional boolean parameter is true.

Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function…

Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function…

Absolute value

Inverse cosine, result in radians

Inverse cosine, result in degrees

Inverse hyperbolic cosine

Inverse sine, result in radians

Inverse sine, result in degrees

Inverse hyperbolic sine

Inverse tangent, result in radians

Inverse tangent of y/x, result in radians

Inverse tangent of y/x, result in degrees

Inverse tangent, result in degrees

Inverse hyperbolic tangent

Cube root

Nearest integer greater than or equal to argument

Nearest integer greater than or equal to argument (same as ceil)

Cosine, argument in radians

Cosine, argument in degrees

Hyperbolic cosine

Cotangent, argument in radians

Cotangent, argument in degrees

Converts radians to degrees

Integer quotient of y/x (truncates towards zero)

Error function

Complementary error function (1 - erf(x), without loss of precision for large inputs)

Exponential (e raised to the given power)

Factorial

Nearest integer less than or equal to argument

Gamma function

Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns 0 if both inputs are zero; available for integer, bigint, and numeric

Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns 0 if either input is zero; available for integer, bigint, and numeric

Natural logarithm of the absolute value of the gamma function

Natural logarithm

Base 10 logarithm

Base 10 logarithm (same as log)

Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely

Remainder of y/x; available for smallint, integer, bigint, and numeric

Approximate value of π

a raised to the power of b

Converts degrees to radians

Returns a random value in the range 0.0 <= x < 1.0

Returns a random value from the normal distribution with the given parameters; mean defaults to 0.0 and stddev defaults to 1.0

Rounds to nearest integer. For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule.

Scale of the argument (the number of decimal digits in the fractional part)

Sets the seed for subsequent random() and random_normal() calls; argument must be between -1.0 and 1.0, inclusive

Sign of the argument (-1, 0, or +1)

Sine, argument in radians

Sine, argument in degrees

Hyperbolic sine

Square root

Tangent, argument in radians

Tangent, argument in degrees

Hyperbolic tangent

Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes

Truncates to integer (towards zero)

Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. The buckets have inclusive lower bounds and exclusive upper bounds. Returns 0 for an input less than low, or coun…

Returns the merge action command executed for the current row. This will be 'INSERT', 'UPDATE', or 'DELETE'.

Creates an abbreviated display format as text. (The result is the same as the inet output function produces; it is “abbreviated” only in comparison to the result of an explicit cast to text, which for historical reasons will never suppress the netma…

Computes the broadcast address for the address's network.

Returns the address's family: 4 for IPv4, 6 for IPv6.

Returns the IP address as text, ignoring the netmask.

Computes the host mask for the address's network.

Computes the smallest network that includes both of the given networks.