PostgreSQL: Aggregate Functions

I kind of skipped over the functions/operators section of the PostgreSQL documentation when reading it. I want to review the sections that I think would be useful to me.

Date Created:
1 97

References


Notes

Aggregate functions compute a single result from a set of unique values.

Function

Description

array_agg(expression)

input values, including nulls, concatenated into array

avg(expression)

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

count(expression)

number of input rows for which the value of expression is not null

every(expression)

the bitwise AND of all non-null input values, or null if none

json_agg(expression)

aggregate values, including nulls, as a JSON array

json_object_agg(name,value)

aggregates name/value pairs as a JSON object; values can be null, but not names

max(expression)

maximum value of expression across all non-null input values

min(expression)

minimum value of expression across all non-null input values

string_agg(expression,delimeter)

non-null input values concatenated into a string, separated by delimiter

sum(expression)

sum of expression across all non-null input values

xmlagg(expression)

concatenation of non-null XML values

It should be noted that except for count, these functions return a null value when no rows are selected. The coalesce function can be used to substitute zero or an empty array for null when necessary.

The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg string_agg, and xml_agg, as well as similar user-defined functions, produce meaningfully different results depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregating call. Alternatively, supplying the input values from a sorted subquery will usually work.

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

This syntax is not allowed in the SQL standard, and is not portable to other database systems.

Comments

You can read more about how comments are sorted in this blog post.

User Comments