PostgreSQL functions

PostgreSQL Functions: The Complete List with Examples

This page lists every function documented for PostgreSQL 18, all 712 of them, grouped the way the official manual groups them. Above the full list are the 75 functions people use most. Each one has an example query and the result it returned on our PostgreSQL 18.6 server.

A function name in orange links to a full tutorial with more examples, edge cases and errors. New tutorials are added here as they’re published.

  • 712PostgreSQL 18 functions
  • 75with tested examples
  • 23full tutorials
  • Nov 2026last updated

How to read the examples

Every example ran on PostgreSQL 18.6 in psql. The aggregate, window and JSON rows use this small orders table, so you can paste it and get the same results. In the results, t means true, and | separates columns.

CREATE TABLE orders (order_id int PRIMARY KEY, customer varchar(20), state char(2), amount numeric(10,2), order_date date);
INSERT INTO orders VALUES
 (1001, 'Hannah Brooks', 'OH', 45.50, '2026-11-02'),
 (1002, 'Tyler Reed',    'TX', 19.99, '2026-11-03'),
 (1003, 'Grace Miller',  'OH', 45.50, '2026-11-09'),
 (1004, 'Marcus Bennett','CA', 12.00, '2026-11-12'),
 (1005, 'Tyler Reed',    'TX', 80.25, '2026-11-16');
SET TimeZone = 'America/New_York';

PostgreSQL string functions

Measure, change, split and search text.

FunctionWhat it doesExampleResult
length()Number of characters in a string.SELECT length('Hannah Brooks');13
octet_length()Number of bytes in a string.SELECT octet_length('café');5
lower() / upper()Convert text to lowercase or uppercase.SELECT lower('OHIO'), upper('ohio');ohio | OHIO
initcap()Capitalize the first letter of each word.SELECT initcap('grace miller');Grace Miller
concat()Join values into one string; NULLs are skipped.SELECT concat('Tyler', ' ', NULL, 'Reed');Tyler Reed
concat_ws()Join values with a separator between them.SELECT concat_ws(', ', 'Austin', 'TX', '78701');Austin, TX, 78701
substring()Part of a string by position or pattern.SELECT substring('Order-1042' FROM 7);1042
left() / right()The first or last n characters.SELECT left('Sacramento', 4), right('555-0142', 4);Sacr | 0142
position()Where a substring starts (0 if not found).SELECT position('@' IN 'tyler@example.com');6
replace()Replace every occurrence of a substring.SELECT replace('555-014-2233', '-', '');5550142233
trim() / ltrim() / rtrim()Remove leading and/or trailing characters (spaces by default).SELECT '[' || trim(' Denver ') || ']';[Denver]
lpad() / rpad()Pad a string to a length.SELECT lpad('42', 5, '0');00042
split_part()The nth field of a delimited string.SELECT split_part('Boise,ID,83702', ',', 2);ID
repeat() / reverse()Repeat a string n times, or reverse it.SELECT repeat('ab', 3), reverse('stressed');ababab | desserts
starts_with()True if a string starts with a prefix.SELECT starts_with('Portland', 'Port');t
format()Build a string from a template, like printf.SELECT format('%s owes $%s', 'Grace', 12.5);Grace owes $12.5
regexp_replace()Replace text that matches a regular expression.SELECT regexp_replace('Call 555-0142', '\d', '#', 'g');Call ###-####
regexp_like()PostgreSQL 15+True if text matches a regular expression.SELECT regexp_like('ABC-123', '^[A-Z]{3}-\d{3}$');t
regexp_split_to_array()Split text on a regular expression into an array.SELECT regexp_split_to_array('red, green;blue', '[,;]\s*');{red,green,blue}

PostgreSQL date and time functions

Get the current time, pull parts out of a date, round dates and do date math.

