Wiki / Functions
Functions
Compare versionsBuilt-in function signatures, examples, and changes across major versions.
Reading PG 18current·27 versions documented, 7.1 to 20
-
any_value ( anyelement ) → same as input type
Returns an arbitrary value from the non-null input values.
-
array_agg ( anynonarray ORDER BY input_sort_columns ) → anyarray
Collects all the input values, including nulls, into an array.
-
avg ( smallint ) → numeric
Computes the average (arithmetic mean) of all the non-null input values.
-
bit_and ( smallint ) → smallint
Computes the bitwise AND of all non-null input values.
-
bit_or ( smallint ) → smallint
Computes the bitwise OR of all non-null input values.
-
bit_xor ( smallint ) → smallint
Computes the bitwise exclusive OR of all non-null input values. Can be useful as a checksum for an unordered set of values.
-
bool_and ( boolean ) → boolean
Returns true if all non-null input values are true, otherwise false.
-
bool_or ( boolean ) → boolean
Returns true if any non-null input value is true, otherwise false.
-
corr ( Y double precision, X double precision ) → double precision
Computes the correlation coefficient.
-
count ( * ) → bigint
Computes the number of input rows.
-
covar_pop ( Y double precision, X double precision ) → double precision
Computes the population covariance.
-
covar_samp ( Y double precision, X double precision ) → double precision
Computes the sample covariance.
-
cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision
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.
-
dense_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint
Computes the rank of the hypothetical row, without gaps; this function effectively counts peer groups.
-
every ( boolean ) → boolean
This is the SQL standard's equivalent to bool_and.
-
GROUPING ( group_by_expression(s) ) → integer
Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is include…
-
json_agg ( anyelement ORDER BY input_sort_columns ) → json
Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.
-
json_agg_strict ( anyelement ) → json
Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.
-
json_arrayagg ( [ value_expression ] [ ORDER BY sort_expression ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
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 …
-
json_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → json
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot.
-
json_object_agg_strict ( key "any", value "any" ) → json
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped,
-
json_object_agg_unique ( key "any", value "any" ) → json
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot. If there is a duplicate key an error is thrown.
-
json_object_agg_unique_strict ( key "any", value "any" ) → json
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…
-
json_objectagg ( [ { key_expression { VALUE | ':' } value_expression } ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
Behaves like json_object, but as an aggregate function, so it only takes one key_expression and one value_expression parameter.
-
jsonb_agg ( anyelement ORDER BY input_sort_columns ) → jsonb
Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.
-
jsonb_agg_strict ( anyelement ) → jsonb
Collects all the input values, skipping nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.
-
jsonb_object_agg ( key "any", value "any" ORDER BY input_sort_columns ) → jsonb
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot.
-
jsonb_object_agg_strict ( key "any", value "any" ) → jsonb
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped,
-
jsonb_object_agg_unique ( key "any", value "any" ) → jsonb
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but keys cannot. If there is a duplicate key an error is thrown.
-
jsonb_object_agg_unique_strict ( key "any", value "any" ) → jsonb
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. The key can not be null. If the value is null then the entry is skipped. If there is a duplicate key an…
-
max ( see text ) → same as input type
Computes the maximum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as bytea, inet, interval, money, oid, pg_lsn, tid, xid8, and also arrays and composite types containing sortable data types.
-
min ( see text ) → same as input type
Computes the minimum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as bytea, inet, interval, money, oid, pg_lsn, tid, xid8, and also arrays and composite types containing sortable data types.
-
mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement
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.
-
percent_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision
Computes the relative rank of the hypothetical row, that is (rank - 1) / (total rows - 1). The value thus ranges from 0 to 1 inclusive.
-
percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision
Computes the continuous percentile, a value corresponding to the specified fraction within the ordered set of aggregated argument values. This will interpolate between adjacent input items if needed.
-
percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement
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.
-
range_agg ( value anyrange ) → anymultirange
Computes the union of the non-null input values.
-
range_intersect_agg ( value anyrange ) → anyrange
Computes the intersection of the non-null input values.
-
rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint
Computes the rank of the hypothetical row, with gaps; that is, the row number of the first row in its peer group.
-
regr_avgx ( Y double precision, X double precision ) → double precision
Computes the average of the independent variable, sum(X)/N.
-
regr_avgy ( Y double precision, X double precision ) → double precision
Computes the average of the dependent variable, sum(Y)/N.
-
regr_count ( Y double precision, X double precision ) → bigint
Computes the number of rows in which both inputs are non-null.
-
regr_intercept ( Y double precision, X double precision ) → double precision
Computes the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.
-
regr_r2 ( Y double precision, X double precision ) → double precision
Computes the square of the correlation coefficient.
-
regr_slope ( Y double precision, X double precision ) → double precision
Computes the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.
-
regr_sxx ( Y double precision, X double precision ) → double precision
Computes the “sum of squares” of the independent variable, sum(X^2) - sum(X)^2/N.
-
regr_sxy ( Y double precision, X double precision ) → double precision
Computes the “sum of products” of independent times dependent variables, sum(X*Y) - sum(X) * sum(Y)/N.
-
regr_syy ( Y double precision, X double precision ) → double precision
Computes the “sum of squares” of the dependent variable, sum(Y^2) - sum(Y)^2/N.
-
stddev ( numeric_type ) → double precision for real or double precision, otherwise numeric
This is a historical alias for stddev_samp.
-
stddev_pop ( numeric_type ) → double precision for real or double precision, otherwise numeric
Computes the population standard deviation of the input values.
-
stddev_samp ( numeric_type ) → double precision for real or double precision, otherwise numeric
Computes the sample standard deviation of the input values.
-
string_agg ( value text, delimiter text ) → text
Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it's not null).
-
sum ( smallint ) → bigint
Computes the sum of the non-null input values.
-
var_pop ( numeric_type ) → double precision for real or double precision, otherwise numeric
Computes the population variance of the input values (square of the population standard deviation).
-
var_samp ( numeric_type ) → double precision for real or double precision, otherwise numeric
Computes the sample variance of the input values (square of the sample standard deviation).
-
variance ( numeric_type ) → double precision for real or double precision, otherwise numeric
This is a historical alias for var_samp.
-
array_append ( anycompatiblearray, anycompatible ) → anycompatiblearray
Appends an element to the end of an array (same as the anycompatiblearray || anycompatible operator).
-
array_cat ( anycompatiblearray, anycompatiblearray ) → anycompatiblearray
Concatenates two arrays (same as the anycompatiblearray || anycompatiblearray operator).
-
array_dims ( anyarray ) → text
Returns a text representation of the array's dimensions.
-
array_fill ( anyelement, integer[] [, integer[] ] ) → anyarray
Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all 1).
-
array_length ( anyarray, integer ) → integer
Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)
-
array_lower ( anyarray, integer ) → integer
Returns the lower bound of the requested array dimension.
-
array_ndims ( anyarray ) → integer
Returns the number of dimensions of the array.
-
array_position ( anycompatiblearray, anycompatible [, integer ] ) → integer
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…
-
array_positions ( anycompatiblearray, anycompatible ) → integer[]
Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to search for NULL. NU…
-
array_prepend ( anycompatible, anycompatiblearray ) → anycompatiblearray
Prepends an element to the beginning of an array (same as the anycompatible || anycompatiblearray operator).
-
array_remove ( anycompatiblearray, anycompatible ) → anycompatiblearray
Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.
-
array_replace ( anycompatiblearray, anycompatible, anycompatible ) → anycompatiblearray
Replaces each array element equal to the second argument with the third argument.
-
array_reverse ( anyarray ) → anyarray
Reverses the first dimension of the array.
-
array_sample ( array anyarray, n integer ) → anyarray
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.
-
array_shuffle ( anyarray ) → anyarray
Randomly shuffles the first dimension of the array.
-
array_sort ( array anyarray [, descending boolean [, nulls_first boolean ]] ) → anyarray
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 …
-
array_to_string ( array anyarray, delimiter text [, null_string text ] ) → text
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 …
-
array_upper ( anyarray, integer ) → integer
Returns the upper bound of the requested array dimension.
-
cardinality ( anyarray ) → integer
Returns the total number of elements in the array, or 0 if the array is empty.
-
trim_array ( array anyarray, n integer ) → anyarray
Trims an array by removing the last n elements. If the array is multidimensional, only the first dimension is trimmed.
-
bit_count ( bytes bytea ) → bigint
Returns the number of bits set in the binary string (also known as “popcount”).
-
convert ( bytes bytea, src_encoding name, dest_encoding name ) → bytea
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).
-
convert_from ( bytes bytea, src_encoding name ) → text
Converts a binary string representing text in encoding src_encoding to text in the database encoding (see Section 23.3.4 for available conversions).
-
convert_to ( string text, dest_encoding name ) → bytea
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).
-
crc32 ( bytea ) → bigint
Computes the CRC-32 value of the binary string.
-
crc32c ( bytea ) → bigint
Computes the CRC-32C value of the binary string.
-
decode ( string text, format text ) → bytea
Decodes binary data from a textual representation; supported format values are the same as for encode.
-
encode ( bytes bytea, format text ) → text
Encodes binary data into a textual representation; supported format values are: base64, escape, hex.
-
get_bit ( bytes bytea, n bigint ) → integer
Extracts n'th bit from binary string.
-
get_byte ( bytes bytea, n integer ) → integer
Extracts n'th byte from binary string.
-
set_bit ( bytes bytea, n bigint, newvalue integer ) → bytea
Sets n'th bit in binary string to newvalue.
-
set_byte ( bytes bytea, n integer, newvalue integer ) → bytea
Sets n'th byte in binary string to newvalue.
-
sha224 ( bytea ) → bytea
Computes the SHA-224 hash of the binary string.
-
sha256 ( bytea ) → bytea
Computes the SHA-256 hash of the binary string.
-
sha384 ( bytea ) → bytea
Computes the SHA-384 hash of the binary string.
-
sha512 ( bytea ) → bytea
Computes the SHA-512 hash of the binary string.
-
error_on_null ( anyelement ) → anyelement
Checks if the input is the null value, generating an error if so; otherwise, returns the input.
-
num_nonnulls ( VARIADIC "any" ) → integer
Returns the number of non-null arguments.
-
num_nulls ( VARIADIC "any" ) → integer
Returns the number of null arguments.
-
COALESCE(value [, ...])
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:
-
GREATEST(value [, ...])
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).
-
LEAST(value [, ...])
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).
-
NULLIF(value1, value2)
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:
-
to_char ( timestamp, text ) → text
Converts time stamp to string according to the given format.
-
to_date ( text, text ) → date
Converts string to date according to the given format.
-
to_number ( text, text ) → numeric
Converts string to numeric according to the given format.
-
to_timestamp ( text, text ) → timestamp with time zone
Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.33.)
-
age ( timestamp, timestamp ) → interval
Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days
-
clock_timestamp ( ) → timestamp with time zone
Current date and time (changes during statement execution); see Section 9.9.5
-
current_date → date
Current date; see Section 9.9.5
-
current_time → time with time zone
Current time of day; see Section 9.9.5
-
current_timestamp → timestamp with time zone
Current date and time (start of current transaction); see Section 9.9.5
-
date_add ( timestamp with time zone, interval [, text ] ) → timestamp with time zone
Add an interval to a timestamp with time zone, computing times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted. The form with two arguments is equival…
-
date_bin ( interval, timestamp, timestamp ) → timestamp
Bin input into specified interval aligned with specified origin; see Section 9.9.3
-
date_part ( text, timestamp ) → double precision
Get timestamp subfield (equivalent to extract); see Section 9.9.1
-
date_subtract ( timestamp with time zone, interval [, text ] ) → timestamp with time zone
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 …
-
date_trunc ( text, timestamp ) → timestamp
Truncate to specified precision; see Section 9.9.2
-
extract ( field FROM timestamp ) → numeric
Get timestamp subfield; see Section 9.9.1
-
isfinite ( date ) → boolean
Test for finite date (not +/-infinity)
-
justify_days ( interval ) → interval
Adjust interval, converting 30-day time periods to months
-
justify_hours ( interval ) → interval
Adjust interval, converting 24-hour time periods to days
-
justify_interval ( interval ) → interval
Adjust interval using justify_days and justify_hours, with additional sign adjustments
-
localtime → time
Current time of day; see Section 9.9.5
-
localtimestamp → timestamp
Current date and time (start of current transaction); see Section 9.9.5
-
make_date ( year int, month int, day int ) → date
Create date from year, month and day fields (negative years signify BC)
-
make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval
Create interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zero
-
make_time ( hour int, min int, sec double precision ) → time
Create time from hour, minute and seconds fields
-
make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp
Create timestamp from year, month, day, hour, minute and seconds fields (negative years signify BC)
-
make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) → timestamp with time zone
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
-
now ( ) → timestamp with time zone
Current date and time (start of current transaction); see Section 9.9.5
-
pg_sleep ( double precision )
pg_sleep makes the current session's process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_unt…
-
pg_sleep_for ( interval )
pg_sleep makes the current session's process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_unt…
-
pg_sleep_until ( timestamp with time zone )
pg_sleep makes the current session's process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_unt…
-
statement_timestamp ( ) → timestamp with time zone
Current date and time (start of current statement); see Section 9.9.5
-
timeofday ( ) → text
Current date and time (like clock_timestamp, but as a text string); see Section 9.9.5
-
timestamp ( date ) → timestamp
date to timestamp
-
transaction_timestamp ( ) → timestamp with time zone
Current date and time (start of current transaction); see Section 9.9.5
-
enum_first ( anyenum ) → anyenum
Returns the first value of the input enum type.
-
enum_last ( anyenum ) → anyenum
Returns the last value of the input enum type.
-
enum_range ( anyenum ) → anyarray
Returns all values of the input enum type in an ordered array.
-
pg_event_trigger_ddl_commands () → setof record
pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger. If called in any other context, an error is raised. pg_event_trigger_ddl_commands retu…
-
pg_event_trigger_dropped_objects () → setof record
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:
-
pg_event_trigger_table_rewrite_oid () → oid
Returns the OID of the table about to be rewritten.
-
pg_event_trigger_table_rewrite_reason () → integer
Returns a code explaining the reason(s) for rewriting. The value is a bitmap built from the following values: 1 (the table has changed its persistence), 2 (default value of a column has changed), 4 (a column has a new data type) and 8 (the table acc…
-
area ( geometric_type ) → double precision
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.
-
bound_box ( box, box ) → box
Computes bounding box of two boxes.
-
box ( circle ) → box
Computes box inscribed within the circle.
-
box_intersect ( box, box ) → box
intersection box
-
center ( geometric_type ) → point
Computes center point. Available for box, circle.
-
circle ( box ) → circle
Computes smallest circle enclosing box.
-
diagonal ( box ) → lseg
Extracts box's diagonal as a line segment (same as lseg(box)).
-
diameter ( circle ) → double precision
Computes diameter of circle.
-
height ( box ) → double precision
Computes vertical size of box.
-
isclosed ( path ) → boolean
Is path closed?
-
isopen ( path ) → boolean
Is path open?
-
line ( point, point ) → line
Converts two points to the line through them.
-
lseg ( box ) → lseg
Extracts box's diagonal as a line segment.
-
npoint ( path ) → integer
number of points
-
npoints ( geometric_type ) → integer
Returns the number of points. Available for path, polygon.
-
path ( polygon ) → path
Converts polygon to a closed path with the same list of points.
-
pclose ( path ) → path
Converts path to closed form.
-
point ( double precision, double precision ) → point
Constructs point from its coordinates.
-
polygon ( box ) → polygon
Converts box to a 4-point polygon.
-
popen ( path ) → path
Converts path to open form.
-
radius ( circle ) → double precision
Computes radius of circle.
-
slope ( point, point ) → double precision
Computes slope of a line drawn through the two points.
-
width ( box ) → double precision
Computes horizontal size of box.
-
array_to_json ( anyarray [, boolean ] ) → json
Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optional boolean parameter is true.
-
json ( expression [ FORMAT JSON [ ENCODING UTF8 ]] [ { WITH | WITHOUT } UNIQUE [ KEYS ]] ) → json
Converts a given expression specified as text or bytea string (in UTF8 encoding) into a JSON value. If expression is NULL, an SQL null value is returned. If WITH UNIQUE is specified, the expression must not contain any duplicate object keys.
-
json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
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 …
-
json_array_elements ( json ) → setof json
Expands the top-level JSON array into a set of JSON values.
-
json_array_elements_text ( json ) → setof text
Expands the top-level JSON array into a set of text values.
-
json_array_length ( json ) → integer
Returns the number of elements in the top-level JSON array.
-
json_build_array ( VARIADIC "any" ) → json
Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. Each argument is converted as per to_json or to_jsonb.
-
json_build_object ( VARIADIC "any" ) → json
Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb.
-
json_each ( json ) → setof record ( key text, value json )
Expands the top-level JSON object into a set of key/value pairs.
-
json_each_text ( json ) → setof record ( key text, value text )
Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text.
-
JSON_EXISTS ( context_item, path_expression [PASSING { value AS varname } [, ...]] [{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR]) → boolean
Returns true if the SQL/JSON path_expression applied to the context_item yields any items, false otherwise.
-
json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json
Extracts JSON sub-object at the specified path. (This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.)
-
json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text
Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)
-
json_object ( [ { key_expression { VALUE | ':' } value_expression [ FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
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…
-
json_object_keys ( json ) → setof text
Returns the set of keys in the top-level JSON object.
-
json_populate_record ( base anyelement, from_json json ) → anyelement
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. (…
-
json_populate_recordset ( base anyelement, from_json json ) → setof anyelement
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.
-
JSON_QUERY ( context_item, path_expression [PASSING { value AS varname } [, ...]] [RETURNING data_type [FORMAT JSON [ENCODING UTF8] ] ] [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ARRAY] WRAPPER] [ { KEEP | OMIT } QUOTES [ON SCALAR STRING] ] [ { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON EMPTY] [ { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON ERROR]) → jsonb
Returns the result of applying the SQL/JSON path_expression to the context_item.
-
json_scalar ( expression )
Converts a given SQL scalar value into a JSON scalar value. If the input is NULL, an SQL null is returned. If the input is number or a boolean value, a corresponding JSON number or boolean value is returned. For any other value, a JSON string is ret…
-
json_serialize ( expression [ FORMAT JSON [ ENCODING UTF8 ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )
Converts an SQL/JSON expression into a character or binary string. The expression can be of any JSON type, any character string type, or bytea in UTF8 encoding. The returned type used in RETURNING can be any character string type or bytea. The defau…
-
json_strip_nulls ( target json [,strip_in_arrays boolean ] ) → json
Deletes all object fields that have null values from the given JSON value, recursively. If strip_in_arrays is true (the default is false), null array elements are also stripped. Otherwise they are not stripped. Bare null values are never stripped.
-
JSON_TABLE ( context_item, path_expression [ AS json_path_name] [ PASSING { value AS varname } [, ...] ] COLUMNS ( json_table_column [, ...] ) [ PLAN ( json_table_plan ) | PLAN DEFAULT ( { OUTER | INNER } [ , { CROSS | UNION } ] | { CROSS | UNION } [ , { OUTER | INNER } ] ) ] [ { ERROR | EMPTY [ARRAY]} ON ERROR] )
Each syntax element is described below in more detail.
-
json_to_record ( json ) → record
Expands the top-level JSON object to a row having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) The output record is fill…
-
json_to_recordset ( json ) → setof record
Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) Each el…
-
json_typeof ( json ) → text
Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null. (The null result should not be confused with an SQL NULL; see the examples.)
-
JSON_VALUE ( context_item, path_expression [PASSING { value AS varname } [, ...]] [RETURNING data_type] [ { ERROR | NULL | DEFAULT expression } ON EMPTY] [ { ERROR | NULL | DEFAULT expression } ON ERROR]) → text
Returns the result of applying the SQL/JSON path_expression to the context_item.
-
jsonb_array_elements ( jsonb ) → setof jsonb
Expands the top-level JSON array into a set of JSON values.
-
jsonb_array_elements_text ( jsonb ) → setof text
Expands the top-level JSON array into a set of text values.
-
jsonb_array_length ( jsonb ) → integer
Returns the number of elements in the top-level JSON array.
-
jsonb_build_array ( VARIADIC "any" ) → jsonb
Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list. Each argument is converted as per to_json or to_jsonb.
-
jsonb_build_object ( VARIADIC "any" ) → 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.
-
jsonb_each ( jsonb ) → setof record ( key text, value jsonb )
Expands the top-level JSON object into a set of key/value pairs.
-
jsonb_each_text ( jsonb ) → setof record ( key text, value text )
Expands the top-level JSON object into a set of key/value pairs. The returned values will be of type text.
-
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb
Extracts JSON sub-object at the specified path. (This is functionally equivalent to the #> operator, but writing the path out as a variadic list can be more convenient in some cases.)
-
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text
Extracts JSON sub-object at the specified path as text. (This is functionally equivalent to the #>> operator.)
-
jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb
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 …
-
jsonb_object ( text[] ) → jsonb
Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elem…
-
jsonb_object_keys ( jsonb ) → setof text
Returns the set of keys in the top-level JSON object.
-
jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
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…
-
jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…
-
jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
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…
-
jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…
-
jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb
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…
-
jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
Returns all JSON items returned by the JSON path for the specified JSON value, as a JSON array. The parameters are the same as for jsonb_path_query.
-
jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…
-
jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
Returns the first JSON item returned by the JSON path for the specified JSON value, or NULL if there are no results. The parameters are the same as for jsonb_path_query.
-
jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…
-
jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb
These functions act like their counterparts described above without the _tz suffix, except that these functions support comparisons of date/time values that require timezone-aware conversions. The example below requires interpretation of the date-on…
-
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement
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. (…
-
jsonb_populate_record_valid ( base anyelement, from_json json ) → boolean
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.
-
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement
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.
-
jsonb_pretty ( jsonb ) → text
Converts the given JSON value to pretty-printed, indented text.
-
jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb
Returns target with the item designated by path replaced by new_value, or with new_value added if create_if_missing is true (which is the default) and the item designated by path does not exist. All earlier steps in the path must exist, or the targe…
-
jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb
If new_value is not NULL, behaves identically to jsonb_set. Otherwise behaves according to the value of null_value_treatment which must be one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target'. The default is 'use_json_null'.
-
jsonb_strip_nulls ( target jsonb [,strip_in_arrays boolean ] ) → jsonb
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.
-
jsonb_to_record ( jsonb ) → record
Expands the top-level JSON object to a row having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) The output record is fill…
-
jsonb_to_recordset ( jsonb ) → setof record
Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause. (As with all functions returning record, the calling query must explicitly define the structure of the record with an AS clause.) Each el…
-
jsonb_typeof ( jsonb ) → text
Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null. (The null result should not be confused with an SQL NULL; see the examples.)
-
row_to_json ( record [, boolean ] ) → json
Converts an SQL composite value to a JSON object. The behavior is the same as to_json except that line feeds will be added between top-level elements if the optional boolean parameter is true.
-
to_json ( anyelement ) → json
Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function…
-
to_jsonb ( anyelement ) → jsonb
Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function…
-
abs ( numeric_type ) → numeric_type
Absolute value
-
acos ( double precision ) → double precision
Inverse cosine, result in radians
-
acosd ( double precision ) → double precision
Inverse cosine, result in degrees
-
acosh ( double precision ) → double precision
Inverse hyperbolic cosine
-
asin ( double precision ) → double precision
Inverse sine, result in radians
-
asind ( double precision ) → double precision
Inverse sine, result in degrees
-
asinh ( double precision ) → double precision
Inverse hyperbolic sine
-
atan ( double precision ) → double precision
Inverse tangent, result in radians
-
atan2 ( y double precision, x double precision ) → double precision
Inverse tangent of y/x, result in radians
-
atan2d ( y double precision, x double precision ) → double precision
Inverse tangent of y/x, result in degrees
-
atand ( double precision ) → double precision
Inverse tangent, result in degrees
-
atanh ( double precision ) → double precision
Inverse hyperbolic tangent
-
cbrt ( double precision ) → double precision
Cube root
-
ceil ( numeric ) → numeric
Nearest integer greater than or equal to argument
-
ceiling ( numeric ) → numeric
Nearest integer greater than or equal to argument (same as ceil)
-
cos ( double precision ) → double precision
Cosine, argument in radians
-
cosd ( double precision ) → double precision
Cosine, argument in degrees
-
cosh ( double precision ) → double precision
Hyperbolic cosine
-
cot ( double precision ) → double precision
Cotangent, argument in radians
-
cotd ( double precision ) → double precision
Cotangent, argument in degrees
-
degrees ( double precision ) → double precision
Converts radians to degrees
-
div ( y numeric, x numeric ) → numeric
Integer quotient of y/x (truncates towards zero)
-
erf ( double precision ) → double precision
Error function
-
erfc ( double precision ) → double precision
Complementary error function (1 - erf(x), without loss of precision for large inputs)
-
exp ( numeric ) → numeric
Exponential (e raised to the given power)
-
factorial ( bigint ) → numeric
Factorial
-
floor ( numeric ) → numeric
Nearest integer less than or equal to argument
-
gamma ( double precision ) → double precision
Gamma function
-
gcd ( numeric_type, numeric_type ) → numeric_type
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
-
lcm ( numeric_type, numeric_type ) → numeric_type
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
-
lgamma ( double precision ) → double precision
Natural logarithm of the absolute value of the gamma function
-
ln ( numeric ) → numeric
Natural logarithm
-
log ( numeric ) → numeric
Base 10 logarithm
-
log10 ( numeric ) → numeric
Base 10 logarithm (same as log)
-
min_scale ( numeric ) → integer
Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely
-
mod ( y numeric_type, x numeric_type ) → numeric_type
Remainder of y/x; available for smallint, integer, bigint, and numeric
-
pi ( ) → double precision
Approximate value of π
-
pow ( a dp, b dp ) → dp
a raised to the power of b
-
power ( a numeric, b numeric ) → numeric
a raised to the power of b
-
radians ( double precision ) → double precision
Converts degrees to radians
-
random ( ) → double precision
Returns a random value in the range 0.0 <= x < 1.0
-
random_normal ( [ mean double precision [, stddev double precision ]] ) → double precision
Returns a random value from the normal distribution with the given parameters; mean defaults to 0.0 and stddev defaults to 1.0
-
round ( numeric ) → numeric
Rounds to nearest integer. For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule.
-
scale ( numeric ) → integer
Scale of the argument (the number of decimal digits in the fractional part)
-
setseed ( double precision ) → void
Sets the seed for subsequent random() and random_normal() calls; argument must be between -1.0 and 1.0, inclusive
-
sign ( numeric ) → numeric
Sign of the argument (-1, 0, or +1)
-
sin ( double precision ) → double precision
Sine, argument in radians
-
sind ( double precision ) → double precision
Sine, argument in degrees
-
sinh ( double precision ) → double precision
Hyperbolic sine
-
sqrt ( numeric ) → numeric
Square root
-
tan ( double precision ) → double precision
Tangent, argument in radians
-
tand ( double precision ) → double precision
Tangent, argument in degrees
-
tanh ( double precision ) → double precision
Hyperbolic tangent
-
trim_scale ( numeric ) → numeric
Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes
-
trunc ( numeric ) → numeric
Truncates to integer (towards zero)
-
width_bucket ( operand numeric, low numeric, high numeric, count integer ) → integer
Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. The buckets have inclusive lower bounds and exclusive upper bounds. Returns 0 for an input less than low, or coun…
-
merge_action ( ) → text
Returns the merge action command executed for the current row. This will be 'INSERT', 'UPDATE', or 'DELETE'.
-
abbrev ( inet ) → text
Creates an abbreviated display format as text. (The result is the same as the inet output function produces; it is “abbreviated” only in comparison to the result of an explicit cast to text, which for historical reasons will never suppress the netma…
-
broadcast ( inet ) → inet
Computes the broadcast address for the address's network.
-
family ( inet ) → integer
Returns the address's family: 4 for IPv4, 6 for IPv6.
-
host ( inet ) → text
Returns the IP address as text, ignoring the netmask.
-
hostmask ( inet ) → inet
Computes the host mask for the address's network.
-
inet_merge ( inet, inet ) → cidr
Computes the smallest network that includes both of the given networks.
-
inet_same_family ( inet, inet ) → boolean
Tests whether the addresses belong to the same IP family.
-
macaddr8_set7bit ( macaddr8 ) → macaddr8
Sets the 7th bit of the address to one, creating what is known as modified EUI-64, for inclusion in an IPv6 address.
-
masklen ( inet ) → integer
Returns the netmask length in bits.
-
netmask ( inet ) → inet
Computes the network mask for the address's network.
-
network ( inet ) → cidr
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.)
-
set_masklen ( inet, integer ) → inet
Sets the netmask length for an inet value. The address part does not change.
-
text ( inet ) → text
Returns the unabbreviated IP address and netmask length as text. (This has the same result as an explicit cast to text.)
-
isempty ( anyrange ) → boolean
Is the range empty?
-
lower_inc ( anyrange ) → boolean
Is the range's lower bound inclusive?
-
lower_inf ( anyrange ) → boolean
Does the range have no lower bound? (A lower bound of -Infinity returns false.)
-
multirange ( anyrange ) → anymultirange
Returns a multirange containing just the given range.
-
multirange_minus_multi ( anymultirange, anymultirange ) → setof anymultirange
Returns the non-empty multirange(s) remaining after subtracting the second multirange from the first.
-
range_merge ( anyrange, anyrange ) → anyrange
Computes the smallest range that includes both of the given ranges.
-
range_minus_multi ( anyrange, anyrange ) → setof anyrange
Returns the non-empty range(s) remaining after subtracting the second range from the first.
-
upper_inc ( anyrange ) → boolean
Is the range's upper bound inclusive?
-
upper_inf ( anyrange ) → boolean
Does the range have no upper bound? (An upper bound of Infinity returns false.)
-
currval ( regclass ) → bigint
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 pred…
-
lastval () → bigint
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…
-
nextval ( regclass ) → bigint
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 …
-
pg_get_sequence_data ( regclass ) → record ( last_value bigint, is_called bool, page_lsn pg_lsn )
Returns information about the sequence.
-
setval ( regclass, bigint [, boolean ] ) → bigint
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 sequ…
-
generate_series ( start integer, stop integer [, step integer ] ) → setof integer
Generates a series of values from start to stop, with a step size of step. step defaults to 1.
-
generate_subscripts ( array anyarray, dim integer ) → setof integer
Generates a series comprising the valid subscripts of the dim'th dimension of the given array.
-
pg_mcv_list_items ( pg_mcv_list ) → setof record
pg_mcv_list_items returns a set of records describing all items stored in a multi-column MCV list. It returns the following columns:
-
ascii ( text ) → integer
Returns the numeric code of the first character of the argument. In UTF8 encoding, returns the Unicode code point of the character. In other multibyte encodings, the argument must be an ASCII character.
-
bit_length ( text ) → integer
Returns number of bits in the string (8 times the octet_length).
-
btrim ( string text [, characters text ] ) → text
Removes the longest string containing only characters in characters (a space by default) from the start and end of string.
-
casefold ( text ) → text
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 con…
-
char_length ( text ) → integer
Returns number of characters in the string.
-
character_length ( text ) → integer
Returns number of characters in the string.
-
chr ( integer ) → text
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 …
-
concat ( val1 "any" [, val2 "any" [, ...] ] ) → text
Concatenates the text representations of all the arguments. NULL arguments are ignored.
-
concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text
Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored.
-
format ( formatstr text [, formatarg "any" [, ...] ] ) → text
Formats arguments according to a format string; see Section 9.4.1. This function is similar to the C function sprintf.
-
getdatabaseencoding ( ) → name
Returns current database encoding name.
-
initcap ( text ) → text
Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
-
left ( string text, n integer ) → text
Returns first n characters in the string, or when n is negative, returns all but last |n| characters.
-
length ( text ) → integer
Returns the number of characters in the string.
-
lower ( text ) → text
Converts the string to all lower case, according to the rules of the database's locale.
-
lpad ( string text, length integer [, fill text ] ) → text
Extends the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right).
-
ltrim ( string text [, characters text ] ) → text
Removes the longest string containing only characters in characters (a space by default) from the start of string.
-
md5 ( text ) → text
Computes the MD5 hash of the argument, with the result written in hexadecimal.
-
normalize ( text [, form ] ) → text
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.
-
octet_length ( text ) → integer
Returns number of bytes in the string.
-
overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text
Replaces the substring of string that starts at the start'th character and extends for count characters with newsubstring. If count is omitted, it defaults to the length of newsubstring.
-
parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[]
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 character…
-
pg_client_encoding ( ) → name
Returns current client encoding name.
-
position ( substring text IN string text ) → integer
Returns first starting index of the specified substring within string, or zero if it's not present.
-
quote_ident ( text ) → text
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 double…
-
quote_literal ( text ) → text
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…
-
quote_nullable ( text ) → text
Returns the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled. See also Example 41.1.
-
regexp_count ( string text, pattern text [, start integer [, flags text ] ] ) → integer
Returns the number of times the POSIX regular expression pattern matches in the string; see Section 9.7.3.
-
regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer
Returns the position within string where the N'th match of the POSIX regular expression pattern occurs, or zero if there is no such match; see Section 9.7.3.
-
regexp_like ( string text, pattern text [, flags text ] ) → boolean
Checks whether a match of the POSIX regular expression pattern occurs within string; see Section 9.7.3.
-
regexp_match ( string text, pattern text [, flags text ] ) → text[]
Returns substrings within the first match of the POSIX regular expression pattern to the string; see Section 9.7.3.
-
regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]
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.
-
regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text
Replaces the substring that is the first match to the POSIX regular expression pattern, or all such matches if the g flag is used; see Section 9.7.3.
-
regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]
Splits string using a POSIX regular expression as the delimiter, producing an array of results; see Section 9.7.3.
-
regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text
Splits string using a POSIX regular expression as the delimiter, producing a set of results; see Section 9.7.3.
-
regexp_substr ( string text, pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ] ) → text
Returns the substring within string that matches the N'th occurrence of the POSIX regular expression pattern, or NULL if there is no such match; see Section 9.7.3.
-
repeat ( string text, number integer ) → text
Repeats string the specified number of times.
-
replace ( string text, from text, to text ) → text
Replaces all occurrences in string of substring from with substring to.
-
reverse ( text ) → text
Reverses the order of the characters in the string.
-
right ( string text, n integer ) → text
Returns last n characters in the string, or when n is negative, returns all but first |n| characters.
-
rpad ( string text, length integer [, fill text ] ) → text
Extends the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated.
-
rtrim ( string text [, characters text ] ) → text
Removes the longest string containing only characters in characters (a space by default) from the end of string.
-
split_part ( string text, delimiter text, n integer ) → text
Splits string at occurrences of delimiter and returns the n'th field (counting from one), or when n is negative, returns the |n|'th-from-last field.
-
starts_with ( string text, prefix text ) → boolean
Returns true if string starts with prefix.
-
string_to_array ( string text, delimiter text [, null_string text ] ) → text[]
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 treat…
-
string_to_table ( string text, delimiter text [, null_string text ] ) → setof text
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 tr…
-
strpos ( string text, substring text ) → integer
Returns first starting index of the specified substring within string, or zero if it's not present. (Same as position(substring in string), but note the reversed argument order.)
-
substr ( string text, start integer [, count integer ] ) → text
Extracts the substring of string starting at the start'th character, and extending for count characters if that is specified. (Same as substring(string from start for count).)
-
substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text
Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.
-
to_ascii ( string text ) → text
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. Co…
-
to_bin ( integer ) → text
Converts the number to its equivalent two's complement binary representation.
-
to_hex ( integer ) → text
Converts the number to its equivalent two's complement hexadecimal representation.
-
to_oct ( integer ) → text
Converts the number to its equivalent two's complement octal representation.
-
translate ( string text, from text, to text ) → text
Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.
-
trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text
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.
-
unicode_assigned ( text ) → boolean
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.
-
unistr ( text ) → text
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, w…
-
upper ( text ) → text
Converts the string to all upper case, according to the rules of the database's locale.
-
brin_desummarize_range ( index regclass, blockNumber bigint ) → void
Removes the BRIN index tuple that summarizes the page range covering the given table block, if there is one.
-
brin_summarize_new_values ( index regclass ) → integer
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 summarie…
-
brin_summarize_range ( index regclass, blockNumber bigint ) → integer
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.
-
current_setting ( setting_name text [, missing_ok boolean ] ) → text
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.
-
gin_clean_pending_list ( index regclass ) → bigint
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 disa…
-
pg_advisory_lock ( key bigint ) → void
Obtains an exclusive session-level advisory lock, waiting if necessary.
-
pg_advisory_lock_shared ( key bigint ) → void
Obtains a shared session-level advisory lock, waiting if necessary.
-
pg_advisory_unlock ( key bigint ) → boolean
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.
-
pg_advisory_unlock_all () → void
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.)
-
pg_advisory_unlock_shared ( key bigint ) → boolean
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.
-
pg_advisory_xact_lock ( key bigint ) → void
Obtains an exclusive transaction-level advisory lock, waiting if necessary.
-
pg_advisory_xact_lock_shared ( key bigint ) → void
Obtains a shared transaction-level advisory lock, waiting if necessary.
-
pg_backup_start ( label text [, fast boolean ] ) → pg_lsn
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 gi…
-
pg_backup_start_time () → timestamp with time zone
Returns the start time of the current on-line exclusive backup if one is in progress, otherwise NULL.
-
pg_backup_stop ( [wait_for_archive boolean ] ) → record ( lsn pg_lsn, labelfile text, spcmapfile text )
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 t…
-
pg_cancel_backend ( pid integer ) → boolean
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, ho…
-
pg_clear_attribute_stats ( schemaname text, relname text, attname text, inherited boolean ) → void
Clears column-level statistics for the given relation and attribute, as though the table was newly created.
-
pg_clear_extended_stats ( schemaname name, relname name, statistics_schemaname name, statistics_name name, inherited boolean ) → void
Clears data of an extended statistics object, as though the object was newly-created.
-
pg_clear_relation_stats ( schemaname text, relname text ) → void
Clears table-level statistics for the given relation, as though the table was newly created.
-
pg_collation_actual_version ( oid ) → text
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 ALTE…
-
pg_column_compression ( "any" ) → text
Shows the compression algorithm that was used to compress an individual variable-length value. Returns NULL if the value is not compressed.
-
pg_column_size ( "any" ) → integer
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.
-
pg_column_toast_chunk_id ( "any" ) → oid8
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.
-
pg_copy_logical_replication_slot ( src_slot_name name, dst_slot_name name [, temporary boolean [, plugin name ]] ) → record ( slot_name name, lsn pg_lsn )
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. Bo…
-
pg_copy_physical_replication_slot ( src_slot_name name, dst_slot_name name [, temporary boolean ] ) → record ( slot_name name, lsn pg_lsn )
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,…
-
pg_create_logical_replication_slot ( slot_name name, plugin name [, temporary boolean, twophase boolean, failover boolean ] ) → record ( slot_name name, lsn pg_lsn )
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 b…
-
pg_create_physical_replication_slot ( slot_name name [, immediately_reserve boolean, temporary boolean ] ) → record ( slot_name name, lsn pg_lsn )
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 replicati…
-
pg_create_restore_point ( name text ) → pg_lsn
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 reco…
-
pg_current_wal_flush_lsn () → pg_lsn
Returns the current write-ahead log flush location (see notes below).
-
pg_current_wal_insert_lsn () → pg_lsn
Returns the current write-ahead log insert location (see notes below).
-
pg_current_wal_lsn () → pg_lsn
Returns the current write-ahead log write location (see notes below).
-
pg_current_xlog_flush_location ( ) → pg_lsn
Get current transaction log flush location
-
pg_current_xlog_insert_location ( ) → pg_lsn
Get current transaction log insert location
-
pg_current_xlog_location ( ) → pg_lsn
Get current transaction log write location
-
pg_database_collation_actual_version ( oid ) → text
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 als…
-
pg_database_size ( name ) → bigint
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.
-
pg_disable_data_checksums () → void
Disables data checksum calculation and validation for the cluster.
-
pg_drop_replication_slot ( slot_name name ) → void
Drops the physical or logical replication slot named slot_name. Same as replication protocol command DROP_REPLICATION_SLOT.
-
pg_enable_data_checksums ( [cost_delay int [, cost_limit int]] ) → void
Initiates the process of enabling data checksums for the cluster.
-
pg_export_snapshot () → text
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 th…
-
pg_filenode_relation ( tablespace oid, filenode oid ) → regclass
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…
-
pg_get_wal_replay_pause_state () → text
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.
-
pg_get_wal_resource_managers () → setof record ( rm_id integer, rm_name text, rm_builtin boolean )
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.
-
pg_import_system_collations ( schema regnamespace ) → integer
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…
-
pg_indexes_size ( regclass ) → bigint
Computes the total disk space used by indexes attached to the specified table.
-
pg_is_in_backup () → boolean
Returns true if an on-line exclusive backup is in progress.
-
pg_is_in_recovery () → boolean
Returns true if recovery is still in progress.
-
pg_is_wal_replay_paused () → boolean
Returns true if recovery pause is requested.
-
pg_is_xlog_replay_paused ( ) → bool
True if recovery is paused.
-
pg_last_wal_receive_lsn () → pg_lsn
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 …
-
pg_last_wal_replay_lsn () → pg_lsn
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 applie…
-
pg_last_xact_replay_timestamp () → timestamp with time zone
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 …
-
pg_last_xlog_receive_location ( ) → pg_lsn
Get last transaction log location received and synced to disk by streaming replication.
-
pg_last_xlog_replay_location ( ) → pg_lsn
Get last transaction log location replayed during recovery.
-
pg_log_backend_memory_contexts ( pid integer ) → boolean
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 th…
-
pg_log_standby_snapshot () → pg_lsn
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 …
-
pg_logical_emit_message ( transactional boolean, prefix text, content text [, flush boolean DEFAULT false] ) → pg_lsn
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 immediate…
-
pg_logical_slot_get_binary_changes ( slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[] ) → setof record ( lsn pg_lsn, xid xid, data bytea )
Behaves just like the pg_logical_slot_get_changes() function, except that changes are returned as bytea.
-
pg_logical_slot_get_changes ( slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[] ) → setof record ( lsn pg_lsn, xid xid, data text )
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 t…
-
pg_logical_slot_peek_binary_changes ( slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[] ) → setof record ( lsn pg_lsn, xid xid, data bytea )
Behaves just like the pg_logical_slot_peek_changes() function, except that changes are returned as bytea.
-
pg_logical_slot_peek_changes ( slot_name name, upto_lsn pg_lsn, upto_nchanges integer, VARIADIC options text[] ) → setof record ( lsn pg_lsn, xid xid, data text )
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.
-
pg_ls_archive_statusdir () → setof record ( name text, size bigint, modification timestamp with time zone )
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.
-
pg_ls_dir ( dirname text [, missing_ok boolean, include_dot_dirs boolean ] ) → setof text
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 c…
-
pg_ls_logdir () → setof record ( name text, size bigint, modification timestamp with time zone )
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.
-
pg_ls_logicalmapdir () → setof record ( name text, size bigint, modification timestamp with time zone )
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.
-
pg_ls_logicalsnapdir () → setof record ( name text, size bigint, modification timestamp with time zone )
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.
-
pg_ls_replslotdir ( slot_name text ) → setof record ( name text, size bigint, modification timestamp with time zone )
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, …
-
pg_ls_summariesdir () → setof record ( name text, size bigint, modification timestamp with time zone )
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.
-
pg_ls_tmpdir ( [ tablespace oid ] ) → setof record ( name text, size bigint, modification timestamp with time zone )
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, dire…
-
pg_ls_waldir () → setof record ( name text, size bigint, modification timestamp with time zone )
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_partition_ancestors ( regclass ) → setof regclass
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.
-
pg_partition_root ( regclass ) → regclass
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.
-
pg_partition_tree ( regclass ) → setof record ( relid regclass, parentrelid regclass, isleaf boolean, level integer )
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 …
-
pg_promote ( wait boolean DEFAULT true, wait_seconds integer DEFAULT 60 ) → boolean
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…
-
pg_read_binary_file ( filename text [, offset bigint, length bigint ] [, missing_ok boolean ] ) → bytea
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.
-
pg_read_file ( filename text [, offset bigint, length bigint ] [, missing_ok boolean ] ) → text
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 ent…
-
pg_relation_filenode ( relation regclass ) → oid
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.r…
-
pg_relation_filepath ( relation regclass ) → text
Returns the entire file path name (relative to the database cluster's data directory, PGDATA) of the relation.
-
pg_relation_size ( relation regclass [, fork text ] ) → bigint
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,…
-
pg_reload_conf () → boolean
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…
-
pg_replication_origin_advance ( node_name text, lsn pg_lsn ) → void
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…
-
pg_replication_origin_create ( node_name text ) → oid
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.
-
pg_replication_origin_drop ( node_name text ) → void
Deletes a previously-created replication origin, including any associated replay progress.
-
pg_replication_origin_oid ( node_name text ) → oid
Looks up a replication origin by name and returns the internal ID. If no such replication origin is found, NULL is returned.
-
pg_replication_origin_progress ( node_name text, flush boolean ) → pg_lsn
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.
-
pg_replication_origin_session_is_setup () → boolean
Returns true if a replication origin has been selected in the current session.
-
pg_replication_origin_session_progress ( flush boolean ) → pg_lsn
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.
-
pg_replication_origin_session_reset () → void
Cancels the effects of pg_replication_origin_session_setup().
-
pg_replication_origin_session_setup ( node_name text [, pid integer DEFAULT 0] ) → void
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.
-
pg_replication_origin_xact_reset () → void
Cancels the effects of pg_replication_origin_xact_setup().
-
pg_replication_origin_xact_setup ( origin_lsn pg_lsn, origin_timestamp timestamp with time zone ) → void
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.
-
pg_replication_slot_advance ( slot_name name, upto_lsn pg_lsn ) → record ( slot_name name, end_lsn pg_lsn )
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 advanc…
-
pg_restore_attribute_stats ( VARIADIC kwargs "any" ) → boolean
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 optimize…
-
pg_restore_extended_stats ( VARIADIC kwargs "any" ) → boolean
Creates or updates statistics for statistics objects.
-
pg_restore_relation_stats ( VARIADIC kwargs "any" ) → boolean
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 …
-
pg_rotate_logfile () → boolean
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.
-
pg_size_bytes ( text ) → bigint
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.
-
pg_size_pretty ( bigint ) → text
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…
-
pg_split_walfile_name ( file_name text ) → record ( segment_number numeric, timeline_id bigint )
Extracts the sequence number and timeline ID from a WAL file name.
-
pg_start_backup ( label text [, fast boolean [, exclusive boolean ]] ) → pg_lsn
Prepares the server to begin an on-line backup.
-
pg_stat_file ( filename text [, missing_ok boolean ] ) → record ( size bigint, access timestamp with time zone, modification timestamp with time zone, change timestamp with time zone, creation timestamp with time zone, isdir boolean )
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.
-
pg_stop_backup ( exclusive boolean [, wait_for_archive boolean ] ) → setof record ( lsn pg_lsn, labelfile text, spcmapfile text )
Finishes performing an exclusive or non-exclusive on-line backup.
-
pg_switch_wal () → pg_lsn
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…
-
pg_switch_xlog ( ) → pg_lsn
Force switch to a new transaction log file (restricted to superusers by default, but other users can be granted EXECUTE to run the function)
-
pg_sync_replication_slots () → void
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…
-
pg_table_size ( regclass ) → bigint
Computes the disk space used by the specified table, excluding indexes (but including its TOAST table if any, free space map, and visibility map).
-
pg_tablespace_size ( name ) → bigint
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 tablespa…
-
pg_terminate_backend ( pid integer, timeout bigint DEFAULT 0 ) → boolean
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 super…
-
pg_total_relation_size ( regclass ) → bigint
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.
-
pg_try_advisory_lock ( key bigint ) → boolean
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.
-
pg_try_advisory_lock_shared ( key bigint ) → boolean
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.
-
pg_try_advisory_xact_lock ( key bigint ) → boolean
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.
-
pg_try_advisory_xact_lock_shared ( key bigint ) → boolean
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.
-
pg_wal_lsn_diff ( lsn1 pg_lsn, lsn2 pg_lsn ) → numeric
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.
-
pg_wal_replay_pause () → void
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_w…
-
pg_wal_replay_resume () → void
Restarts recovery if it was paused.
-
pg_walfile_name ( lsn pg_lsn ) → text
Converts a write-ahead log location to the name of the WAL file holding that location.
-
pg_walfile_name_offset ( lsn pg_lsn ) → record ( file_name text, file_offset integer )
Converts a write-ahead log location to a WAL file name and byte offset within that file.
-
pg_xlog_location_diff ( location pg_lsn, location pg_lsn ) → numeric
Calculate the difference between two transaction log locations
-
pg_xlog_replay_pause ( ) → void
Pauses recovery immediately (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
-
pg_xlog_replay_resume ( ) → void
Restarts recovery if it was paused (restricted to superusers by default, but other users can be granted EXECUTE to run the function).
-
pg_xlogfile_name ( location pg_lsn ) → text
Convert transaction log location string to file name
-
pg_xlogfile_name_offset ( location pg_lsn ) → text, integer
Convert transaction log location string to file name and decimal byte offset within file
-
set_config ( setting_name text, new_value text, is_local boolean ) → text
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 …
-
acldefault ( type "char", ownerId oid ) → aclitem[]
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 p…
-
aclexplode ( aclitem[] ) → setof record ( grantor oid, grantee oid, privilege_type text, is_grantable boolean )
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 privi…
-
col_description ( table oid, column integer ) → text
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.)
-
current_catalog → name
Returns the name of the current database. (Databases are called “catalogs” in the SQL standard, so current_catalog is the standard's spelling.)
-
current_database () → name
Returns the name of the current database. (Databases are called “catalogs” in the SQL standard, so current_catalog is the standard's spelling.)
-
current_query () → text
Returns the text of the currently executing query, as submitted by the client (which might contain more than one statement).
-
current_role → name
This is equivalent to current_user.
-
current_schema → name
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.
-
current_schemas ( include_implicit boolean ) → name[]
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 t…
-
current_user → name
Returns the user name of the current execution context.
-
format_type ( type oid, typemod integer ) → text
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.
-
has_any_column_privilege ( [ user name or oid, ] table text or oid, privilege text ) → boolean
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, …
-
has_column_privilege ( [ user name or oid, ] table text or oid, column text or smallint, privilege text ) → boolean
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 numb…
-
has_database_privilege ( [ user name or oid, ] database text or oid, privilege text ) → boolean
Does user have privilege for database? Allowable privilege types are CREATE, CONNECT, TEMPORARY, and TEMP (which is equivalent to TEMPORARY).
-
has_foreign_data_wrapper_privilege ( [ user name or oid, ] fdw text or oid, privilege text ) → boolean
Does user have privilege for foreign-data wrapper? The only allowable privilege type is USAGE.
-
has_function_privilege ( [ user name or oid, ] function text or oid, privilege text ) → boolean
Does user have privilege for function? The only allowable privilege type is EXECUTE.
-
has_language_privilege ( [ user name or oid, ] language text or oid, privilege text ) → boolean
Does user have privilege for language? The only allowable privilege type is USAGE.
-
has_largeobject_privilege ( [ user name or oid, ] largeobject oid, privilege text ) → boolean
Does user have privilege for large object? Allowable privilege types are SELECT and UPDATE.
-
has_parameter_privilege ( [ user name or oid, ] parameter text, privilege text ) → boolean
Does user have privilege for configuration parameter? The parameter name is case-insensitive. Allowable privilege types are SET and ALTER SYSTEM.
-
has_schema_privilege ( [ user name or oid, ] schema text or oid, privilege text ) → boolean
Does user have privilege for schema? Allowable privilege types are CREATE and USAGE.
-
has_sequence_privilege ( [ user name or oid, ] sequence text or oid, privilege text ) → boolean
Does user have privilege for sequence? Allowable privilege types are USAGE, SELECT, and UPDATE.
-
has_server_privilege ( [ user name or oid, ] server text or oid, privilege text ) → boolean
Does user have privilege for foreign server? The only allowable privilege type is USAGE.
-
has_table_privilege ( [ user name or oid, ] table text or oid, privilege text ) → boolean
Does user have privilege for table? Allowable privilege types are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and MAINTAIN.
-
has_tablespace_privilege ( [ user name or oid, ] tablespace text or oid, privilege text ) → boolean
Does user have privilege for tablespace? The only allowable privilege type is CREATE.
-
has_type_privilege ( [ user name or oid, ] type text or oid, privilege text ) → boolean
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).
-
icu_unicode_version () → text
Returns a string representing the version of Unicode used by ICU, if the server was built with ICU support; otherwise returns NULL
-
inet_client_addr () → inet
Returns the IP address of the current client, or NULL if the current connection is via a Unix-domain socket.
-
inet_client_port () → integer
Returns the IP port number of the current client, or NULL if the current connection is via a Unix-domain socket.
-
inet_server_addr () → inet
Returns the IP address on which the server accepted the current connection, or NULL if the current connection is via a Unix-domain socket.
-
inet_server_port () → integer
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.
-
makeaclitem ( grantee oid, grantor oid, privileges text, is_grantable boolean ) → aclitem
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 allow…
-
mxid_age ( xid ) → integer
Returns the number of multixacts IDs between the supplied multixact ID and the current multixacts counter.
-
obj_description ( object oid, catalog name ) → text
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.
-
pg_available_wal_summaries () → setof record ( tli bigint, start_lsn pg_lsn, end_lsn pg_lsn )
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 b…
-
pg_backend_pid () → integer
Returns the process ID of the server process attached to the current session.
-
pg_basetype ( regtype ) → regtype
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…
-
pg_blocking_pids ( integer ) → integer[]
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.
-
pg_char_to_encoding ( encoding name ) → integer
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.
-
pg_collation_is_visible ( collation oid ) → boolean
Is collation visible in search path?
-
pg_conf_load_time () → timestamp with time zone
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 sessio…
-
pg_control_checkpoint () → record
Returns information about current checkpoint state, as shown in Table 9.89.
-
pg_control_init () → record
Returns information about cluster initialization state, as shown in Table 9.91.
-
pg_control_recovery () → record
Returns information about recovery state, as shown in Table 9.92.
-
pg_control_system () → record
Returns information about current control file state, as shown in Table 9.90.
-
pg_conversion_is_visible ( conversion oid ) → boolean
Is conversion visible in search path?
-
pg_current_logfile ( [ text ] ) → text
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, …
-
pg_current_snapshot () → pg_snapshot
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.
-
pg_current_xact_id () → xid8
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 …
-
pg_current_xact_id_if_assigned () → xid8
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 th…
-
pg_describe_object ( classid oid, objid oid, objsubid integer ) → text
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 huma…
-
pg_encoding_to_char ( encoding integer ) → name
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_function_is_visible ( function oid ) → boolean
Is function visible in search path? (This also works for procedures and aggregates.)
-
pg_get_acl ( classid oid, objid oid, objsubid integer ) → aclitem[]
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.
-
pg_get_catalog_foreign_keys () → setof record ( fktable regclass, fkcols text[], pktable regclass, pkcols text[], is_array boolean, is_opt boolean )
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…
-
pg_get_constraintdef ( constraint oid [, pretty boolean ] ) → text
Reconstructs the creating command for a constraint. (This is a decompiled reconstruction, not the original text of the command.)
-
pg_get_database_ddl ( database regdatabase [, pretty boolean DEFAULT false] [, owner boolean DEFAULT true] [, tablespace boolean DEFAULT true] ) → setof text
Reconstructs the CREATE DATABASE statement for the specified database, followed by ALTER DATABASE statements for connection limit, template status, and configuration settings.
-
pg_get_expr ( expr pg_node_tree, relation oid [, pretty boolean ] ) → text
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…
-
pg_get_function_arguments ( func oid ) → text
Reconstructs the argument list of a function or procedure, in the form it would need to appear in within CREATE FUNCTION (including default values).
-
pg_get_function_identity_arguments ( func oid ) → text
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.
-
pg_get_function_result ( func oid ) → text
Reconstructs the RETURNS clause of a function, in the form it would need to appear in within CREATE FUNCTION. Returns NULL for a procedure.
-
pg_get_functiondef ( func oid ) → text
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.
-
pg_get_indexdef ( index oid [, column integer, pretty boolean ] ) → text
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.
-
pg_get_keywords () → setof record ( word text, catcode "char", barelabel boolean, catdesc text, baredesc text )
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…
-
pg_get_loaded_modules () → setof record ( module_name text, version text, file_name text )
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 …
-
pg_get_multixact_members ( multixid xid ) → setof record ( xid xid, mode text )
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…
-
pg_get_multixact_stats () → record ( num_mxids integer, num_members bigint, members_size bigint, oldest_multixact xid )
Returns statistics about current multixact usage: num_mxids is the total number of multixact IDs currently present in the system, num_members is the total number of multixact member entries currently present in the system, members_size is the storag…
-
pg_get_object_address ( type text, object_names text[], object_args text[] ) → record ( classid oid, objid oid, objsubid integer )
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 …
-
pg_get_partition_constraintdef ( table oid ) → text
Reconstructs the definition of a partition constraint. (This is a decompiled reconstruction, not the original text of the command.)
-
pg_get_partkeydef ( table oid ) → text
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.)
-
pg_get_role_ddl ( role regrole [, pretty boolean DEFAULT false] [, memberships boolean DEFAULT true] ) → setof text
Reconstructs the CREATE ROLE statement and any ALTER ROLE ...
-
pg_get_ruledef ( rule oid [, pretty boolean ] ) → text
Reconstructs the creating command for a rule. (This is a decompiled reconstruction, not the original text of the command.)
-
pg_get_serial_sequence ( table text, column text ) → text
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 usi…
-
pg_get_statisticsobjdef ( statobj oid ) → text
Reconstructs the creating command for an extended statistics object. (This is a decompiled reconstruction, not the original text of the command.)
-
pg_get_tablespace_ddl ( tablespace oid [, pretty boolean DEFAULT false] [, owner boolean DEFAULT true] ) → setof text
Reconstructs the CREATE TABLESPACE statement for the specified tablespace (by OID or name).
-
pg_get_triggerdef ( trigger oid [, pretty boolean ] ) → text
Reconstructs the creating command for a trigger. (This is a decompiled reconstruction, not the original text of the command.)
-
pg_get_userbyid ( role oid ) → name
Returns a role's name given its OID.
-
pg_get_viewdef ( view oid [, pretty boolean ] ) → text
Reconstructs the underlying SELECT command for a view or materialized view. (This is a decompiled reconstruction, not the original text of the command.)
-
pg_get_wal_summarizer_state () → record ( summarized_tli bigint, summarized_lsn pg_lsn, pending_lsn pg_lsn, summarizer_pid int )
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 o…
-
pg_has_role ( [ user name or oid, ] role text or oid, privilege text ) → boolean
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 th…
-
pg_identify_object ( classid oid, objid oid, objsubid integer ) → record ( type text, schema text, name text, identity text )
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 da…
-
pg_identify_object_as_address ( classid oid, objid oid, objsubid integer ) → record ( type text, object_names text[], object_args text[] )
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 iden…
-
pg_index_column_has_property ( index regclass, column integer, property text ) → boolean
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 …
-
pg_index_has_property ( index regclass, property text ) → boolean
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 …
-
pg_indexam_has_property ( am oid, property text ) → boolean
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 ob…
-
pg_input_error_info ( string text, type text ) → record ( message text, detail text, hint text, sql_error_code text )
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.
-
pg_input_is_valid ( string text, type text ) → boolean
Tests whether the given string is valid input for the specified data type, returning true or false.
-
pg_is_other_temp_schema ( oid ) → boolean
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.)
-
pg_jit_available () → boolean
Returns true if a JIT compiler extension is available (see Chapter 30) and the jit configuration parameter is set to on.
-
pg_last_committed_xact () → record ( xid xid, timestamp timestamp with time zone, roident oid )
Returns the transaction ID, commit timestamp and replication origin of the latest committed transaction.
-
pg_listening_channels () → setof text
Returns the set of names of asynchronous notification channels that the current session is listening to.
-
pg_my_temp_schema () → oid
Returns the OID of the current session's temporary schema, or zero if it has none (because it has not created any temporary tables).
-
pg_notification_queue_usage () → double precision
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.
-
pg_numa_available () → boolean
Returns true if the server has been compiled with NUMA support.
-
pg_opclass_is_visible ( opclass oid ) → boolean
Is operator class visible in search path?
-
pg_operator_is_visible ( operator oid ) → boolean
Is operator visible in search path?
-
pg_opfamily_is_visible ( opclass oid ) → boolean
Is operator family visible in search path?
-
pg_options_to_table ( options_array text[] ) → setof record ( option_name text, option_value text )
Returns the set of storage options represented by a value from pg_class.reloptions or pg_attribute.attoptions.
-
pg_postmaster_start_time () → timestamp with time zone
Returns the time when the server started.
-
pg_safe_snapshot_blocking_pids ( integer ) → integer[]
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.
-
pg_settings_get_flags ( guc text ) → text[]
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.
-
pg_snapshot_xip ( pg_snapshot ) → setof xid8
Returns the set of in-progress transaction IDs contained in a snapshot.
-
pg_snapshot_xmax ( pg_snapshot ) → xid8
Returns the xmax of a snapshot.
-
pg_snapshot_xmin ( pg_snapshot ) → xid8
Returns the xmin of a snapshot.
-
pg_statistics_obj_is_visible ( stat oid ) → boolean
Is statistics object visible in search path?
-
pg_table_is_visible ( table oid ) → boolean
Is table visible in search path? (This works for all types of relations, including views, materialized views, indexes, sequences and foreign tables.)
-
pg_tablespace_databases ( tablespace oid ) → setof oid
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 nee…
-
pg_tablespace_location ( tablespace oid ) → text
Returns the file system path that this tablespace is located in.
-
pg_trigger_depth () → integer
Returns the current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger).
-
pg_ts_config_is_visible ( config oid ) → boolean
Is text search configuration visible in search path?
-
pg_ts_dict_is_visible ( dict oid ) → boolean
Is text search dictionary visible in search path?
-
pg_ts_parser_is_visible ( parser oid ) → boolean
Is text search parser visible in search path?
-
pg_ts_template_is_visible ( template oid ) → boolean
Is text search template visible in search path?
-
pg_type_is_visible ( type oid ) → boolean
Is type (or domain) visible in search path?
-
pg_typeof ( "any" ) → regtype
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 m…
-
pg_visible_in_snapshot ( xid8, pg_snapshot ) → boolean
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.
-
pg_wal_summary_contents ( tli bigint, start_lsn pg_lsn, end_lsn pg_lsn ) → setof record ( relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, is_limit_block boolean )
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 o…
-
pg_xact_commit_timestamp ( xid ) → timestamp with time zone
Returns the commit timestamp of a transaction.
-
pg_xact_commit_timestamp_origin ( xid ) → record ( timestamp timestamp with time zone, roident oid)
Returns the commit timestamp and replication origin of a transaction.
-
pg_xact_status ( xid8 ) → text
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 ref…
-
row_security_active ( table text or oid ) → boolean
Is row-level security active for the specified table in the context of the current user and current environment?
-
session_user → name
Returns the session user's name.
-
shobj_description ( object oid, catalog name ) → text
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 t…
-
system_user → text
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 (…
-
to_regclass ( text ) → regclass
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.
-
to_regcollation ( text ) → regcollation
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.
-
to_regdatabase ( text ) → regdatabase
Translates a textual database name to its OID.
-
to_regnamespace ( text ) → regnamespace
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.
-
to_regoper ( text ) → regoper
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.
-
to_regoperator ( text ) → regoperator
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…
-
to_regproc ( text ) → regproc
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 a…
-
to_regprocedure ( text ) → regprocedure
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 th…
-
to_regrole ( text ) → regrole
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.
-
to_regtype ( text ) → regtype
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…
-
to_regtypemod ( text ) → integer
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…
-
txid_current () → bigint
See pg_current_xact_id().
-
txid_current_if_assigned () → bigint
See pg_current_xact_id_if_assigned().
-
txid_current_snapshot () → txid_snapshot
See pg_current_snapshot().
-
txid_snapshot_xip ( txid_snapshot ) → setof bigint
See pg_snapshot_xip().
-
txid_snapshot_xmax ( txid_snapshot ) → bigint
See pg_snapshot_xmax().
-
txid_snapshot_xmin ( txid_snapshot ) → bigint
See pg_snapshot_xmin().
-
txid_status ( bigint ) → text
See pg_xact_status().
-
txid_visible_in_snapshot ( bigint, txid_snapshot ) → boolean
See pg_visible_in_snapshot().
-
unicode_version () → text
Returns a string representing the version of Unicode used by PostgreSQL.
-
user → name
This is equivalent to current_user.
-
version () → text
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 …
-
array_to_tsvector ( text[] ) → tsvector
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.
-
get_current_ts_config ( ) → regconfig
Returns the OID of the current default text search configuration (as set by default_text_search_config).
-
json_to_tsvector ( [ config regconfig, ] document json, filter jsonb ) → tsvector
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 out…
-
jsonb_to_tsvector ( [ config regconfig, ] document jsonb, filter jsonb ) → tsvector
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 out…
-
numnode ( tsquery ) → integer
Returns the number of lexemes plus operators in the tsquery.
-
phraseto_tsquery ( [ config regconfig, ] query text ) → tsquery
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 …
-
plainto_tsquery ( [ config regconfig, ] query text ) → tsquery
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 i…
-
querytree ( tsquery ) → text
Produces a representation of the indexable portion of a tsquery. A result that is empty or just T indicates a non-indexable query.
-
setweight ( vector tsvector, weight "char" ) → tsvector
Assigns the specified weight to each element of the vector.
-
strip ( tsvector ) → tsvector
Removes positions and weights from the tsvector.
-
to_tsquery ( [ config regconfig, ] query text ) → tsquery
Converts text to a tsquery, normalizing words according to the specified or default configuration. The words must be combined by valid tsquery operators.
-
to_tsvector ( [ config regconfig, ] document text ) → tsvector
Converts text to a tsvector, normalizing words according to the specified or default configuration. Position information is included in the result.
-
ts_debug ( [ config regconfig, ] document text ) → setof record ( alias text, description text, token text, dictionaries regdictionary[], dictionary regdictionary, lexemes text[] )
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.
-
ts_delete ( vector tsvector, lexeme text ) → tsvector
Removes any occurrence of the given lexeme from the vector. The lexeme string is treated as a lexeme as-is, without further processing.
-
ts_filter ( vector tsvector, weights "char"[] ) → tsvector
Selects only elements with the given weights from the vector.
-
ts_headline ( [ config regconfig, ] document text, query tsquery [, options text ] ) → text
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 …
-
ts_lexize ( dict regdictionary, token text ) → text[]
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.
-
ts_parse ( parser_name text, document text ) → setof record ( tokid integer, token text )
Extracts tokens from the document using the named parser. See Section 12.8.2 for details.
-
ts_rank ( [ weights real[], ] vector tsvector, query tsquery [, normalization integer ] ) → real
Computes a score showing how well the vector matches the query. See Section 12.3.3 for details.
-
ts_rank_cd ( [ weights real[], ] vector tsvector, query tsquery [, normalization integer ] ) → real
Computes a score showing how well the vector matches the query, using a cover density algorithm. See Section 12.3.3 for details.
-
ts_rewrite ( query tsquery, target tsquery, substitute tsquery ) → tsquery
Replaces occurrences of target with substitute within the query. See Section 12.4.2.1 for details.
-
ts_stat ( sqlquery text [, weights text ] ) → setof record ( word text, ndoc integer, nentry integer )
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.
-
ts_token_type ( parser_name text ) → setof record ( tokid integer, alias text, description text )
Returns a table that describes each type of token the named parser can recognize. See Section 12.8.2 for details.
-
tsquery_phrase ( query1 tsquery, query2 tsquery ) → tsquery
Constructs a phrase query that searches for matches of query1 and query2 at successive lexemes (same as <-> operator).
-
tsvector_to_array ( tsvector ) → text[]
Converts a tsvector to an array of lexemes.
-
unnest ( tsvector ) → setof record ( lexeme text, positions smallint[], weights text )
Expands a tsvector into a set of rows, one per lexeme.
-
websearch_to_tsquery ( [ config regconfig, ] query text ) → tsquery
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; othe…
-
tid_block ( tid ) → bigint
Extracts the block number from a tuple identifier.
-
tid_offset ( tid ) → integer
Extracts the tuple offset within the block from a tuple identifier.
-
suppress_redundant_updates_trigger ( ) → trigger
Suppresses do-nothing update operations. See below for details.
-
tsvector_update_trigger ( ) → trigger
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.
-
tsvector_update_trigger_column ( ) → trigger
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.
-
gen_random_uuid ( ) → uuid
Generates a version 4 (random) UUID
-
uuid_extract_timestamp ( uuid ) → timestamp with time zone
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 implementat…
-
uuid_extract_version ( uuid ) → smallint
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.
-
uuidv4 ( ) → uuid
Generates a version 4 (random) UUID
-
uuidv7 ( [ shift interval ] ) → 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…
-
first_value ( value anyelement ) [ null treatment ] → anyelement
Returns value evaluated at the row that is the first row of the window frame.
-
lag ( value anycompatible [, offset integer [, default anycompatible ]] ) [ null treatment ] → anycompatible
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 …
-
last_value ( value anyelement ) [ null treatment ] → anyelement
Returns value evaluated at the row that is the last row of the window frame.
-
lead ( value anycompatible [, offset integer [, default anycompatible ]] ) [ null treatment ] → anycompatible
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 t…
-
nth_value ( value anyelement, n integer ) [ null treatment ] → anyelement
Returns value evaluated at the row that is the n'th row of the window frame (counting from 1); returns NULL if there is no such row.
-
ntile ( num_buckets integer ) → integer
Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.
-
row_number () → bigint
Returns the number of the current row within its partition, counting from 1.
-
cursor_to_xml ( cursor refcursor, count integer, nulls boolean, tableforest boolean, targetns text ) → xml
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). que…
-
cursor_to_xmlschema ( cursor refcursor, nulls boolean, tableforest boolean, targetns text ) → xml
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
-
database_to_xml ( nulls boolean, tableforest boolean, targetns text ) → xml
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.
-
database_to_xml_and_xmlschema ( nulls boolean, tableforest boolean, targetns text ) → xml
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.
-
database_to_xmlschema ( nulls boolean, tableforest boolean, targetns text ) → xml
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.
-
query_to_xml ( query text, nulls boolean, tableforest boolean, targetns text ) → xml
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). que…
-
query_to_xml_and_xmlschema ( query text, nulls boolean, tableforest boolean, targetns text ) → xml
In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:
-
query_to_xmlschema ( query text, nulls boolean, tableforest boolean, targetns text ) → xml
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
-
schema_to_xml ( schema name, nulls boolean, tableforest boolean, targetns text ) → xml
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.
-
schema_to_xml_and_xmlschema ( schema name, nulls boolean, tableforest boolean, targetns text ) → xml
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.
-
schema_to_xmlschema ( schema name, nulls boolean, tableforest boolean, targetns text ) → xml
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.
-
table_to_xml ( table regclass, nulls boolean, tableforest boolean, targetns text ) → xml
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). que…
-
table_to_xml_and_xmlschema ( table regclass, nulls boolean, tableforest boolean, targetns text ) → xml
In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:
-
table_to_xmlschema ( table regclass, nulls boolean, tableforest boolean, targetns text ) → xml
It is essential that the same parameters are passed in order to obtain matching XML data mappings and XML Schema documents.
-
xml_is_well_formed ( text ) → boolean
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 do…
-
xml_is_well_formed_content ( text ) → boolean
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 do…
-
xml_is_well_formed_document ( text ) → boolean
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 do…
-
xmlagg ( xml ) → xml
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 expr…
-
xmlcomment ( text ) → xml
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, t…
-
xmlconcat ( xml [, ...] ) → xml
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.
-
xmlelement ( NAME name [, XMLATTRIBUTES ( attvalue [AS attname] [, ...] ) ] [, content [, ...]] ) → xml
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 Po…
-
XMLEXISTS ( text PASSING [BY {REF|VALUE}] xml [BY {REF|VALUE}] ) → boolean
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. Th…
-
xmlforest ( content [AS name] [, ...] ) → xml
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.
-
xmlpi ( NAME name [, content] ) → xml
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 ?>.
-
xmlroot ( xml, VERSION {text|NO VALUE} [, STANDALONE {YES|NO|NO VALUE} ] ) → xml
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 st…
-
XMLTABLE ( [XMLNAMESPACES ( namespace_uri AS namespace_name [, ...] ), ] row_expression PASSING [BY {REF|VALUE}] document_expression [BY {REF|VALUE}] COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL] | FOR ORDINALITY } [, ...] ) → setof record
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.
-
xmltext ( text ) → xml
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.
-
xpath ( xpath text, xml xml [, nsarray text[]] ) → xml[]
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 r…
-
xpath_exists ( xpath text, xml xml [, nsarray text[]] ) → boolean
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 (specifica…