Learn
PostgreSQL function reference
230 functions from the PostgreSQL manual, each with 282 verified examples between them. Every result on every page came out of PostgreSQL 18.6 rather than being copied from the documentation.
Where it is useful, a page also says what the same job is called in MySQL, SQL Server, Oracle and SQLite. That part is written by hand, because it is a claim about five products at once and a confident wrong answer is worse than no answer.
2 further functions are written and verified and go up next. Some chapters will not: where we have nothing to add to the documentation, a page here would not be worth landing on.
/
Mathematical Functions53
abs()Absolute valueacos()Inverse cosine, result in radiansacosd()Inverse cosine, result in degreesacosh()Inverse hyperbolic cosineasin()Inverse sine, result in radiansasind()Inverse sine, result in degreesasinh()Inverse hyperbolic sineatan()Inverse tangent, result in radiansatan2()Inverse tangent of y/x, result in radiansatan2d()Inverse tangent of y/x, result in degreesatand()Inverse tangent, result in degreesatanh()Inverse hyperbolic tangentcbrt()Cube rootceil()Nearest integer greater than or equal to argumentceiling()Nearest integer greater than or equal to argument (same as ceil)cos()Cosine, argument in radianscosd()Cosine, argument in degreescosh()Hyperbolic cosinecot()Cotangent, argument in radianscotd()Cotangent, argument in degreesdegrees()Converts radians to degreesdiv()Integer quotient of y/x (truncates towards zero)erf()Error functionerfc()Complementary error function (1 - erf(x), without loss of precision for large inputs)exp()Exponential (e raised to the given power)factorial()Factorialfloor()Nearest integer less than or equal to argumentgamma()Gamma functiongcd()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 numericlcm()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 numericlgamma()Natural logarithm of the absolute value of the gamma functionln()Natural logarithmlog()Base 10 logarithmlog10()Base 10 logarithm (same as log)min_scale()Minimum scale (number of fractional decimal digits) needed to represent the supplied value preciselymod()Remainder of y/x; available for smallint, integer, bigint, and numericpi()Approximate value of πpower()a raised to the power of bradians()Converts degrees to radiansrandom()Returns a random value in the range 0.0 <= x < 1.0round()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()Scale of the argument (the number of decimal digits in the fractional part)sign()Sign of the argument (-1, 0, or +1)sin()Sine, argument in radianssind()Sine, argument in degreessinh()Hyperbolic sinesqrt()Square roottan()Tangent, argument in radianstand()Tangent, argument in degreestanh()Hyperbolic tangenttrim_scale()Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroestrunc()Truncates to integer (towards zero)width_bucket()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 count+1 for an input greater than or equal to high. If low > high, the behavior is mirror-reversed, with bucket 1 now being the one just below low, and the inclusive bounds now being on the upper side.
String Functions50
ascii()Returns the numeric code of the first character of the argument. In UTF8 encoding, returns the Unicode code point of the character. In other multibyte encodings, the argument must be an ASCII character.bit_length()Returns number of bits in the string (8 times the octet_length).btrim()Removes the longest string containing only characters in characters (a space by default) from the start and end of string.char_length()Returns number of characters in the string.character_length()Returns number of characters in the string.chr()Returns the character with the given code. In UTF8 encoding the argument is treated as a Unicode code point. In other multibyte encodings the argument must designate an ASCII character. chr(0) is disallowed because text data types cannot store that character.concat()Concatenates the text representations of all the arguments. NULL arguments are ignored.concat_ws()Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored.format()Formats arguments according to a format string; see Section 9.4.1. This function is similar to the C function sprintf.initcap()Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.left()Returns first n characters in the string, or when n is negative, returns all but last |n| characters.length()Returns the number of characters in the string.lower()Converts the string to all lower case, according to the rules of the database's locale.lpad()Extends the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right).ltrim()Removes the longest string containing only characters in characters (a space by default) from the start of string.md5()Computes the MD5 hash of the argument, with the result written in hexadecimal.octet_length()Returns number of bytes in the string. octet_length('josé') → 5 (if server encoding is UTF8)overlay()Replaces the substring of string that starts at the start'th character and extends for count characters with newsubstring. If count is omitted, it defaults to the length of newsubstring.parse_ident()Splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is false, then such extra characters are ignored. (This behavior is useful for parsing names for objects like functions.) Note that this function does not truncate over-length identifiers. If you want truncation you can cast the result to name[].pg_client_encoding()Returns current client encoding name.position()Returns first starting index of the specified substring within string, or zero if it's not present.quote_ident()Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. See also Example 41.1.quote_literal()Returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 41.1.quote_nullable()Returns the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled. See also Example 41.1.regexp_count()Returns the number of times the POSIX regular expression pattern matches in the string; see Section 9.7.3.regexp_instr()Returns the position within string where the N'th match of the POSIX regular expression pattern occurs, or zero if there is no such match; see Section 9.7.3.regexp_like()Checks whether a match of the POSIX regular expression pattern occurs within string; see Section 9.7.3.regexp_match()Returns substrings within the first match of the POSIX regular expression pattern to the string; see Section 9.7.3.regexp_replace()Replaces the substring that is the first match to the POSIX regular expression pattern, or all such matches if the g flag is used; see Section 9.7.3.regexp_split_to_array()Splits string using a POSIX regular expression as the delimiter, producing an array of results; see Section 9.7.3.regexp_substr()Returns the substring within string that matches the N'th occurrence of the POSIX regular expression pattern, or NULL if there is no such match; see Section 9.7.3.repeat()Repeats string the specified number of times.replace()Replaces all occurrences in string of substring from with substring to.reverse()Reverses the order of the characters in the string.right()Returns last n characters in the string, or when n is negative, returns all but first |n| characters.rpad()Extends the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated.rtrim()Removes the longest string containing only characters in characters (a space by default) from the end of string.split_part()Splits string at occurrences of delimiter and returns the n'th field (counting from one), or when n is negative, returns the |n|'th-from-last field.starts_with()Returns true if string starts with prefix.string_to_array()Splits the string at occurrences of delimiter and forms the resulting fields into a text array. If delimiter is NULL, each character in the string will become a separate element in the array. If delimiter is an empty string, then the string is treated as a single field. If null_string is supplied and is not NULL, fields matching that string are replaced by NULL. See also array_to_string.strpos()Returns first starting index of the specified substring within string, or zero if it's not present. (Same as position(substring in string), but note the reversed argument order.)substr()Extracts the substring of string starting at the start'th character, and extending for count characters if that is specified. (Same as substring(string from start for count).)substring()Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.to_bin()Converts the number to its equivalent two's complement binary representation.to_hex()Converts the number to its equivalent two's complement hexadecimal representation.to_oct()Converts the number to its equivalent two's complement octal representation.translate()Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.trim()Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.unistr()Evaluate escaped Unicode characters in the argument. Unicode characters can be specified as \XXXX (4 hexadecimal digits), \+XXXXXX (6 hexadecimal digits), \uXXXX (4 hexadecimal digits), or \UXXXXXXXX (8 hexadecimal digits). To specify a backslash, write two backslashes. All other characters are taken literally. If the server encoding is not UTF-8, the Unicode code point identified by one of these escape sequences is converted to the actual server encoding; an error is reported if that's not possible. This function provides a (non-standard) alternative to string constants with Unicode escapes (see Section 4.1.2.3).upper()Converts the string to all upper case, according to the rules of the database's locale.
Aggregate Functions44
any_value()Returns an arbitrary value from the non-null input values.array_agg()Collects all the input values, including nulls, into an array.avg()Computes the average (arithmetic mean) of all the non-null input values.bit_and()Computes the bitwise AND of all non-null input values.bit_or()Computes the bitwise OR of all non-null input values.bit_xor()Computes the bitwise exclusive OR of all non-null input values. Can be useful as a checksum for an unordered set of values.bool_and()Returns true if all non-null input values are true, otherwise false.bool_or()Returns true if any non-null input value is true, otherwise false.corr()Computes the correlation coefficient.count()Computes the number of input rows.covar_pop()Computes the population covariance.covar_samp()Computes the sample covariance.every()This is the SQL standard's equivalent to bool_and.json_agg()Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.json_agg_strict()Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.json_object_agg()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.json_object_agg_strict()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,json_object_agg_unique()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.json_object_agg_unique_strict()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 error is thrown.jsonb_agg()Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.jsonb_agg_strict()Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.jsonb_object_agg()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.max()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.min()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.mode()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.percentile_cont()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.percentile_disc()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.regr_avgx()Computes the average of the independent variable, sum(X)/N.regr_avgy()Computes the average of the dependent variable, sum(Y)/N.regr_count()Computes the number of rows in which both inputs are non-null.regr_intercept()Computes the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.regr_r2()Computes the square of the correlation coefficient.regr_slope()Computes the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.regr_sxx()Computes the “sum of squares” of the independent variable, sum(X^2) - sum(X)^2/N.regr_sxy()Computes the “sum of products” of independent times dependent variables, sum(X*Y) - sum(X) * sum(Y)/N.regr_syy()Computes the “sum of squares” of the dependent variable, sum(Y^2) - sum(Y)^2/N.stddev()This is a historical alias for stddev_samp.stddev_pop()Computes the population standard deviation of the input values.stddev_samp()Computes the sample standard deviation of the input values.string_agg()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).sum()Computes the sum of the non-null input values.var_pop()Computes the population variance of the input values (square of the population standard deviation).var_samp()Computes the sample variance of the input values (square of the sample standard deviation).variance()This is a historical alias for var_samp.
JSON Functions37
array_to_json()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.json()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.json_array()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 case if a query_expression is used.json_array_length()Returns the number of elements in the top-level JSON array.json_build_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.json_build_object()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.JSON_EXISTS()Returns true if the SQL/JSON path_expression applied to the context_item yields any items, false otherwise. The ON ERROR clause specifies the behavior if an error occurs during path_expression evaluation. Specifying ERROR will cause an error to be thrown with the appropriate message. Other options include returning boolean values FALSE or TRUE or the value UNKNOWN which is actually an SQL NULL. The default when no ON ERROR clause is specified is to return the boolean value FALSE. Examples: JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR) →json_extract_path()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.)json_extract_path_text()Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)json_object()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 a cast to the json type. If WITH UNIQUE KEYS is specified, there must not be any duplicate key_expression. Any pair for which the value_expression evaluates to NULL is omitted from the output if ABSENT ON NULL is specified; if NULL ON NULL is specified or the clause omitted, the key is included with value NULL.JSON_QUERY()Returns the result of applying the SQL/JSON path_expression to the context_item. By default, the result is returned as a value of type jsonb, though the RETURNING clause can be used to return as some other type to which it can be successfully coerced. If the path expression may return multiple values, it might be necessary to wrap those values using the WITH WRAPPER clause to make it a valid JSON string, because the default behavior is to not wrap them, as if WITHOUT WRAPPER were specified. The WITH WRAPPER clause is by default taken to mean WITH UNCONDITIONAL WRAPPER, which means that even a single result value will be wrapped. To apply the wrapper only when multiple values are present, specify WITH CONDITIONAL WRAPPER. Getting multiple values in result will be treated as an error if WITHOUT WRAPPER is specified. If the result is a scalar string, by default, the returned value will be surrounded by quotes, making it a valid JSON value. It can be made explicit by specifying KEEP QUOTES. Conversely, quotes can be omitted by specifying OMIT QUOTES. To ensure that the result is a valid JSON value, OMIT QUOTES cannot be specified when WITH WRAPPER is also specified. The ON EMPTY clause specifies the behavior if evaluating path_expression yields an empty set. The ON ERROR clause specifies the behavior if an error occurs when evaluating path_expression, when coercing the result value to the RETURNING type, or when evaluating the ON EMPTY expression if the path_expression evaluation returns an empty set. For both ON EMPTY and ON ERROR, specifying ERROR will cause an error to be thrown with the appropriate message. Other options include returning an SQL NULL, an empty array (EMPTY [ARRAY]), an empty object (EMPTY OBJECT), or a user-specified expression (DEFAULT expression) that can be coerced to jsonb or the type specified in RETURNING. The default when ON EMPTY or ON ERROR is not specified is to return an SQL NULL value. Examples: JSON_QUERY(jsonb '{"a": "[1, 2]"}', 'lax $.a' RETURNING int[] OMIT QUOTES ERROR ON ERROR) →json_scalar()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 returned.json_serialize()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 default is text.json_strip_nulls()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.json_typeof()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.)JSON_VALUE()Returns the result of applying the SQL/JSON path_expression to the context_item. Only use JSON_VALUE() if the extracted value is expected to be a single SQL/JSON scalar item; getting multiple values will be treated as an error. If you expect that extracted value might be an object or an array, use the JSON_QUERY function instead. By default, the result, which must be a single scalar value, is returned as a value of type text, though the RETURNING clause can be used to return as some other type to which it can be successfully coerced. The ON ERROR and ON EMPTY clauses have similar semantics as mentioned in the description of JSON_QUERY, except the set of values returned in lieu of throwing an error is different. Note that scalar strings returned by JSON_VALUE always have their quotes removed, equivalent to specifying OMIT QUOTES in JSON_QUERY. Examples:jsonb_array_length()Returns the number of elements in the top-level JSON array.jsonb_build_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.jsonb_build_object()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.jsonb_extract_path()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.)jsonb_extract_path_text()Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)jsonb_insert()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 by the path is an object field, new_value will be inserted only if the object does not already contain that key. All earlier steps in the path must exist, or the target is returned unchanged. As with the path oriented operators, negative integers that appear in the path count from the end of JSON arrays. If the last path step is an array index that is out of range, the new value is added at the beginning of the array if the index is negative, or at the end of the array if it is positive.jsonb_object()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 elements, which are taken as a key/value pair. All values are converted to JSON strings.jsonb_path_exists()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 be a JSON object, and its fields provide named values to be substituted into the jsonpath expression. If the silent argument is specified and is true, the function suppresses the same errors as the @? and @@ operators do.jsonb_path_exists_tz()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-only value 2015-08-02 as a timestamp with time zone, so the result depends on the current TimeZone setting. Due to this dependency, these functions are marked as stable, which means these functions cannot be used in indexes. Their counterparts are immutable, and so can be used in indexes; but they will throw errors if asked to make such comparisons.jsonb_path_match()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 not a single boolean value.) The optional vars and silent arguments act the same as for jsonb_path_exists.jsonb_path_query_array()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.jsonb_path_query_array_tz()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-only value 2015-08-02 as a timestamp with time zone, so the result depends on the current TimeZone setting. Due to this dependency, these functions are marked as stable, which means these functions cannot be used in indexes. Their counterparts are immutable, and so can be used in indexes; but they will throw errors if asked to make such comparisons.jsonb_path_query_first()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.jsonb_path_query_first_tz()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-only value 2015-08-02 as a timestamp with time zone, so the result depends on the current TimeZone setting. Due to this dependency, these functions are marked as stable, which means these functions cannot be used in indexes. Their counterparts are immutable, and so can be used in indexes; but they will throw errors if asked to make such comparisons.jsonb_path_query_tz()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-only value 2015-08-02 as a timestamp with time zone, so the result depends on the current TimeZone setting. Due to this dependency, these functions are marked as stable, which means these functions cannot be used in indexes. Their counterparts are immutable, and so can be used in indexes; but they will throw errors if asked to make such comparisons.jsonb_set()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 target is returned unchanged. As with the path oriented operators, negative integers that appear in the path count from the end of JSON arrays. If the last path step is an array index that is out of range, and create_if_missing is true, the new value is added at the beginning of the array if the index is negative, or at the end of the array if it is positive.jsonb_set_lax()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'.jsonb_typeof()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.)row_to_json()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.to_json()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 will be used to perform the conversion;[a] otherwise, a scalar JSON value is produced. For any scalar other than a number, a Boolean, or a null value, the text representation will be used, with escaping as necessary to make it a valid JSON string value.to_jsonb()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 will be used to perform the conversion;[a] otherwise, a scalar JSON value is produced. For any scalar other than a number, a Boolean, or a null value, the text representation will be used, with escaping as necessary to make it a valid JSON string value.
Array Functions18
array_append()Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).array_cat()Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator).array_dims()Returns a text representation of the array's dimensions.array_fill()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).array_length()Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)array_lower()Returns the lower bound of the requested array dimension.array_ndims()Returns the number of dimensions of the array.array_position()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 DISTINCT FROM semantics, so it is possible to search for NULL.array_positions()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. NULL is returned only if the array is NULL; if the value is not found in the array, an empty array is returned.array_prepend()Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator).array_remove()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.array_replace()Replaces each array element equal to the second argument with the third argument.array_reverse()Reverses the first dimension of the array.array_sort()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 argument. If descending is true then sort in descending order, otherwise ascending order. If omitted, the default is ascending order. If nulls_first is true then nulls appear before non-null values, otherwise nulls appear after non-null values. If omitted, nulls_first is taken to have the same value as descending.array_to_string()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 string_to_array.array_upper()Returns the upper bound of the requested array dimension.cardinality()Returns the total number of elements in the array, or 0 if the array is empty.trim_array()Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.
Date/Time Functions14
age()Subtract arguments, producing a “symbolic” result that uses years and months, rather than just daysdate_bin()Bin input into specified interval aligned with specified origin; see Section 9.9.3date_part()Get timestamp subfield (equivalent to extract); see Section 9.9.1date_trunc()Truncate to specified precision; see Section 9.9.2extract()Get timestamp subfield; see Section 9.9.1isfinite()Test for finite date (not +/-infinity)justify_days()Adjust interval, converting 30-day time periods to monthsjustify_hours()Adjust interval, converting 24-hour time periods to daysjustify_interval()Adjust interval using justify_days and justify_hours, with additional sign adjustmentsmake_date()Create date from year, month and day fields (negative years signify BC)make_interval()Create interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zeromake_time()Create time from hour, minute and seconds fieldsmake_timestamp()Create timestamp from year, month, day, hour, minute and seconds fields (negative years signify BC)now()Current date and time (start of current transaction); see Section 9.9.5
Window Functions11
cume_dist()Returns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows). The value thus ranges from 1/N to 1.dense_rank()Returns the rank of the current row, without gaps; this function effectively counts peer groups.first_value()Returns value evaluated at the row that is the first row of the window frame.lag()Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.last_value()Returns value evaluated at the row that is the last row of the window frame.lead()Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.nth_value()Returns value evaluated at the row that is the n'th row of the window frame (counting from 1); returns NULL if there is no such row.ntile()Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.percent_rank()Returns the relative rank of the current row, that is (rank - 1) / (total partition rows - 1). The value thus ranges from 0 to 1 inclusive.rank()Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.row_number()Returns the number of the current row within its partition, counting from 1.
Formatting Functions3
Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.