FunctionWhat it doesExampleResult
now() / current_timestampDate and time when the current transaction started.SELECT now();2026-11-16 20:01:52.662059-05(changes on every run)
clock_timestamp()The actual time right now, even inside a transaction.SELECT clock_timestamp() >= now();t
current_dateToday's date (no time).SELECT current_date;2026-11-16(changes daily)
date_trunc()Round a timestamp down to a unit (hour, day, month…).SELECT date_trunc('month', timestamp '2026-11-17 14:35:00');2026-11-01 00:00:00
date_part() / extract()Pull one field (year, month, hour, dow…) out of a date or time.SELECT date_part('hour', timestamp '2026-11-17 14:35:00'), extract(dow FROM date '2026-11-17');14 | 2
age()Interval between two dates in years, months and days.SELECT age(date '2026-11-17', date '1990-04-12');36 years 7 mons 5 days
date + intervalAdd or subtract days, months or hours.SELECT date '2026-11-17' + interval '1 month';2026-12-17 00:00:00
make_date()Build a date from year, month and day numbers.SELECT make_date(2026, 11, 17);2026-11-17
date_bin()PostgreSQL 14+Put timestamps into fixed-size buckets (e.g. 15 minutes).SELECT date_bin('15 minutes', timestamp '2026-11-17 14:38', timestamp '2026-01-01');2026-11-17 14:30:00
generate_series() for datesOne row per day, week or month in a range.SELECT d::date FROM generate_series(date '2026-11-01', date '2026-11-03', interval '1 day') AS d;2026-11-01 2026-11-02 2026-11-03

PostgreSQL conversion and formatting functions

Turn text into dates and numbers, and dates and numbers into formatted text.

FunctionWhat it doesExampleResult
CAST() / ::Convert a value to another data type.SELECT CAST('42' AS integer), '2026-11-17 14:35'::timestamp::date;42 | 2026-11-17
to_char() for datesFormat a date or timestamp as text.SELECT to_char(date '2026-11-17', 'MM/DD/YYYY'), to_char(date '2026-11-17', 'FMMonth FMDD, YYYY');11/17/2026 | November 17, 2026
to_char() for numbersFormat a number as text (currency, commas, decimals).SELECT to_char(1234.5, 'FM$9,999.00');$1,234.50
to_date()Turn formatted text into a date.SELECT to_date('11/17/2026', 'MM/DD/YYYY');2026-11-17
to_timestamp()Turn formatted text, or Unix epoch seconds, into a timestamp.SELECT to_timestamp('2026-11-17 14:35', 'YYYY-MM-DD HH24:MI');2026-11-17 14:35:00-05
to_number()Turn formatted text into a number.SELECT to_number('1,250.75', '9,999.99');1250.75

PostgreSQL math functions

Round, truncate and calculate numbers.

FunctionWhat it doesExampleResult
round()Round to n decimal places.SELECT round(42.567, 2);42.57
ceil() / floor()Round up or down to a whole number.SELECT ceil(4.1), floor(4.9);5 | 4
trunc()Cut off decimals without rounding.SELECT trunc(42.567, 1);42.5
abs()Absolute value.SELECT abs(-17);17
mod() / div()Remainder, and whole-number quotient.SELECT mod(17, 5), div(17, 5);2 | 3
power() / sqrt()Raise to a power, square root.SELECT power(2, 10), sqrt(144);1024 | 12
random()Random number from 0 up to (not including) 1.SELECT random() < 1;t

PostgreSQL aggregate functions

Summarize many rows into one value.

FunctionWhat it doesExampleResult
count()Number of rows (or non-NULL values).SELECT count(*), count(DISTINCT customer) FROM orders;5 | 4
sum()Total of a column.SELECT sum(amount) FROM orders;203.24
avg()Average of a column.SELECT round(avg(amount), 2) FROM orders;40.65
min() / max()Smallest and largest value.SELECT min(amount), max(amount) FROM orders;12.00 | 80.25
string_agg()Join values from many rows into one string (GROUP_CONCAT in MySQL).SELECT string_agg(DISTINCT state, ', ' ORDER BY state) FROM orders;CA, OH, TX
array_agg()Collect values from many rows into an array.SELECT array_agg(order_id ORDER BY order_id) FROM orders WHERE state = 'OH';{1001,1003}
count() FILTER (WHERE …)Aggregate only the rows that match a condition.SELECT count(*) FILTER (WHERE amount > 40) FROM orders;3

