Functions
Version coverage matrixDefinitions for PostgreSQL 18
English manual coverage begins with PostgreSQL 7.1. Source history and runtime observations have their own recorded version boundaries.
Reading PG 18·Select a recorded version
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).
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 included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included.
Returns true if the SQL/JSON path_expression applied to the context_item yields any items, false otherwise.
Returns the result of applying the SQL/JSON path_expression to the context_item.
Each syntax element is described below in more detail.
Returns the result of applying the SQL/JSON path_expression to the context_item.
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:
The function xmlexists evaluates an XPath 1.0 expression (the first argument), with the passed XML value as its context item. The function returns false if the result of that evaluation yields an empty node-set, true if it yields any other value. The function returns null if any argument is null. A nonnull value passed as the context item must be an XML document, not a content fragment or any non-XML value.
The xmltable expression produces a table based on an XML value, an XPath filter to extract rows, and a set of column definitions. Although it syntactically resembles a function, it can only appear as a table in a query's FROM clause.
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 netmask part.)
Absolute value
Constructs an aclitem array holding the default access privileges for an object of type type belonging to the role with OID ownerId. This represents the access privileges that will be assumed when an object's ACL entry is null. (The default access privileges are described in Section 5.8.) The type parameter must be one of 'c' for COLUMN, 'r' for TABLE and table-like objects, 's' for SEQUENCE, 'd' for DATABASE, 'f' for FUNCTION or PROCEDURE, 'l' for LANGUAGE, 'L' for LARGE OBJECT, 'n' for SCHEMA, 'p' for PARAMETER, 't' for TABLESPACE, 'F' for FOREIGN DATA WRAPPER, 'S' for FOREIGN SERVER, or 'T' for TYPE or DOMAIN.
Returns the aclitem array as a set of rows. If the grantee is the pseudo-role PUBLIC, it is represented by zero in the grantee column. Each granted privilege is represented as SELECT, INSERT, etc (see Table 5.1 for a full list). Note that each privilege is broken out as a separate row, so only one keyword appears in the privilege_type column.
Inverse cosine, result in radians
Inverse cosine, result in degrees
Inverse hyperbolic cosine
Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days
Returns an arbitrary value from the non-null input values.
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.
Collects all the input values, including nulls, into an array.
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 DISTINCT FROM semantics, so it is possible to search for NULL.
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.
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 argument.
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 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.
Converts an array of text strings to a tsvector. The given strings are used as lexemes as-is, without further processing. Array elements must not be empty strings or NULL.
Returns the upper bound of the requested array dimension.
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.
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
Computes the average (arithmetic mean) of all the non-null input values.
Computes the bitwise AND of all non-null input values.
Returns the number of bits set in the binary string (also known as “popcount”).
Returns number of bits in the string (8 times the octet_length).
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 bounding box of two boxes.
Computes box inscribed within the circle.
Removes the BRIN index tuple that summarizes the page range covering the given table block, if there is one.
Scans the specified BRIN index to find page ranges in the base table that are not currently summarized by the index; for any such range it creates a new summary index tuple by scanning those table pages. Returns the number of new page range summaries that were inserted into the index.
Summarizes the page range covering the given block, if not already summarized. This is like brin_summarize_new_values except that it only processes the page range that covers the given table block number.
Computes the broadcast address for the address's network.
Removes the longest string containing only characters in characters (a space by default) from the start and end of string.
Returns the total number of elements in the array, or 0 if the array is empty.
Performs case folding of the input string according to the collation. Case folding is similar to case conversion, but the purpose of case folding is to facilitate case-insensitive matching of strings, whereas the purpose of case conversion is to convert to a particular cased form. This function can only be used when the server encoding is UTF8.
Cube root
Nearest integer greater than or equal to argument
Nearest integer greater than or equal to argument (same as ceil)
Computes center point. Available for box, circle.
Returns number of characters in the string.
Returns number of characters in the string.
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.
Computes smallest circle enclosing box.
Current date and time (changes during statement execution); see Section 9.9.5
Returns the comment for a table column, which is specified by the OID of its table and its column number. (obj_description cannot be used for table columns, since columns do not have OIDs of their own.)
Concatenates the text representations of all the arguments. NULL arguments are ignored.
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.
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 correlation coefficient.
Cosine, argument in radians
Cosine, argument in degrees
Hyperbolic cosine
Cotangent, argument in radians
Cotangent, argument in degrees
Computes the number of input rows.
Computes the population covariance.
Computes the sample covariance.
Computes the CRC-32 value of the binary string.
Computes the CRC-32C value of the binary string.
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.
Returns the name of the current database. (Databases are called “catalogs” in the SQL standard, so current_catalog is the standard's spelling.)
Returns the name of the current database. (Databases are called “catalogs” in the SQL standard, so current_catalog is the standard's spelling.)
Current date; see Section 9.9.5
Returns the text of the currently executing query, as submitted by the client (which might contain more than one statement).
This is equivalent to current_user.
Returns the name of the schema that is first in the search path (or a null value if the search path is empty). This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.
Returns an array of the names of all schemas presently in the effective search path, in their priority order. (Items in the current search_path setting that do not correspond to existing, searchable schemas are omitted.) If the Boolean argument is true, then implicitly-searched system schemas such as pg_catalog are included in the result.
Returns the current value of the setting setting_name. If there is no such setting, current_setting throws an error unless missing_ok is supplied and is true (in which case NULL is returned). This function corresponds to the SQL command SHOW.
Current time of day; see Section 9.9.5
Current date and time (start of current transaction); see Section 9.9.5
Returns the user name of the current execution context.
Returns the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.
table_to_xml maps the content of the named table, passed as parameter table. The regclass type accepts strings identifying tables using the usual notation, including optional schema qualification and double quotes (see Section 8.19 for details). query_to_xml executes the query whose text is passed as parameter query and maps the result set. cursor_to_xml fetches the indicated number of rows from the cursor specified by the parameter cursor. This variant is recommended if large tables have to be mapped, because the result value is built up in memory by each function.
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
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 equivalent to the timestamp with time zone + interval operator.
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 equivalent to the timestamp with time zone - interval operator.
Truncate to specified precision; see Section 9.9.2
Decodes binary data from a textual representation; supported format values are the same as for encode.
Converts radians to degrees
Computes the rank of the hypothetical row, without gaps; this function effectively counts peer groups.
Extracts box's diagonal as a line segment (same as lseg(box)).
Computes diameter of circle.
Integer quotient of y/x (truncates towards zero)
Encodes binary data into a textual representation; supported format values are: base64, escape, hex.
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.
Error function
Complementary error function (1 - erf(x), without loss of precision for large inputs)
This is the SQL standard's equivalent to bool_and.
Exponential (e raised to the given power)
Get timestamp subfield; see Section 9.9.1
Factorial
Returns the address's family: 4 for IPv4, 6 for IPv6.
Returns value evaluated at the row that is the first row of the window frame.
Nearest integer less than or equal to argument
Formats arguments according to a format string; see Section 9.4.1. This function is similar to the C function sprintf.
Returns the SQL name for a data type that is identified by its type OID and possibly a type modifier. Pass NULL for the type modifier if no specific modifier is known.
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
Generates a version 4 (random) UUID
Generates a series of values from start to stop, with a step size of step. step defaults to 1.
Generates a series comprising the valid subscripts of the dim'th dimension of the given array.
Extracts n'th bit from binary string.
Extracts n'th byte from binary string.
Returns the OID of the current default text search configuration (as set by default_text_search_config).
Cleans up the “pending” list of the specified GIN index by moving entries in it, in bulk, to the main GIN data structure. Returns the number of pages removed from the pending list. If the argument is a GIN index built with the fastupdate option disabled, no cleanup happens and the result is zero, because the index doesn't have a pending list. See Section 65.4.4.1 and Section 65.4.5 for details about the pending list and fastupdate option.
Does user have privilege for any column of table? This succeeds either if the privilege is held for the whole table, or if there is a column-level grant of the privilege for at least one column. Allowable privilege types are SELECT, INSERT, UPDATE, and REFERENCES.
Does user have privilege for the specified table column? This succeeds either if the privilege is held for the whole table, or if there is a column-level grant of the privilege for the column. The column can be specified by name or by attribute number (pg_attribute.attnum). Allowable privilege types are SELECT, INSERT, UPDATE, and REFERENCES.
Does user have privilege for database? Allowable privilege types are CREATE, CONNECT, TEMPORARY, and TEMP (which is equivalent to TEMPORARY).
Does user have privilege for foreign-data wrapper? The only allowable privilege type is USAGE.
Does user have privilege for function? The only allowable privilege type is EXECUTE.
Does user have privilege for language? The only allowable privilege type is USAGE.
Does user have privilege for large object? Allowable privilege types are SELECT and UPDATE.
Does user have privilege for configuration parameter? The parameter name is case-insensitive. Allowable privilege types are SET and ALTER SYSTEM.
Does user have privilege for schema? Allowable privilege types are CREATE and USAGE.
Does user have privilege for sequence? Allowable privilege types are USAGE, SELECT, and UPDATE.
Does user have privilege for foreign server? The only allowable privilege type is USAGE.
Does user have privilege for table? Allowable privilege types are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and MAINTAIN.
Does user have privilege for tablespace? The only allowable privilege type is CREATE.
Does user have privilege for data type? The only allowable privilege type is USAGE. When specifying a type by name rather than by OID, the allowed input is the same as for the regtype data type (see Section 8.19).
Computes vertical size of box.
Returns the IP address as text, ignoring the netmask.
Computes the host mask for the address's network.
Returns a string representing the version of Unicode used by ICU, if the server was built with ICU support; otherwise returns NULL
Returns the IP address of the current client, or NULL if the current connection is via a Unix-domain socket.
Returns the IP port number of the current client, or NULL if the current connection is via a Unix-domain socket.
Computes the smallest network that includes both of the given networks.
Tests whether the addresses belong to the same IP family.
Returns the IP address on which the server accepted the current connection, or NULL if the current connection is via a Unix-domain socket.
Returns the IP port number on which the server accepted the current connection, or NULL if the current connection is via a Unix-domain socket.
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.
Is path closed?
Is the range empty?
Test for finite date (not +/-infinity)
Is path open?
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.
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.
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.
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.
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 order rather than in the input order.
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 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.
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 error is thrown.
Returns the set of keys in the top-level JSON object.
Behaves like json_object, but as an aggregate function, so it only takes one key_expression and one value_expression parameter.
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. (Fields that do not correspond to any output column name are ignored.) In typical use, the value of base is just NULL, which means that any output columns that do not match any object field will be filled with nulls. However, if base isn't NULL then the values it contains will be used for unmatched columns.
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 returned.
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.
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 filled from fields of the JSON object, in the same way as described above for json[b]_populate_record. Since there is no input record value, unmatched columns are always filled with nulls.
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 element of the JSON array is processed as described above for json[b]_populate_record.
Selects each item in the JSON document that is requested by the filter and converts each one to a tsvector, normalizing words according to the specified or default configuration. The results are then concatenated in document order to produce the output. Position information is generated as though one stopword exists between each pair of selected items. (Beware that “document order” of the fields of a JSON object is implementation-dependent when the input is jsonb.) The filter must be a jsonb array containing zero or more of these keywords: "string" (to include all string values), "numeric" (to include all numeric values), "boolean" (to include all boolean values), "key" (to include all keys), or "all" (to include all the above). As a special case, the filter can also be a simple JSON value that is one of these keywords.
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.)
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.
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 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.
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.
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 error is thrown.
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 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.
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.
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.
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.
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, false, or null. The optional vars and silent arguments act the same as for jsonb_path_exists.
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-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.
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-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.
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.
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. (Fields that do not correspond to any output column name are ignored.) In typical use, the value of base is just NULL, which means that any output columns that do not match any object field will be filled with nulls. However, if base isn't NULL then the values it contains will be used for unmatched columns.
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 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.
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 filled from fields of the JSON object, in the same way as described above for json[b]_populate_record. Since there is no input record value, unmatched columns are always filled with nulls.
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 element of the JSON array is processed as described above for json[b]_populate_record.
Selects each item in the JSON document that is requested by the filter and converts each one to a tsvector, normalizing words according to the specified or default configuration. The results are then concatenated in document order to produce the output. Position information is generated as though one stopword exists between each pair of selected items. (Beware that “document order” of the fields of a JSON object is implementation-dependent when the input is jsonb.) The filter must be a jsonb array containing zero or more of these keywords: "string" (to include all string values), "numeric" (to include all numeric values), "boolean" (to include all boolean values), "key" (to include all keys), or "all" (to include all the above). As a special case, the filter can also be a simple JSON value that is one of these keywords.
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.)
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
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.
Returns value evaluated at the row that is the last row of the window frame.
Returns the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current session. It is an error to call lastval if nextval has not yet been called in the current session.
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
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.
Returns first n characters in the string, or when n is negative, returns all but last |n| characters.
Returns the number of characters in the string.
Natural logarithm of the absolute value of the gamma function
Converts two points to the line through them.
Natural logarithm
Current time of day; see Section 9.9.5
Current date and time (start of current transaction); see Section 9.9.5
Base 10 logarithm
Base 10 logarithm (same as log)
Converts the string to all lower case, according to the rules of the database's locale.
Is the range's lower bound inclusive?
Does the range have no lower bound? (A lower bound of -Infinity returns false.)
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).
Extracts box's diagonal as a line segment.
Removes the longest string containing only characters in characters (a space by default) from the start of string.
Sets the 7th bit of the address to one, creating what is known as modified EUI-64, for inclusion in an IPv6 address.
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
Constructs an aclitem with the given properties. privileges is a comma-separated list of privilege names such as SELECT, INSERT, etc, all of which are set in the result. (Case of the privilege string is not significant, and extra whitespace is allowed between but not within privilege names.)
Returns the netmask length in bits.
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 MD5 hash of the argument, with the result written in hexadecimal.
Returns the merge action command executed for the current row. This will be 'INSERT', 'UPDATE', or 'DELETE'.
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.
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
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.
Returns a multirange containing just the given range.
Returns the number of multixacts IDs between the supplied multixact ID and the current multixacts counter.
Computes the network mask for the address's network.
Returns the network part of the address, zeroing out whatever is to the right of the netmask. (This is equivalent to casting the value to cidr.)
Advances the sequence object to its next value and returns that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value. If the sequence object has been created with default parameters, successive nextval calls will return successive values beginning with 1. Other behaviors can be obtained by using appropriate parameters in the CREATE SEQUENCE command.
Converts the string to the specified Unicode normalization form. The optional form key word specifies the form: NFC (the default), NFD, NFKC, or NFKD. This function can only be used when the server encoding is UTF8.
Current date and time (start of current transaction); see Section 9.9.5
Returns the number of points. Available for path, polygon.
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.
Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.
Returns the number of non-null arguments.
Returns the number of null arguments.
Returns the number of lexemes plus operators in the tsquery.
Returns the comment for a database object specified by its OID and the name of the containing system catalog. For example, obj_description(123456, 'pg_class') would retrieve the comment for the table with OID 123456.
Returns number of bytes in the string.
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.
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[].
Converts polygon to a closed path with the same list of points.
Converts path to closed form.
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.
Obtains an exclusive session-level advisory lock, waiting if necessary.
Obtains a shared session-level advisory lock, waiting if necessary.
Releases a previously-acquired exclusive session-level advisory lock. Returns true if the lock is successfully released. If the lock was not held, false is returned, and in addition, an SQL warning will be reported by the server.
Releases all session-level advisory locks held by the current session. (This function is implicitly invoked at session end, even if the client disconnects ungracefully.)
Releases a previously-acquired shared session-level advisory lock. Returns true if the lock is successfully released. If the lock was not held, false is returned, and in addition, an SQL warning will be reported by the server.
Obtains an exclusive transaction-level advisory lock, waiting if necessary.
Obtains a shared transaction-level advisory lock, waiting if necessary.
Returns information about the WAL summary files present in the data directory, under pg_wal/summaries. One row will be returned per WAL summary file. Each file summarizes WAL on the indicated TLI within the indicated LSN range. This function might be useful to determine whether enough WAL summaries are present on the server to take an incremental backup based on some prior backup whose start LSN is known.
Returns the process ID of the server process attached to the current session.
Prepares the server to begin an on-line backup. The only required parameter is an arbitrary user-defined label for the backup. (Typically this would be the name under which the backup dump file will be stored.) If the optional second parameter is given as true, it specifies executing pg_backup_start as quickly as possible. This forces an immediate checkpoint which will cause a spike in I/O operations, slowing any concurrently executing queries.
Finishes performing an on-line backup. The desired contents of the backup label file and the tablespace map file are returned as part of the result of the function and must be written to files in the backup area. These files must not be written to the live data directory (doing so will cause PostgreSQL to fail to restart in the event of a crash).
Returns the OID of the base type of a domain identified by its type OID. If the argument is the OID of a non-domain type, returns the argument as-is. Returns NULL if the argument is not a valid type OID. If there's a chain of domain dependencies, it will recurse until finding the base type.
Returns an array of the process ID(s) of the sessions that are blocking the server process with the specified process ID from acquiring a lock, or an empty array if there is no such server process or it is not blocked.
Cancels the current query of the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being canceled or the calling role has privileges of pg_signal_backend, however only superusers can cancel superuser backends. As an exception, roles with privileges of pg_signal_autovacuum_worker are permitted to cancel autovacuum worker processes, which are otherwise considered superuser backends.
Converts the supplied encoding name into an integer representing the internal identifier used in some system catalog tables. Returns -1 if an unknown encoding name is provided.
Clears column-level statistics for the given relation and attribute, as though the table was newly created.
Clears table-level statistics for the given relation, as though the table was newly created.
Returns current client encoding name.
Returns the actual version of the collation object as it is currently installed in the operating system. If this is different from the value in pg_collation.collversion, then objects depending on the collation might need to be rebuilt. See also ALTER COLLATION.
Is collation visible in search path?
Shows the compression algorithm that was used to compress an individual variable-length value. Returns NULL if the value is not compressed.
Shows the number of bytes used to store any individual data value. If applied directly to a table column value, this reflects any compression that was done.
Shows the chunk_id of an on-disk TOASTed value. Returns NULL if the value is un-TOASTed or not on-disk. See Section 66.2 for more information about TOAST.
Returns the time when the server configuration files were last loaded. If the current session was alive at the time, this will be the time when the session itself re-read the configuration files (so the reading will vary a little in different sessions). Otherwise it is the time when the postmaster process re-read the configuration files.
Returns information about current checkpoint state, as shown in Table 9.89.
Returns information about cluster initialization state, as shown in Table 9.91.
Returns information about recovery state, as shown in Table 9.92.
Returns information about current control file state, as shown in Table 9.90.
Is conversion visible in search path?
Copies an existing logical replication slot named src_slot_name to a logical replication slot named dst_slot_name, optionally changing the output plugin and persistence. The copied logical slot starts from the same LSN as the source logical slot. Both temporary and plugin are optional; if they are omitted, the values of the source slot are used. The failover option of the source logical slot is not copied and is set to false by default. This is to avoid the risk of being unable to continue logical replication after failover to standby where the slot is being synchronized. Copy of an invalidated slot is not allowed.
Copies an existing physical replication slot named src_slot_name to a physical replication slot named dst_slot_name. The copied physical slot starts to reserve WAL from the same LSN as the source slot. temporary is optional. If temporary is omitted, the same value as the source slot is used. Copy of an invalidated slot is not allowed.
Creates a new logical (decoding) replication slot named slot_name using the output plugin plugin. The optional third parameter, temporary, when set to true, specifies that the slot should not be permanently stored to disk and is only meant for use by the current session. Temporary slots are also released upon any error. The optional fourth parameter, twophase, when set to true, specifies that the decoding of prepared transactions is enabled for this slot. The optional fifth parameter, failover, when set to true, specifies that this slot is enabled to be synced to the standbys so that logical replication can be resumed after failover. A call to this function has the same effect as the replication protocol command CREATE_REPLICATION_SLOT ... LOGICAL.
Creates a new physical replication slot named slot_name. The optional second parameter, when true, specifies that the LSN for this replication slot be reserved immediately; otherwise the LSN is reserved on first connection from a streaming replication client. Streaming changes from a physical slot is only possible with the streaming-replication protocol — see Section 54.4. The optional third parameter, temporary, when set to true, specifies that the slot should not be permanently stored to disk and is only meant for use by the current session. Temporary slots are also released upon any error. This function corresponds to the replication protocol command CREATE_REPLICATION_SLOT ... PHYSICAL.
Creates a named marker record in the write-ahead log that can later be used as a recovery target, and returns the corresponding write-ahead log location. The given name can then be used with recovery_target_name to specify the point up to which recovery will proceed. Avoid creating multiple restore points with the same name, since recovery will stop at the first one whose name matches the recovery target.
Returns the path name of the log file currently in use by the logging collector. The path includes the log_directory directory and the individual log file name. The result is NULL if the logging collector is disabled. When multiple log files exist, each in a different format, pg_current_logfile without an argument returns the path of the file having the first format found in the ordered list: stderr, csvlog, jsonlog. NULL is returned if no log file has any of these formats. To request information about a specific log file format, supply either csvlog, jsonlog or stderr as the value of the optional parameter. The result is NULL if the log format requested is not configured in log_destination. The result reflects the contents of the current_logfiles file.
Returns a current snapshot, a data structure showing which transaction IDs are now in-progress. Only top-level transaction IDs are included in the snapshot; subtransaction IDs are not shown; see Section 67.3 for details.
Returns the current write-ahead log flush location (see notes below).
Returns the current write-ahead log insert location (see notes below).
Returns the current write-ahead log write location (see notes below).
Returns the current transaction's ID. It will assign a new one if the current transaction does not have one already (because it has not performed any database updates); see Section 67.1 for details. If executed in a subtransaction, this will return the top-level transaction ID; see Section 67.3 for details.
Returns the current transaction's ID, or NULL if no ID is assigned yet. (It's best to use this variant if the transaction might otherwise be read-only, to avoid unnecessary consumption of an XID.) If executed in a subtransaction, this will return the top-level transaction ID.
Returns the actual version of the database's collation as it is currently installed in the operating system. If this is different from the value in pg_database.datcollversion, then objects depending on the collation might need to be rebuilt. See also ALTER DATABASE.
Computes the total disk space used by the database with the specified name or OID. To use this function, you must have CONNECT privilege on the specified database (which is granted by default) or have privileges of the pg_read_all_stats role.
Returns a textual description of a database object identified by catalog OID, object OID, and sub-object ID (such as a column number within a table; the sub-object ID is zero when referring to a whole object). This description is intended to be human-readable, and might be translated, depending on server configuration. This is especially useful to determine the identity of an object referenced in the pg_depend catalog. This function returns NULL values for undefined objects.
Drops the physical or logical replication slot named slot_name. Same as replication protocol command DROP_REPLICATION_SLOT.
Converts the integer used as the internal identifier of an encoding in some system catalog tables into a human-readable string. Returns an empty string if an invalid encoding number is provided.
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 returns one row for each base command executed; some commands that are a single SQL sentence may return more than one row. This function returns the following columns:
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 access method has changed).
Saves the transaction's current snapshot and returns a text string identifying the snapshot. This string must be passed (outside the database) to clients that want to import the snapshot. The snapshot is available for import only until the end of the transaction that exported it.
Returns a relation's OID given the tablespace OID and filenode it is stored under. This is essentially the inverse mapping of pg_relation_filepath. For a relation in the database's default tablespace, the tablespace can be specified as zero. Returns NULL if no relation in the current database is associated with the given values, or if dealing with a temporary relation.
Is function visible in search path? (This also works for procedures and aggregates.)
Returns the ACL for a database object, specified by catalog OID, object OID and sub-object ID. This function returns NULL values for undefined objects.
Returns a set of records describing the foreign key relationships that exist within the PostgreSQL system catalogs. The fktable column contains the name of the referencing catalog, and the fkcols column contains the name(s) of the referencing column(s). Similarly, the pktable column contains the name of the referenced catalog, and the pkcols column contains the name(s) of the referenced column(s). If is_array is true, the last referencing column is an array, each of whose elements should match some entry in the referenced catalog. If is_opt is true, the referencing column(s) are allowed to contain zeroes instead of a valid reference.
Reconstructs the creating command for a constraint. (This is a decompiled reconstruction, not the original text of the command.)
Decompiles the internal form of an expression stored in the system catalogs, such as the default value for a column. If the expression might contain Vars, specify the OID of the relation they refer to as the second parameter; if no Vars are expected, passing zero is sufficient.
Reconstructs the argument list of a function or procedure, in the form it would need to appear in within CREATE FUNCTION (including default values).
Reconstructs the argument list necessary to identify a function or procedure, in the form it would need to appear in within commands such as ALTER FUNCTION. This form omits default values.
Reconstructs the RETURNS clause of a function, in the form it would need to appear in within CREATE FUNCTION. Returns NULL for a procedure.
Reconstructs the creating command for a function or procedure. (This is a decompiled reconstruction, not the original text of the command.) The result is a complete CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE statement.
Reconstructs the creating command for an index. (This is a decompiled reconstruction, not the original text of the command.) If column is supplied and is not zero, only the definition of that column is reconstructed.
Returns a set of records describing the SQL keywords recognized by the server. The word column contains the keyword. The catcode column contains a category code: U for an unreserved keyword, C for a keyword that can be a column name, T for a keyword that can be a type or function name, or R for a fully reserved keyword. The barelabel column contains true if the keyword can be used as a “bare” column label in SELECT lists, or false if it can only be used after AS. The catdesc column contains a possibly-localized string describing the keyword's category. The baredesc column contains a possibly-localized string describing the keyword's column label status.
Returns a list of the loadable modules that are loaded into the current server session. The module_name and version fields are NULL unless the module author supplied values for them using the PG_MODULE_MAGIC_EXT macro. The file_name field gives the file name of the module (shared library).
Returns the transaction ID and lock mode for each member of the specified multixact ID. The lock modes forupd, fornokeyupd, sh, and keysh correspond to the row-level locks FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE, respectively, as described in Section 13.3.2. Two additional modes are specific to multixacts: nokeyupd, used by updates that do not modify key columns, and upd, used by updates or deletes that modify key columns.
Returns a row containing enough information to uniquely identify the database object specified by a type code and object name and argument arrays. The returned values are the ones that would be used in system catalogs such as pg_depend; they can be passed to other system functions such as pg_describe_object or pg_identify_object. classid is the OID of the system catalog containing the object; objid is the OID of the object itself, and objsubid is the sub-object ID, or zero if none. This function is the inverse of pg_identify_object_as_address. Undefined objects are identified with NULL values.
Reconstructs the definition of a partition constraint. (This is a decompiled reconstruction, not the original text of the command.)
Reconstructs the definition of a partitioned table's partition key, in the form it would have in the PARTITION BY clause of CREATE TABLE. (This is a decompiled reconstruction, not the original text of the command.)
Reconstructs the creating command for a rule. (This is a decompiled reconstruction, not the original text of the command.)
Returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. If the column is an identity column, the associated sequence is the sequence internally created for that column. For columns created using one of the serial types (serial, smallserial, bigserial), it is the sequence created for that serial column definition. In the latter case, the association can be modified or removed with ALTER SEQUENCE OWNED BY. (This function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it has historically been used with serial-type columns.) The first parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter potentially contains both schema and table names, it is parsed per usual SQL rules, meaning it is lower-cased by default. The second parameter, being just a column name, is treated literally and so has its case preserved. The result is suitably formatted for passing to the sequence functions (see Section 9.17).
Reconstructs the creating command for an extended statistics object. (This is a decompiled reconstruction, not the original text of the command.)
Reconstructs the creating command for a trigger. (This is a decompiled reconstruction, not the original text of the command.)
Returns a role's name given its OID.
Reconstructs the underlying SELECT command for a view or materialized view. (This is a decompiled reconstruction, not the original text of the command.)
Returns recovery pause state. The return values are not paused if pause is not requested, pause requested if pause is requested but recovery is not yet paused, and paused if the recovery is actually paused.
Returns the currently-loaded WAL resource managers in the system. The column rm_builtin indicates whether it's a built-in resource manager, or a custom resource manager loaded by an extension.
Returns information about the progress of the WAL summarizer. If the WAL summarizer has never run since the instance was started, then summarized_tli and summarized_lsn will be 0 and 0/0 respectively; otherwise, they will be the TLI and ending LSN of the last WAL summary file written to disk. If the WAL summarizer is currently running, pending_lsn will be the ending LSN of the last record that it has consumed, which must always be greater than or equal to summarized_lsn; if the WAL summarizer is not running, it will be equal to summarized_lsn. summarizer_pid is the PID of the WAL summarizer process, if it is running, and otherwise NULL.
Does user have privilege for role? Allowable privilege types are MEMBER, USAGE, and SET. MEMBER denotes direct or indirect membership in the role without regard to what specific privileges may be conferred. USAGE denotes whether the privileges of the role are immediately available without doing SET ROLE, while SET denotes whether it is possible to change to the role using the SET ROLE command. WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of these privilege types to test whether the ADMIN privilege is held (all six spellings test the same thing). This function does not allow the special case of setting user to public, because the PUBLIC pseudo-role can never be a member of real roles.
Returns a row containing enough information to uniquely identify the database object specified by catalog OID, object OID and sub-object ID. This information is intended to be machine-readable, and is never translated. type identifies the type of database object; schema is the schema name that the object belongs in, or NULL for object types that do not belong to schemas; name is the name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL; identity is the complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary. Undefined objects are identified with NULL values.
Returns a row containing enough information to uniquely identify the database object specified by catalog OID, object OID and sub-object ID. The returned information is independent of the current server, that is, it could be used to identify an identically named object in another server. type identifies the type of database object; object_names and object_args are text arrays that together form a reference to the object. These three values can be passed to pg_get_object_address to obtain the internal address of the object.
Adds collations to the system catalog pg_collation based on all the locales it finds in the operating system. This is what initdb uses; see Section 23.2.2 for more details. If additional locales are installed into the operating system later on, this function can be run again to add collations for the new locales. Locales that match existing entries in pg_collation will be skipped. (But collation objects based on locales that are no longer present in the operating system are not removed by this function.) The schema parameter would typically be pg_catalog, but that is not a requirement; the collations could be installed into some other schema as well. The function returns the number of new collation objects it created. Use of this function is restricted to superusers.
Tests whether an index column has the named property. Common index column properties are listed in Table 9.77. (Note that extension access methods can define additional property names for their indexes.) NULL is returned if the property name is not known or does not apply to the particular object, or if the OID or column number does not identify a valid object.
Tests whether an index has the named property. Common index properties are listed in Table 9.78. (Note that extension access methods can define additional property names for their indexes.) NULL is returned if the property name is not known or does not apply to the particular object, or if the OID does not identify a valid object.
Tests whether an index access method has the named property. Access method properties are listed in Table 9.79. NULL is returned if the property name is not known or does not apply to the particular object, or if the OID does not identify a valid object.
Computes the total disk space used by indexes attached to the specified table.
Tests whether the given string is valid input for the specified data type; if not, return the details of the error that would have been thrown. If the input is valid, the results are NULL. The inputs are the same as for pg_input_is_valid.
Tests whether the given string is valid input for the specified data type, returning true or false.
Returns true if recovery is still in progress.
Returns true if the given OID is the OID of another session's temporary schema. (This can be useful, for example, to exclude other sessions' temporary tables from a catalog display.)
Returns true if recovery pause is requested.
Returns true if a JIT compiler extension is available (see Chapter 30) and the jit configuration parameter is set to on.
Returns the transaction ID, commit timestamp and replication origin of the latest committed transaction.
Returns the last write-ahead log location that has been received and synced to disk by streaming replication. While streaming replication is in progress this will increase monotonically. If recovery has completed then this will remain static at the location of the last WAL record received and synced to disk during recovery. If streaming replication is disabled, or if it has not yet started, the function returns NULL.
Returns the last write-ahead log location that has been replayed during recovery. If recovery is still in progress this will increase monotonically. If recovery has completed then this will remain static at the location of the last WAL record applied during recovery. When the server has been started normally without recovery, the function returns NULL.
Returns the time stamp of the last transaction replayed during recovery. This is the time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed during recovery, the function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this will remain static at the time of the last transaction applied during recovery. When the server has been started normally without recovery, the function returns NULL.
Returns the set of names of asynchronous notification channels that the current session is listening to.
Requests to log the memory contexts of the backend with the specified process ID. This function can send the request to backends and auxiliary processes except logger. These memory contexts will be logged at LOG message level. They will appear in the server log based on the log configuration set (see Section 19.8 for more information), but will not be sent to the client regardless of client_min_messages.
Take a snapshot of running transactions and write it to WAL, without having to wait for bgwriter or checkpointer to log one. This is useful for logical decoding on standby, as logical slot creation has to wait until such a record is replayed on the standby.
Emits a logical decoding message. This can be used to pass generic messages to logical decoding plugins through WAL. The transactional parameter specifies if the message should be part of the current transaction, or if it should be written immediately and decoded as soon as the logical decoder reads the record. The prefix parameter is a textual prefix that can be used by logical decoding plugins to easily recognize messages that are interesting for them. The content parameter is the content of the message, given either in text or binary form. The flush parameter (default set to false) controls if the message is immediately flushed to WAL or not. flush has no effect with transactional, as the message's WAL record is flushed along with its transaction.
Behaves just like the pg_logical_slot_get_changes() function, except that changes are returned as bytea.
Returns changes in the slot slot_name, starting from the point from which changes have been consumed last. If upto_lsn and upto_nchanges are NULL, logical decoding will continue until end of WAL. If upto_lsn is non-NULL, decoding will include only those transactions which commit prior to the specified LSN. If upto_nchanges is non-NULL, decoding will stop when the number of rows produced by decoding exceeds the specified value. Note, however, that the actual number of rows returned may be larger, since this limit is only checked after adding the rows produced when decoding each new transaction commit. If the specified slot is a logical failover slot then the function will not return until all physical slots specified in synchronized_standby_slots have confirmed WAL receipt.
Behaves just like the pg_logical_slot_peek_changes() function, except that changes are returned as bytea.
Behaves just like the pg_logical_slot_get_changes() function, except that changes are not consumed; that is, they will be returned again on future calls.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's WAL archive status directory (pg_wal/archive_status). Filenames beginning with a dot, directories, and other special files are excluded.
Returns the names of all files (and directories and other special files) in the specified directory. The include_dot_dirs parameter indicates whether “.” and “..” are to be included in the result set; the default is to exclude them. Including them can be useful when missing_ok is true, to distinguish an empty directory from a non-existent directory.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's log directory. Filenames beginning with a dot, directories, and other special files are excluded.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's pg_logical/mappings directory. Filenames beginning with a dot, directories, and other special files are excluded.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's pg_logical/snapshots directory. Filenames beginning with a dot, directories, and other special files are excluded.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's pg_replslot/slot_name directory, where slot_name is the name of the replication slot provided as input of the function. Filenames beginning with a dot, directories, and other special files are excluded.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's WAL summaries directory (pg_wal/summaries). Filenames beginning with a dot, directories, and other special files are excluded.
Returns the name, size, and last modification time (mtime) of each ordinary file in the temporary file directory for the specified tablespace. If tablespace is not provided, the pg_default tablespace is examined. Filenames beginning with a dot, directories, and other special files are excluded.
Returns the name, size, and last modification time (mtime) of each ordinary file in the server's write-ahead log (WAL) directory. Filenames beginning with a dot, directories, and other special files are excluded.
pg_mcv_list_items returns a set of records describing all items stored in a multi-column MCV list. It returns the following columns:
Returns the OID of the current session's temporary schema, or zero if it has none (because it has not created any temporary tables).
Returns the fraction (0–1) of the asynchronous notification queue's maximum size that is currently occupied by notifications that are waiting to be processed. See LISTEN and NOTIFY for more information.
Returns true if the server has been compiled with NUMA support.
Is operator class visible in search path?
Is operator visible in search path?
Is operator family visible in search path?
Returns the set of storage options represented by a value from pg_class.reloptions or pg_attribute.attoptions.
Lists the ancestor relations of the given partition, including the relation itself. Returns no rows if the relation does not exist or is not a partition or partitioned table.
Returns the top-most parent of the partition tree to which the given relation belongs. Returns NULL if the relation does not exist or is not a partition or partitioned table.
Lists the tables or indexes in the partition tree of the given partitioned table or partitioned index, with one row for each partition. Information provided includes the OID of the partition, the OID of its immediate parent, a boolean value telling if the partition is a leaf, and an integer telling its level in the hierarchy. The level value is 0 for the input table or index, 1 for its immediate child partitions, 2 for their partitions, and so on. Returns no rows if the relation does not exist or is not a partition or partitioned table.
Returns the time when the server started.
Promotes a standby server to primary status. With wait set to true (the default), the function waits until promotion is completed or wait_seconds seconds have passed, and returns true if promotion is successful and false otherwise. If wait is set to false, the function returns true immediately after sending a SIGUSR1 signal to the postmaster to trigger promotion.
Returns all or part of a file. This function is identical to pg_read_file except that it can read arbitrary binary data, returning the result as bytea not text; accordingly, no encoding checks are performed.
Returns all or part of a text file, starting at the given byte offset, returning at most length bytes (less if the end of file is reached first). If offset is negative, it is relative to the end of the file. If offset and length are omitted, the entire file is returned. The bytes read from the file are interpreted as a string in the database's encoding; an error is thrown if they are not valid in that encoding.
Returns the “filenode” number currently assigned to the specified relation. The filenode is the base component of the file name(s) used for the relation (see Section 66.1 for more information). For most relations the result is the same as pg_class.relfilenode, but for certain system catalogs relfilenode is zero and this function must be used to get the correct value. The function returns NULL if passed a relation that does not have storage, such as a view.
Returns the entire file path name (relative to the database cluster's data directory, PGDATA) of the relation.
Computes the disk space used by one “fork” of the specified relation. (Note that for most purposes it is more convenient to use the higher-level functions pg_total_relation_size or pg_table_size, which sum the sizes of all forks.) With one argument, this returns the size of the main data fork of the relation. The second argument can be provided to specify which fork to examine:
Causes all processes of the PostgreSQL server to reload their configuration files. (This is initiated by sending a SIGHUP signal to the postmaster process, which in turn sends SIGHUP to each of its children.) You can use the pg_file_settings, pg_hba_file_rules and pg_ident_file_mappings views to check the configuration files for possible errors, before reloading.
Sets replication progress for the given node to the given location. This is primarily useful for setting up the initial location, or setting a new location after configuration changes and similar. Be aware that careless use of this function can lead to inconsistently replicated data.
Creates a replication origin with the given external name, and returns the internal ID assigned to it. The name must be no longer than 512 bytes.
Deletes a previously-created replication origin, including any associated replay progress.
Looks up a replication origin by name and returns the internal ID. If no such replication origin is found, NULL is returned.
Returns the replay location for the given replication origin. The parameter flush determines whether the corresponding local transaction will be guaranteed to have been flushed to disk or not.
Returns true if a replication origin has been selected in the current session.
Returns the replay location for the replication origin selected in the current session. The parameter flush determines whether the corresponding local transaction will be guaranteed to have been flushed to disk or not.
Cancels the effects of pg_replication_origin_session_setup().
Marks the current session as replaying from the given origin, allowing replay progress to be tracked. Can only be used if no origin is currently selected. Use pg_replication_origin_session_reset to undo.
Cancels the effects of pg_replication_origin_xact_setup().
Marks the current transaction as replaying a transaction that has committed at the given LSN and timestamp. Can only be called when a replication origin has been selected using pg_replication_origin_session_setup.
Advances the current confirmed position of a replication slot named slot_name. The slot will not be moved backwards, and it will not be moved beyond the current insert location. Returns the name of the slot and the actual position that it was advanced to. The updated slot position information is written out at the next checkpoint if any advancing is done. So in the event of a crash, the slot may return to an earlier position. If the specified slot is a logical failover slot then the function will not return until all physical slots specified in synchronized_standby_slots have confirmed WAL receipt.
Creates or updates column-level statistics. Ordinarily, these statistics are collected automatically or updated as a part of VACUUM or ANALYZE, so it's not necessary to call this function. However, it is useful after a restore to enable the optimizer to choose better plans if ANALYZE has not been run yet.
Updates table-level statistics. Ordinarily, these statistics are collected automatically or updated as a part of VACUUM or ANALYZE, so it's not necessary to call this function. However, it is useful after a restore to enable the optimizer to choose better plans if ANALYZE has not been run yet.
Signals the log-file manager to switch to a new output file immediately. This works only when the built-in log collector is running, since otherwise there is no log-file manager subprocess.
Returns an array of the process ID(s) of the sessions that are blocking the server process with the specified process ID from acquiring a safe snapshot, or an empty array if there is no such server process or it is not blocked.
Returns an array of the flags associated with the given GUC, or NULL if it does not exist. The result is an empty array if the GUC exists but there are no flags to show. Only the most useful flags listed in Table 9.80 are exposed.
Converts a size in human-readable format (as returned by pg_size_pretty) into bytes. Valid units are bytes, B, kB, MB, GB, TB, and PB.
Converts a size in bytes into a more easily human-readable format with size units (bytes, kB, MB, GB, TB, or PB as appropriate). Note that the units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes, 1MB is 10242 = 1048576 bytes, and so on.
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_until is a convenience function for when a specific wake-up time is desired. For example:
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_until is a convenience function for when a specific wake-up time is desired. For example:
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_until is a convenience function for when a specific wake-up time is desired. For example:
Returns the set of in-progress transaction IDs contained in a snapshot.
Returns the xmax of a snapshot.
Returns the xmin of a snapshot.
Extracts the sequence number and timeline ID from a WAL file name.
Returns a record containing the file's size, last access time stamp, last modification time stamp, last file status change time stamp (Unix platforms only), file creation time stamp (Windows only), and a flag indicating if it is a directory.
Is statistics object visible in search path?
Forces the server to switch to a new write-ahead log file, which allows the current file to be archived (assuming you are using continuous archiving). The result is the ending write-ahead log location plus 1 within the just-completed write-ahead log file. If there has been no write-ahead log activity since the last write-ahead log switch, pg_switch_wal does nothing and returns the start location of the write-ahead log file currently in use.
Synchronize the logical failover replication slots from the primary server to the standby server. This function can only be executed on the standby server. Temporary synced slots, if any, cannot be used for logical decoding and must be dropped after promotion. See Section 47.2.3 for details. Note that this function is primarily intended for testing and debugging purposes and should be used with caution. Additionally, this function cannot be executed if sync_replication_slots is enabled and the slotsync worker is already running to perform the synchronization of slots.
Is table visible in search path? (This works for all types of relations, including views, materialized views, indexes, sequences and foreign tables.)
Computes the disk space used by the specified table, excluding indexes (but including its TOAST table if any, free space map, and visibility map).
Returns the set of OIDs of databases that have objects stored in the specified tablespace. If this function returns any rows, the tablespace is not empty and cannot be dropped. To identify the specific objects populating the tablespace, you will need to connect to the database(s) identified by pg_tablespace_databases and query their pg_class catalogs.
Returns the file system path that this tablespace is located in.
Computes the total disk space used in the tablespace with the specified name or OID. To use this function, you must have CREATE privilege on the specified tablespace or have privileges of the pg_read_all_stats role, unless it is the default tablespace for the current database.
Terminates the session whose backend process has the specified process ID. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has privileges of pg_signal_backend, however only superusers can terminate superuser backends. As an exception, roles with privileges of pg_signal_autovacuum_worker are permitted to terminate autovacuum worker processes, which are otherwise considered superuser backends.
Computes the total disk space used by the specified table, including all indexes and TOAST data. The result is equivalent to pg_table_size + pg_indexes_size.
Returns the current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger).
Obtains an exclusive session-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
Obtains a shared session-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
Obtains an exclusive transaction-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
Obtains a shared transaction-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
Is text search configuration visible in search path?
Is text search dictionary visible in search path?
Is text search parser visible in search path?
Is text search template visible in search path?
Is type (or domain) visible in search path?
Returns the OID of the data type of the value that is passed to it. This can be helpful for troubleshooting or dynamically constructing SQL queries. The function is declared as returning regtype, which is an OID alias type (see Section 8.19); this means that it is the same as an OID for comparison purposes but displays as a type name.
Is the given transaction ID visible according to this snapshot (that is, was it completed before the snapshot was taken)? Note that this function will not give the correct answer for a subtransaction ID (subxid); see Section 67.3 for details.
Calculates the difference in bytes (lsn1 - lsn2) between two write-ahead log locations. This can be used with pg_stat_replication or some of the functions shown in Table 9.97 to get the replication lag.
Request to pause recovery. A request doesn't mean that recovery stops right away. If you want a guarantee that recovery is actually paused, you need to check for the recovery pause state returned by pg_get_wal_replay_pause_state(). Note that pg_is_wal_replay_paused() returns whether a request is made. While recovery is paused, no further database changes are applied. If hot standby is active, all new queries will see the same consistent snapshot of the database, and no further query conflicts will be generated until recovery is resumed.
Restarts recovery if it was paused.
Returns one information about the contents of a single WAL summary file identified by TLI and starting and ending LSNs. Each row with is_limit_block false indicates that the block identified by the remaining output columns was modified by at least one WAL record within the range of records summarized by this file. Each row with is_limit_block true indicates either that (a) the relation fork was truncated to the length given by relblocknumber within the relevant range of WAL records or (b) that the relation fork was created or dropped within the relevant range of WAL records; in such cases, relblocknumber will be zero.
Converts a write-ahead log location to the name of the WAL file holding that location.
Converts a write-ahead log location to a WAL file name and byte offset within that file.
Returns the commit timestamp of a transaction.
Returns the commit timestamp and replication origin of a transaction.
Reports the commit status of a recent transaction. The result is one of in progress, committed, or aborted, provided that the transaction is recent enough that the system retains the commit status of that transaction. If it is old enough that no references to the transaction survive in the system and the commit status information has been discarded, the result is NULL. Applications might use this function, for example, to determine whether their transaction committed or aborted after the application and database server become disconnected while a COMMIT is in progress. Note that prepared transactions are reported as in progress; applications must check pg_prepared_xacts if they need to determine whether a transaction ID belongs to a prepared transaction.
Converts text to a tsquery, normalizing words according to the specified or default configuration. Any punctuation in the string is ignored (it does not determine query operators). The resulting query matches phrases containing all non-stopwords in the text.
Approximate value of π
Converts text to a tsquery, normalizing words according to the specified or default configuration. Any punctuation in the string is ignored (it does not determine query operators). The resulting query matches documents containing all non-stopwords in the text.
Constructs point from its coordinates.
Converts box to a 4-point polygon.
Converts path to open form.
Returns first starting index of the specified substring within string, or zero if it's not present.
a raised to the power of b
table_to_xml maps the content of the named table, passed as parameter table. The regclass type accepts strings identifying tables using the usual notation, including optional schema qualification and double quotes (see Section 8.19 for details). query_to_xml executes the query whose text is passed as parameter query and maps the result set. cursor_to_xml fetches the indicated number of rows from the cursor specified by the parameter cursor. This variant is recommended if large tables have to be mapped, because the result value is built up in memory by each function.
In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
Produces a representation of the indexable portion of a tsquery. A result that is empty or just T indicates a non-indexable query.
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.
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.
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.
Converts degrees to radians
Computes radius of circle.
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
Computes the union of the non-null input values.
Computes the intersection of the non-null input values.
Computes the smallest range that includes both of the given ranges.
Computes the rank of the hypothetical row, with gaps; that is, the row number of the first row in its peer group.
Returns the number of times the POSIX regular expression pattern matches in the string; see Section 9.7.3.
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.
Checks whether a match of the POSIX regular expression pattern occurs within string; see Section 9.7.3.
Returns substrings within the first match of the POSIX regular expression pattern to the string; see Section 9.7.3.
Returns substrings within the first match of the POSIX regular expression pattern to the string, or substrings within all such matches if the g flag is used; see Section 9.7.3.
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.
Splits string using a POSIX regular expression as the delimiter, producing an array of results; see Section 9.7.3.
Splits string using a POSIX regular expression as the delimiter, producing a set of results; see Section 9.7.3.
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.
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.
Repeats string the specified number of times.
Replaces all occurrences in string of substring from with substring to.
Reverses the order of the characters in the string.
Returns last n characters in the string, or when n is negative, returns all but first |n| characters.
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.
Returns the number of the current row within its partition, counting from 1.
Is row-level security active for the specified table in the context of the current user and current environment?
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.
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.
Removes the longest string containing only characters in characters (a space by default) from the end of string.
Scale of the argument (the number of decimal digits in the fractional part)
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
These functions ignore tables that are not readable by the current user. The database-wide functions additionally ignore schemas that the current user does not have USAGE (lookup) privilege for.
Returns the session user's name.
Sets n'th bit in binary string to newvalue.
Sets n'th byte in binary string to newvalue.
Sets the parameter setting_name to new_value, and returns that value. If is_local is true, the new value will only apply during the current transaction. If you want the new value to apply for the rest of the current session, use false instead. This function corresponds to the SQL command SET.
Sets the netmask length for an inet value. The address part does not change.
Sets the seed for subsequent random() and random_normal() calls; argument must be between -1.0 and 1.0, inclusive
Sets the sequence object's current value, and optionally its is_called flag. The two-parameter form sets the sequence's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value. The value that will be reported by currval is also set to the specified value. In the three-parameter form, is_called can be set to either true or false. true has the same effect as the two-parameter form. If it is set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. Furthermore, the value reported by currval is not changed in this case. For example,
Assigns the specified weight to each element of the vector.
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.
Returns the comment for a shared database object specified by its OID and the name of the containing system catalog. This is just like obj_description except that it is used for retrieving comments on shared objects (that is, databases, roles, and tablespaces). Some system catalogs are global to all databases within each cluster, and the descriptions for objects in them are stored globally as well.
Sign of the argument (-1, 0, or +1)
Sine, argument in radians
Sine, argument in degrees
Hyperbolic sine
Computes slope of a line drawn through the two points.
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.
Square root
Returns true if string starts with prefix.
Current date and time (start of current statement); see Section 9.9.5
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).
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.
Splits the string at occurrences of delimiter and returns the resulting fields as a set of text rows. If delimiter is NULL, each character in the string will become a separate row of the result. 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.
Removes positions and weights from the tsvector.
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.)
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).)
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.
Computes the sum of the non-null input values.
Suppresses do-nothing update operations. See below for details.
Returns the authentication method and the identity (if any) that the user presented during the authentication cycle before they were assigned a database role. It is represented as auth_method:identity or NULL if the user has not been authenticated (for example if Trust authentication has been used).
table_to_xml maps the content of the named table, passed as parameter table. The regclass type accepts strings identifying tables using the usual notation, including optional schema qualification and double quotes (see Section 8.19 for details). query_to_xml executes the query whose text is passed as parameter query and maps the result set. cursor_to_xml fetches the indicated number of rows from the cursor specified by the parameter cursor. This variant is recommended if large tables have to be mapped, because the result value is built up in memory by each function.
In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
Tangent, argument in radians
Tangent, argument in degrees
Hyperbolic tangent
Returns the unabbreviated IP address and netmask length as text. (This has the same result as an explicit cast to text.)
Current date and time (like clock_timestamp, but as a text string); see Section 9.9.5
Converts string to ASCII from another encoding, which may be identified by name or number. If encoding is omitted the database encoding is assumed (which in practice is the only useful case). The conversion consists primarily of dropping accents. Conversion is only supported from LATIN1, LATIN2, LATIN9, and WIN1250 encodings. (See the unaccent module for another, more flexible solution.)
Converts the number to its equivalent two's complement binary representation.
Converts time stamp to string according to the given format.
Converts string to date according to the given format.
Converts the number to its equivalent two's complement hexadecimal representation.
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.
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.
Converts string to numeric according to the given format.
Converts the number to its equivalent two's complement octal representation.
Translates a textual relation name to its OID. A similar result is obtained by casting the string to type regclass (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.
Translates a textual collation name to its OID. A similar result is obtained by casting the string to type regcollation (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.
Translates a textual schema name to its OID. A similar result is obtained by casting the string to type regnamespace (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.
Translates a textual operator name to its OID. A similar result is obtained by casting the string to type regoper (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found or is ambiguous.
Translates a textual operator name (with parameter types) to its OID. A similar result is obtained by casting the string to type regoperator (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.
Translates a textual function or procedure name to its OID. A similar result is obtained by casting the string to type regproc (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found or is ambiguous.
Translates a textual function or procedure name (with argument types) to its OID. A similar result is obtained by casting the string to type regprocedure (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.
Translates a textual role name to its OID. A similar result is obtained by casting the string to type regrole (see Section 8.19); however, this function will return NULL rather than throwing an error if the name is not found.
Parses a string of text, extracts a potential type name from it, and translates that name into a type OID. A syntax error in the string will result in an error; but if the string is a syntactically valid type name that happens not to be found in the catalogs, the result is NULL. A similar result is obtained by casting the string to type regtype (see Section 8.19), except that that will throw error for name not found.
Parses a string of text, extracts a potential type name from it, and translates its type modifier, if any. A syntax error in the string will result in an error; but if the string is a syntactically valid type name that happens not to be found in the catalogs, the result is NULL. The result is -1 if no type modifier is present.
Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.33.)
Converts text to a tsquery, normalizing words according to the specified or default configuration. The words must be combined by valid tsquery operators.
Converts text to a tsvector, normalizing words according to the specified or default configuration. Position information is included in the result.
Current date and time (start of current transaction); see Section 9.9.5
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.
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.
Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.
Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes
Truncates to integer (towards zero)
Extracts and normalizes tokens from the document according to the specified or default text search configuration, and returns information about how each token was processed. See Section 12.8.1 for details.
Removes any occurrence of the given lexeme from the vector. The lexeme string is treated as a lexeme as-is, without further processing.
Selects only elements with the given weights from the vector.
Displays, in an abbreviated form, the match(es) for the query in the document, which must be raw text not a tsvector. Words in the document are normalized according to the specified or default configuration before matching to the query. Use of this function is discussed in Section 12.3.4, which also describes the available options.
Returns an array of replacement lexemes if the input token is known to the dictionary, or an empty array if the token is known to the dictionary but it is a stop word, or NULL if it is not a known word. See Section 12.8.3 for details.
Extracts tokens from the document using the named parser. See Section 12.8.2 for details.
Computes a score showing how well the vector matches the query. See Section 12.3.3 for details.
Computes a score showing how well the vector matches the query, using a cover density algorithm. See Section 12.3.3 for details.
Replaces occurrences of target with substitute within the query. See Section 12.4.2.1 for details.
Executes the sqlquery, which must return a single tsvector column, and returns statistics about each distinct lexeme contained in the data. See Section 12.4.4 for details.
Returns a table that describes each type of token the named parser can recognize. See Section 12.8.2 for details.
Constructs a phrase query that searches for matches of query1 and query2 at successive lexemes (same as <-> operator).
Converts a tsvector to an array of lexemes.
Automatically updates a tsvector column from associated plain-text document column(s). The text search configuration to use is specified by name as a trigger argument. See Section 12.4.3 for details.
Automatically updates a tsvector column from associated plain-text document column(s). The text search configuration to use is taken from a regconfig column of the table. See Section 12.4.3 for details.
See pg_current_xact_id().
See pg_current_xact_id_if_assigned().
See pg_current_snapshot().
See pg_snapshot_xip().
See pg_snapshot_xmax().
See pg_snapshot_xmin().
See pg_xact_status().
See pg_visible_in_snapshot().
Returns true if all characters in the string are assigned Unicode codepoints; false otherwise. This function can only be used when the server encoding is UTF8.
Returns a string representing the version of Unicode used by PostgreSQL.
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.
Expands a tsvector into a set of rows, one per lexeme.
Converts the string to all upper case, according to the rules of the database's locale.
Is the range's upper bound inclusive?
Does the range have no upper bound? (An upper bound of Infinity returns false.)
This is equivalent to current_user.
Extracts a timestamp with time zone from a UUID of version 1 or 7. For other versions, this function returns null. Note that the extracted timestamp is not necessarily exactly equal to the time the UUID was generated; this depends on the implementation that generated the UUID.
Extracts the version from a UUID of one of the variants described by RFC 9562. For other variants, this function returns null. For example, for a UUID generated by gen_random_uuid(), this function will return 4.
Generates a version 4 (random) UUID
Generates a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp with millisecond precision + sub-millisecond timestamp + random. The optional parameter shift will shift the computed timestamp by the given interval. Infinite interval values are not accepted. The shifted timestamp must fall within the range supported by UUID version 7's 48-bit millisecond timestamp field: from 1970-01-01 00:00:00 UTC to approximately year 10889. An error is raised if the resulting timestamp is outside this range.
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.
Returns a string describing the PostgreSQL server's version. You can also get this information from server_version, or for a machine-readable version use server_version_num. Software developers should use server_version_num (available since 8.2) or PQserverVersion instead of parsing the text version.
Converts text to a tsquery, normalizing words according to the specified or default configuration. Quoted word sequences are converted to phrase tests. The word “or” is understood as producing an OR operator, and a dash produces a NOT operator; other punctuation is ignored. This approximates the behavior of some common web search tools.
Computes horizontal size of box.
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.
These functions check whether a text string represents well-formed XML, returning a Boolean result. xml_is_well_formed_document checks for a well-formed document, while xml_is_well_formed_content checks for well-formed content. xml_is_well_formed does the former if the xmloption configuration parameter is set to DOCUMENT, or the latter if it is set to CONTENT. This means that xml_is_well_formed is useful for seeing whether a simple cast to type xml will succeed, whereas the other two functions are useful for seeing whether the corresponding variants of XMLPARSE will succeed.
These functions check whether a text string represents well-formed XML, returning a Boolean result. xml_is_well_formed_document checks for a well-formed document, while xml_is_well_formed_content checks for well-formed content. xml_is_well_formed does the former if the xmloption configuration parameter is set to DOCUMENT, or the latter if it is set to CONTENT. This means that xml_is_well_formed is useful for seeing whether a simple cast to type xml will succeed, whereas the other two functions are useful for seeing whether the corresponding variants of XMLPARSE will succeed.
These functions check whether a text string represents well-formed XML, returning a Boolean result. xml_is_well_formed_document checks for a well-formed document, while xml_is_well_formed_content checks for well-formed content. xml_is_well_formed does the former if the xmloption configuration parameter is set to DOCUMENT, or the latter if it is set to CONTENT. This means that xml_is_well_formed is useful for seeing whether a simple cast to type xml will succeed, whereas the other two functions are useful for seeing whether the corresponding variants of XMLPARSE will succeed.
The function xmlagg is, unlike the other functions described here, an aggregate function. It concatenates the input values to the aggregate function call, much like xmlconcat does, except that concatenation occurs across rows rather than across expressions in a single row. See Section 9.21 for additional information about aggregate functions.
The function xmlcomment creates an XML value containing an XML comment with the specified text as content. The text cannot contain “--” or end with a “-”, otherwise the resulting construct would not be a valid XML comment. If the argument is null, the result is null.
The function xmlconcat concatenates a list of individual XML values to create a single value containing an XML content fragment. Null values are omitted; the result is only null if there are no nonnull arguments.
The xmlelement expression produces an XML element with the given name, attributes, and content. The name and attname items shown in the syntax are simple identifiers, not values. The attvalue and content items are expressions, which can yield any PostgreSQL data type. The argument(s) within XMLATTRIBUTES generate attributes of the XML element; the content value(s) are concatenated to form its content.
The xmlforest expression produces an XML forest (sequence) of elements using the given names and content. As for xmlelement, each name must be a simple identifier, while the content expressions can have any data type.
The xmlpi expression creates an XML processing instruction. As for xmlelement, the name must be a simple identifier, while the content expression can have any data type. The content, if present, must not contain the character sequence ?>.
The xmlroot expression alters the properties of the root node of an XML value. If a version is specified, it replaces the value in the root node's version declaration; if a standalone setting is specified, it replaces the value in the root node's standalone declaration.
The function xmltext returns an XML value with a single text node containing the input argument as its content. Predefined entities like ampersand (&), left and right angle brackets (< >), and quotation marks ("") are escaped.
The function xpath evaluates the XPath 1.0 expression xpath (given as text) against the XML value xml. It returns an array of XML values corresponding to the node-set produced by the XPath expression. If the XPath expression returns a scalar value rather than a node-set, a single-element array is returned.
The function xpath_exists is a specialized form of the xpath function. Instead of returning the individual XML values that satisfy the XPath 1.0 expression, this function returns a Boolean indicating whether the query was satisfied or not (specifically, whether it produced any value other than an empty node-set). This function is equivalent to the XMLEXISTS predicate, except that it also offers support for a namespace mapping argument.