PostgreSQL window functions

Rank rows and compare each row with its neighbors without collapsing them.

FunctionWhat it doesExampleResult
row_number()1, 2, 3… for each row in a window.SELECT order_id, row_number() OVER (ORDER BY amount DESC) FROM orders ORDER BY 2;1005 | 1 1001 | 2 1003 | 3 1002 | 4 1004 | 5
rank()Rank with gaps after ties.SELECT amount, rank() OVER (ORDER BY amount DESC) FROM orders ORDER BY 2;80.25 | 1 45.50 | 2 45.50 | 2 19.99 | 4 12.00 | 5
dense_rank()Rank without gaps after ties.SELECT amount, dense_rank() OVER (ORDER BY amount DESC) FROM orders ORDER BY 2;80.25 | 1 45.50 | 2 45.50 | 2 19.99 | 3 12.00 | 4
lag() / lead()Value from the previous or next row.SELECT order_id, amount, lag(amount) OVER (ORDER BY order_id) FROM orders WHERE order_id <= 1003 ORDER BY 1;1001 | 45.50 | 1002 | 19.99 | 45.50 1003 | 45.50 | 19.99
sum() OVERRunning total.SELECT order_id, sum(amount) OVER (ORDER BY order_id) FROM orders ORDER BY 1;1001 | 45.50 1002 | 65.49 1003 | 110.99 1004 | 122.99 1005 | 203.24
ntile()Split rows into n roughly equal groups.SELECT order_id, ntile(2) OVER (ORDER BY order_id) FROM orders ORDER BY 1;1001 | 1 1002 | 1 1003 | 1 1004 | 2 1005 | 2

PostgreSQL conditional expressions

Handle NULLs and pick between values.

FunctionWhat it doesExampleResult
COALESCE()First value that isn't NULL.SELECT COALESCE(NULL, NULL, 'N/A');N/A
NULLIF()NULL when two values are equal (avoids divide-by-zero).SELECT 100 / NULLIF(0, 0) IS NULL;t
GREATEST() / LEAST()Largest or smallest of a list of values.SELECT GREATEST(3, 9, 4), LEAST(3, 9, 4);9 | 3
CASEIf/then/else logic inside a query.SELECT CASE WHEN 80.25 >= 50 THEN 'large' ELSE 'small' END;large

PostgreSQL jSON functions

Build and read JSON and JSONB values.

FunctionWhat it doesExampleResult
jsonb_build_object()Build a JSONB object from keys and values.SELECT jsonb_build_object('name', 'Grace Miller', 'state', 'OH');{"name": "Grace Miller", "state": "OH"}
->> operatorRead a JSON field as text.SELECT '{"name": "Tyler Reed", "state": "TX"}'::jsonb ->> 'state';TX
jsonb_set()Change one value inside a JSONB document.SELECT jsonb_set('{"qty": 1}'::jsonb, '{qty}', '3');{"qty": 3}
jsonb_array_length()Number of elements in a JSON array.SELECT jsonb_array_length('["OH", "TX", "CA"]'::jsonb);3
json_agg()Collect rows into a JSON array.SELECT json_agg(customer ORDER BY order_id) FROM orders WHERE state = 'OH';["Hannah Brooks", "Grace Miller"]

PostgreSQL array functions

Create, measure and expand arrays.

FunctionWhat it doesExampleResult
array_length() / cardinality()Number of elements in an array.SELECT array_length(ARRAY[10, 20, 30], 1), cardinality(ARRAY[10, 20, 30]);3 | 3
string_to_array()Split a delimited string into an array.SELECT string_to_array('OH,TX,CA', ',');{OH,TX,CA}
array_to_string()Join array elements into a string.SELECT array_to_string(ARRAY['OH', 'TX', 'CA'], ' | ');OH | TX | CA
unnest()Expand an array into rows.SELECT unnest(ARRAY['OH', 'TX']);OH TX
= ANY()True if a value is in an array.SELECT 'TX' = ANY(ARRAY['OH', 'TX', 'CA']);t

PostgreSQL set-returning functions

Functions that return many rows, such as number and date series.

FunctionWhat it doesExampleResult
generate_series()A series of numbers (or dates) as rows.SELECT generate_series(1, 10, 3);1 4 7 10
generate_subscripts()The index of every element in an array.SELECT generate_subscripts(ARRAY['a', 'b', 'c'], 1);1 2 3

PostgreSQL system information functions

Check the server version, the current user and object sizes.

FunctionWhat it doesExampleResult
version()Full PostgreSQL version string.SELECT split_part(version(), ',', 1);PostgreSQL 18.6 on x86_64-windows
current_user / current_database()Who you're connected as, and to which database.SELECT current_user, current_database();postgres | dfq_fnhub
pg_typeof()The data type of a value.SELECT pg_typeof(42), pg_typeof(42.0), pg_typeof(now());integer | numeric | timestamp with time zone
pg_size_pretty() + pg_total_relation_size()Size of a table (with indexes) in readable units.SELECT pg_size_pretty(pg_total_relation_size('orders'));24 kB

Complete list of PostgreSQL functions (712)

Every function in the PostgreSQL 18 manual, A to Z inside each group, with its one-line description from the manual. Operators (such as || and ->) aren’t listed. Functions marked Tutorial have a full DatabaseFAQs tutorial.

String functions57
  • asciiReturns the numeric code of the first character of the argument.
  • bit_lengthReturns number of bits in the string (8 times the octet_length).
  • btrimRemoves the longest string containing only characters in characters (a space by default) from the start and end of string.
  • casefoldPerforms case folding of the input string according to the collation.
  • char_lengthTutorial Returns number of characters in the string.
  • character_lengthReturns number of characters in the string.
  • chrReturns the character with the given code.
  • concatTutorial Concatenates the text representations of all the arguments.
  • concat_wsTutorial Concatenates all but the first argument, with separators.
  • formatFormats arguments according to a format string.
  • initcapConverts the first letter of each word to upper case and the rest to lower case.
  • leftReturns first n characters in the string, or when n is negative, returns all but last | n | characters.
  • lengthTutorial Returns the number of characters in the string.
  • lowerConverts the string to all lower case, according to the rules of the database's locale.
  • lpadExtends the string to length length by prepending the characters fill (a space by default).
  • ltrimRemoves the longest string containing only characters in characters (a space by default) from the start of string.
  • md5Computes the MD5 hash of the argument, with the result written in hexadecimal.
  • normalizeConverts the string to the specified Unicode normalization form.
  • octet_lengthTutorial Returns number of bytes in the string.
  • overlayReplaces the substring of string that starts at the start 'th character and extends for count characters with newsubstring.
  • parse_identSplits the qualified identifier str into an array of identifiers, removing any quoting of individual identifiers.
  • pg_client_encodingReturns current client encoding name.
  • positionReturns first starting index of the specified substring within string, or zero if it's not present.
  • quote_identReturns the given string suitably quoted to be used as an identifier in an SQL statement string.
  • quote_literalReturns the given string suitably quoted to be used as a string literal in an SQL statement string.
  • quote_nullableReturns 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.
  • regexp_countReturns the number of times the POSIX regular expression pattern matches in the string.
  • regexp_instrReturns the position within string where the N 'th match of the POSIX regular expression pattern occurs, or zero if there is no such match.
  • regexp_likeChecks whether a match of the POSIX regular expression pattern occurs within string.
  • regexp_matchReturns substrings within the first match of the POSIX regular expression pattern to the string.
  • regexp_matchesReturns 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.
  • regexp_replaceTutorial Replaces the substring that is the first match to the POSIX regular expression pattern, or all such matches if the g flag is used.
  • regexp_split_to_arraySplits string using a POSIX regular expression as the delimiter, producing an array of results.
  • regexp_split_to_tableSplits string using a POSIX regular expression as the delimiter, producing a set of results.
  • regexp_substrReturns the substring within string that matches the N 'th occurrence of the POSIX regular expression pattern, or NULL if there is no such match.
  • repeatRepeats string the specified number of times.
  • replaceTutorial Replaces all occurrences in string of substring from with substring to.
  • reverseReverses the order of the characters in the string.
  • rightReturns last n characters in the string, or when n is negative, returns all but first | n | characters.
  • rpadExtends the string to length length by appending the characters fill (a space by default).
  • rtrimRemoves the longest string containing only characters in characters (a space by default) from the end of string.
  • split_partSplits 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_withReturns true if string starts with prefix.
  • string_to_arraySplits the string at occurrences of delimiter and forms the resulting fields into a text array.
  • string_to_tableSplits the string at occurrences of delimiter and returns the resulting fields as a set of text rows.
  • strposReturns first starting index of the specified substring within string, or zero if it's not present.
  • substrExtracts the substring of string starting at the start 'th character, and extending for count characters if that is specified.
  • substringExtracts the substring of string starting at the start 'th character if that is specified, and stopping after count characters if that is specified.
  • to_asciiConverts string to ASCII from another encoding, which may be identified by name or number.
  • to_binConverts the number to its equivalent two's complement binary representation.
  • to_hexConverts the number to its equivalent two's complement hexadecimal representation.
  • to_octConverts the number to its equivalent two's complement octal representation.
  • translateReplaces each character in string that matches a character in the from set with the corresponding character in the to set.
  • trimRemoves 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_assignedReturns true if all characters in the string are assigned Unicode codepoints; false otherwise.
  • unistrEvaluate escaped Unicode characters in the argument.
  • upperConverts the string to all upper case, according to the rules of the database's locale.
Date/time functions31
  • ageTutorial Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days.
  • clock_timestampTutorial Current date and time (changes during statement execution).
  • current_dateTutorial Current date.
  • current_timeCurrent time of day.
  • current_timestampTutorial Current date and time (start of current transaction).
  • date_addTutorial 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.
  • date_binBin input into specified interval aligned with specified origin.
  • date_partTutorial Get timestamp subfield (equivalent to extract).
  • date_subtractTutorial 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.
  • date_truncTutorial Truncate to specified precision.
  • extractTutorial Get timestamp subfield.
  • isfiniteTest for finite date (not +/-infinity).
  • justify_daysAdjust interval, converting 30-day time periods to months.
  • justify_hoursAdjust interval, converting 24-hour time periods to days.
  • justify_intervalAdjust interval using justify_days and justify_hours, with additional sign adjustments.
  • localtimeCurrent time of day.
  • localtimestampCurrent date and time (start of current transaction).
  • make_dateCreate date from year, month and day fields (negative years signify BC).
  • make_intervalCreate interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zero.
  • make_timeCreate time from hour, minute and seconds fields.
  • make_timestampCreate timestamp from year, month, day, hour, minute and seconds fields (negative years signify BC).
  • make_timestamptzCreate timestamp with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC).
  • nowTutorial Current date and time (start of current transaction).
  • pg_sleepMakes the current session's process sleep for the given number of seconds.
  • pg_sleep_forSleeps for the given interval, for example pg_sleep_for('5 minutes').
  • pg_sleep_untilSleeps until the given timestamp.
  • statement_timestampCurrent date and time (start of current statement).
  • timeofdayCurrent date and time (like clock_timestamp, but as a text string).
  • timezoneConverts a timestamp to or from another time zone; the function form of AT TIME ZONE.
  • to_timestampTutorial Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone.
  • transaction_timestampCurrent date and time (start of current transaction).
Data type formatting functions4
  • to_charTutorial Converts time stamp to string according to the given format.
  • to_dateConverts string to date according to the given format.
  • to_numberTutorial Converts string to numeric according to the given format.
  • to_timestampTutorial Converts string to time stamp according to the given format.
Math functions55
  • absAbsolute value.
  • acosInverse cosine, result in radians.
  • acosdInverse cosine, result in degrees.
  • acoshInverse hyperbolic cosine.
  • asinInverse sine, result in radians.
  • asindInverse sine, result in degrees.
  • asinhInverse hyperbolic sine.
  • atanInverse tangent, result in radians.
  • atan2Inverse tangent of y / x, result in radians.
  • atan2dInverse tangent of y / x, result in degrees.
  • atandInverse tangent, result in degrees.
  • atanhInverse hyperbolic tangent.
  • cbrtCube root.
  • ceilNearest integer greater than or equal to argument.
  • ceilingNearest integer greater than or equal to argument (same as ceil).
  • cosCosine, argument in radians.
  • cosdCosine, argument in degrees.
  • coshHyperbolic cosine.
  • cotCotangent, argument in radians.
  • cotdCotangent, argument in degrees.
  • degreesConverts radians to degrees.
  • divInteger quotient of y / x (truncates towards zero).
  • erfError function.
  • erfcComplementary error function (1 – erf(x), without loss of precision for large inputs).
  • expExponential (e raised to the given power).
  • factorialFactorial.
  • floorNearest integer less than or equal to argument.
  • gammaGamma function.
  • gcdGreatest 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.
  • lcmLeast 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.
  • lgammaNatural logarithm of the absolute value of the gamma function.
  • lnNatural logarithm.
  • logBase 10 logarithm.
  • log10Base 10 logarithm (same as log).
  • min_scaleMinimum scale (number of fractional decimal digits) needed to represent the supplied value precisely.
  • modRemainder of y / x; available for smallint, integer, bigint, and numeric.
  • piApproximate value of π.
  • powera raised to the power of b.
  • radiansConverts degrees to radians.
  • randomReturns a random value in the range 0.0 <= x < 1.0.
  • random_normalReturns a random value from the normal distribution with the given parameters; mean defaults to 0.0 and stddev defaults to 1.0.
  • roundRounds to nearest integer.
  • scaleScale of the argument (the number of decimal digits in the fractional part).
  • setseedSets the seed for subsequent random() and random_normal() calls; argument must be between -1.0 and 1.0, inclusive.
  • signSign of the argument (-1, 0, or +1).
  • sinSine, argument in radians.
  • sindSine, argument in degrees.
  • sinhHyperbolic sine.
  • sqrtSquare root.
  • tanTangent, argument in radians.
  • tandTangent, argument in degrees.
  • tanhHyperbolic tangent.
  • trim_scaleReduces the value's scale (number of fractional decimal digits) by removing trailing zeroes.
  • truncTruncates to integer (towards zero).
  • width_bucketReturns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high.
Aggregate functions57
  • any_valueReturns an arbitrary value from the non-null input values.
  • array_aggCollects all the input values, including nulls, into an array.
  • avgTutorial Computes the average (arithmetic mean) of all the non-null input values.
  • bit_andComputes the bitwise AND of all non-null input values.
  • bit_orComputes the bitwise OR of all non-null input values.
  • bit_xorComputes the bitwise exclusive OR of all non-null input values.
  • bool_andReturns true if all non-null input values are true, otherwise false.
  • bool_orReturns true if any non-null input value is true, otherwise false.
  • corrComputes the correlation coefficient.
  • countComputes the number of input rows.
  • covar_popComputes the population covariance.
  • covar_sampComputes the sample covariance.
  • cume_distComputes the cumulative distribution, that is (number of rows preceding or peers with hypothetical row) / (total rows).
  • dense_rankTutorial Computes the rank of the hypothetical row, without gaps; this function effectively counts peer groups.
  • everyThis is the SQL standard's equivalent to bool_and.
  • GROUPINGReturns a bit mask indicating which GROUP BY expressions are not included in the current grouping set.
  • json_aggCollects all the input values, including nulls, into a JSON array.
  • json_agg_strictCollects all the input values, skipping nulls, into a JSON array.
  • json_arrayaggBehaves in the same way as json_array but as an aggregate function so it only takes one value_expression parameter.
  • json_object_aggCollects all the key/value pairs into a JSON object.
  • json_object_agg_strictCollects all the key/value pairs into a JSON object.
  • json_object_agg_uniqueCollects all the key/value pairs into a JSON object.
  • json_object_agg_unique_strictCollects all the key/value pairs into a JSON object.
  • json_objectaggBehaves like json_object, but as an aggregate function, so it only takes one key_expression and one value_expression parameter.
  • jsonb_aggCollects all the input values, including nulls, into a JSON array.
  • jsonb_agg_strictCollects all the input values, skipping nulls, into a JSON array.
  • jsonb_object_aggCollects all the key/value pairs into a JSON object.
  • jsonb_object_agg_strictCollects all the key/value pairs into a JSON object.
  • jsonb_object_agg_uniqueCollects all the key/value pairs into a JSON object.
  • jsonb_object_agg_unique_strictCollects all the key/value pairs into a JSON object.
  • maxComputes the maximum of the non-null input values.
  • minTutorial Computes the minimum of the non-null input values.
  • modeComputes the mode, the most frequent value of the aggregated argument (arbitrarily choosing the first one if there are multiple equally-frequent values).
  • percent_rankComputes the relative rank of the hypothetical row, that is (rank – 1) / (total rows – 1).
  • percentile_contComputes the continuous percentile, a value corresponding to the specified fraction within the ordered set of aggregated argument values.
  • percentile_discComputes 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.
  • range_aggComputes the union of the non-null input values.
  • range_intersect_aggComputes the intersection of the non-null input values.
  • rankTutorial Computes the rank of the hypothetical row, with gaps; that is, the row number of the first row in its peer group.
  • regr_avgxComputes the average of the independent variable, sum(X)/ N.
  • regr_avgyComputes the average of the dependent variable, sum(Y)/ N.
  • regr_countComputes the number of rows in which both inputs are non-null.
  • regr_interceptComputes the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.
  • regr_r2Computes the square of the correlation coefficient.
  • regr_slopeComputes the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.
  • regr_sxxComputes the “sum of squares” of the independent variable, sum(X ^2) – sum(X)^2/ N.
  • regr_sxyComputes the “sum of products” of independent times dependent variables, sum(X * Y) – sum(X) * sum(Y)/ N.
  • regr_syyComputes the “sum of squares” of the dependent variable, sum(Y ^2) – sum(Y)^2/ N.
  • stddevThis is a historical alias for stddev_samp.
  • stddev_popComputes the population standard deviation of the input values.
  • stddev_sampComputes the sample standard deviation of the input values.
  • string_aggTutorial Concatenates the non-null input values into a string.
  • sumTutorial Computes the sum of the non-null input values.
  • var_popComputes the population variance of the input values (square of the population standard deviation).
  • var_sampComputes the sample variance of the input values (square of the sample standard deviation).
  • varianceThis is a historical alias for var_samp.
  • xmlaggConcatenates the non-null XML input values.
Window functions11
  • cume_distReturns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows).
  • dense_rankTutorial Returns the rank of the current row, without gaps; this function effectively counts peer groups.
  • first_valueReturns value evaluated at the row that is the first row of the window frame.
  • lagReturns 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).
  • last_valueReturns value evaluated at the row that is the last row of the window frame.
  • leadReturns 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).
  • nth_valueReturns 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.
  • ntileReturns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.
  • percent_rankReturns the relative rank of the current row, that is (rank – 1) / (total partition rows – 1).
  • rankTutorial Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.
  • row_numberTutorial Returns the number of the current row within its partition, counting from 1.
Conditional expressions5
  • CASETutorial The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages.
  • COALESCETutorial Returns the first of its arguments that is not null.
  • GREATESTSelects the largest value from a list of any number of expressions.
  • LEASTSelects the smallest value from a list of any number of expressions.
  • NULLIFReturns a null value if value1 equals value2; otherwise it returns value1.
JSON functions60
  • array_to_jsonConverts an SQL array to a JSON array.
  • jsonConverts a given expression specified as text or bytea string (in UTF8 encoding) into a JSON value.
  • json_arrayConstructs 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.
  • json_array_elementsExpands the top-level JSON array into a s