8th November 2018: PostgreSQL 11.1, 10.6, 9.6.11, 9.5.15, 9.4.20, and 9.3.25 Released!

Development Versions:
devel

Aggregate functions in PostgreSQL are defined in terms of *state values* and *state
transition functions*. That is, an aggregate operates using a
state value that is updated as each successive input row is
processed. To define a new aggregate function, one selects a data
type for the state value, an initial value for the state, and a
state transition function. The state transition function takes the
previous state value and the aggregate's input value(s) for the
current row, and returns a new state value. A *final function* can also be specified, in case the
desired result of the aggregate is different from the data that
needs to be kept in the running state value. The final function
takes the ending state value and returns whatever is wanted as the
aggregate result. In principle, the transition and final functions
are just ordinary functions that could also be used outside the
context of the aggregate. (In practice, it's often helpful for
performance reasons to create specialized transition functions that
can only work when called as part of an aggregate.)

Thus, in addition to the argument and result data types seen by a user of the aggregate, there is an internal state-value data type that might be different from both the argument and result types.

If we define an aggregate that does not use a final function, we
have an aggregate that computes a running function of the column
values from each row. `sum`

is an
example of this kind of aggregate. `sum`

starts at zero and always adds the current
row's value to its running total. For example, if we want to make a
`sum`

aggregate to work on a data type
for complex numbers, we only need the addition function for that
data type. The aggregate definition would be:

CREATE AGGREGATE sum (complex) ( sfunc = complex_add, stype = complex, initcond = '(0,0)' );

which we might use like this:

SELECT sum(a) FROM test_complex; sum ----------- (34,53.9)

(Notice that we are relying on function overloading: there is
more than one aggregate named `sum`

,
but PostgreSQL can figure out
which kind of sum applies to a column of type `complex`

.)

The above definition of `sum`

will
return zero (the initial state value) if there are no nonnull input
values. Perhaps we want to return null in that case instead — the
SQL standard expects `sum`

to behave
that way. We can do this simply by omitting the `initcond`

phrase, so that the initial state value
is null. Ordinarily this would mean that the `sfunc`

would need to check for a null state-value
input. But for `sum`

and some other
simple aggregates like `max`

and
`min`

, it is sufficient to insert the
first nonnull input value into the state variable and then start
applying the transition function at the second nonnull input value.
PostgreSQL will do that
automatically if the initial state value is null and the transition
function is marked “strict” (i.e., not to be called for null
inputs).

Another bit of default behavior for a “strict” transition function is that the previous state value is retained unchanged whenever a null input value is encountered. Thus, null values are ignored. If you need some other behavior for null inputs, do not declare your transition function as strict; instead code it to test for null inputs and do whatever is needed.

`avg`

(average) is a more complex
example of an aggregate. It requires two pieces of running state:
the sum of the inputs and the count of the number of inputs. The
final result is obtained by dividing these quantities. Average is
typically implemented by using an array as the state value. For
example, the built-in implementation of `avg(float8)`

looks like:

CREATE AGGREGATE avg (float8) ( sfunc = float8_accum, stype = float8[], finalfunc = float8_avg, initcond = '{0,0,0}' );

`float8_accum`

requires a
three-element array, not just two elements, because it accumulates
the sum of squares as well as the sum and count of the inputs. This
is so that it can be used for some other aggregates as well as
`avg`

.

Aggregate function calls in SQL allow `DISTINCT`

and `ORDER BY`

options that control which rows are fed to the aggregate's
transition function and in what order. These options are
implemented behind the scenes and are not the concern of the
aggregate's support functions.

For further details see the CREATE AGGREGATE command.

Aggregate functions can optionally support *moving-aggregate mode*, which allows substantially
faster execution of aggregate functions within windows with moving
frame starting points. (See Section 3.5 and Section 4.2.8 for
information about use of aggregate functions as window functions.)
The basic idea is that in addition to a normal “forward” transition
function, the aggregate provides an *inverse
transition function*, which allows rows to be removed from the
aggregate's running state value when they exit the window frame.
For example a `sum`

aggregate, which
uses addition as the forward transition function, would use
subtraction as the inverse transition function. Without an inverse
transition function, the window function mechanism must recalculate
the aggregate from scratch each time the frame starting point
moves, resulting in run time proportional to the number of input
rows times the average frame length. With an inverse transition
function, the run time is only proportional to the number of input
rows.

The inverse transition function is passed the current state value and the aggregate input value(s) for the earliest row included in the current state. It must reconstruct what the state value would have been if the given input row had never been aggregated, but only the rows following it. This sometimes requires that the forward transition function keep more state than is needed for plain aggregation mode. Therefore, the moving-aggregate mode uses a completely separate implementation from the plain mode: it has its own state data type, its own forward transition function, and its own final function if needed. These can be the same as the plain mode's data type and functions, if there is no need for extra state.

As an example, we could extend the `sum`

aggregate given above to support
moving-aggregate mode like this:

CREATE AGGREGATE sum (complex) ( sfunc = complex_add, stype = complex, initcond = '(0,0)', msfunc = complex_add, minvfunc = complex_sub, mstype = complex, minitcond = '(0,0)' );

The parameters whose names begin with `m`

define the moving-aggregate implementation.
Except for the inverse transition function `minvfunc`

, they correspond to the plain-aggregate
parameters without `m`

.

The forward transition function for moving-aggregate mode is not
allowed to return null as the new state value. If the inverse
transition function returns null, this is taken as an indication
that the inverse function cannot reverse the state calculation for
this particular input, and so the aggregate calculation will be
redone from scratch for the current frame starting position. This
convention allows moving-aggregate mode to be used in situations
where there are some infrequent cases that are impractical to
reverse out of the running state value. The inverse transition
function can “punt” on these cases, and yet still come out
ahead so long as it can work for most cases. As an example, an
aggregate working with floating-point numbers might choose to punt
when a `NaN`

(not a number) input has to
be removed from the running state value.

When writing moving-aggregate support functions, it is important
to be sure that the inverse transition function can reconstruct the
correct state value exactly. Otherwise there might be user-visible
differences in results depending on whether the moving-aggregate
mode is used. An example of an aggregate for which adding an
inverse transition function seems easy at first, yet where this
requirement cannot be met is `sum`

over
`float4`

or `float8`

inputs. A naive declaration of `sum(`

could
be`float8`

)

CREATE AGGREGATE unsafe_sum (float8) ( stype = float8, sfunc = float8pl, mstype = float8, msfunc = float8pl, minvfunc = float8mi );

This aggregate, however, can give wildly different results than it would have without the inverse transition function. For example, consider

SELECT unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM (VALUES (1, 1.0e20::float8), (2, 1.0::float8)) AS v (n,x);

This query returns `0`

as its second
result, rather than the expected answer of `1`

. The cause is the limited precision of
floating-point values: adding `1`

to
`1e20`

results in `1e20`

again, and so subtracting `1e20`

from that yields `0`

, not `1`

. Note that
this is a limitation of floating-point arithmetic in general, not a
limitation of PostgreSQL.

Aggregate functions can use polymorphic state transition functions or final functions, so that the same functions can be used to implement multiple aggregates. See Section 37.2.5 for an explanation of polymorphic functions. Going a step further, the aggregate function itself can be specified with polymorphic input type(s) and state type, allowing a single aggregate definition to serve for multiple input data types. Here is an example of a polymorphic aggregate:

CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' );

Here, the actual state type for any given aggregate call is the
array type having the actual input type as elements. The behavior
of the aggregate is to concatenate all the inputs into an array of
that type. (Note: the built-in aggregate `array_agg`

provides similar functionality, with
better performance than this definition would have.)

Here's the output using two different actual data types as arguments:

SELECT attrelid::regclass, array_accum(attname) FROM pg_attribute WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass GROUP BY attrelid; attrelid | array_accum ---------------+--------------------------------------- pg_tablespace | {spcname,spcowner,spcacl,spcoptions} (1 row) SELECT attrelid::regclass, array_accum(atttypid::regtype) FROM pg_attribute WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass GROUP BY attrelid; attrelid | array_accum ---------------+--------------------------- pg_tablespace | {name,oid,aclitem[],text[]} (1 row)

Ordinarily, an aggregate function with a polymorphic result type
has a polymorphic state type, as in the above example. This is
necessary because otherwise the final function cannot be declared
sensibly: it would need to have a polymorphic result type but no
polymorphic argument type, which ```
CREATE
FUNCTION
```

will reject on the grounds that the result type
cannot be deduced from a call. But sometimes it is inconvenient to
use a polymorphic state type. The most common case is where the
aggregate support functions are to be written in C and the state
type should be declared as `internal`

because there is no SQL-level equivalent for it. To address this
case, it is possible to declare the final function as taking extra
“dummy”
arguments that match the input arguments of the aggregate. Such
dummy arguments are always passed as null values since no specific
value is available when the final function is called. Their only
use is to allow a polymorphic final function's result type to be
connected to the aggregate's input type(s). For example, the
definition of the built-in aggregate `array_agg`

is equivalent to

CREATE FUNCTION array_agg_transfn(internal, anynonarray) RETURNS internal ...; CREATE FUNCTION array_agg_finalfn(internal, anynonarray) RETURNS anyarray ...; CREATE AGGREGATE array_agg (anynonarray) ( sfunc = array_agg_transfn, stype = internal, finalfunc = array_agg_finalfn, finalfunc_extra );

Here, the `finalfunc_extra`

option
specifies that the final function receives, in addition to the
state value, extra dummy argument(s) corresponding to the
aggregate's input argument(s). The extra `anynonarray`

argument allows the declaration of
`array_agg_finalfn`

to be valid.

An aggregate function can be made to accept a varying number of
arguments by declaring its last argument as a `VARIADIC`

array, in much the same fashion as for
regular functions; see Section 37.4.5.
The aggregate's transition function(s) must have the same array
type as their last argument. The transition function(s) typically
would also be marked `VARIADIC`

, but
this is not strictly required.

Variadic aggregates are easily misused in connection with the
`ORDER BY`

option (see Section 4.2.7), since
the parser cannot tell whether the wrong number of actual arguments
have been given in such a combination. Keep in mind that everything
to the right of `ORDER BY`

is a sort
key, not an argument to the aggregate. For example, in

SELECT myaggregate(a ORDER BY a, b, c) FROM ...

the parser will see this as a single aggregate function argument and three sort keys. However, the user might have intended

SELECT myaggregate(a, b, c ORDER BY a) FROM ...

If `myaggregate`

is variadic, both
these calls could be perfectly valid.

For the same reason, it's wise to think twice before creating aggregate functions with the same names and different numbers of regular arguments.

The aggregates we have been describing so far are “normal” aggregates.
PostgreSQL also supports
*ordered-set aggregates*, which differ
from normal aggregates in two key ways. First, in addition to
ordinary aggregated arguments that are evaluated once per input
row, an ordered-set aggregate can have “direct” arguments that
are evaluated only once per aggregation operation. Second, the
syntax for the ordinary aggregated arguments specifies a sort
ordering for them explicitly. An ordered-set aggregate is usually
used to implement a computation that depends on a specific row
ordering, for instance rank or percentile, so that the sort
ordering is a required aspect of any call. For example, the
built-in definition of `percentile_disc`

is equivalent to:

CREATE FUNCTION ordered_set_transition(internal, anyelement) RETURNS internal ...; CREATE FUNCTION percentile_disc_final(internal, float8, anyelement) RETURNS anyelement ...; CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement) ( sfunc = ordered_set_transition, stype = internal, finalfunc = percentile_disc_final, finalfunc_extra );

This aggregate takes a `float8`

direct
argument (the percentile fraction) and an aggregated input that can
be of any sortable data type. It could be used to obtain a median
household income like this:

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households; percentile_disc ----------------- 50489

Here, `0.5`

is a direct argument; it
would make no sense for the percentile fraction to be a value
varying across rows.

Unlike the case for normal aggregates, the sorting of input rows
for an ordered-set aggregate is *not* done behind the scenes, but is the
responsibility of the aggregate's support functions. The typical
implementation approach is to keep a reference to a “tuplesort” object in the
aggregate's state value, feed the incoming rows into that object,
and then complete the sorting and read out the data in the final
function. This design allows the final function to perform special
operations such as injecting additional “hypothetical” rows into
the data to be sorted. While normal aggregates can often be
implemented with support functions written in PL/pgSQL or another PL language, ordered-set
aggregates generally have to be written in C, since their state
values aren't definable as any SQL data type. (In the above
example, notice that the state value is declared as type
`internal`

— this is typical.)

The state transition function for an ordered-set aggregate
receives the current state value plus the aggregated input values
for each row, and returns the updated state value. This is the same
definition as for normal aggregates, but note that the direct
arguments (if any) are not provided. The final function receives
the last state value, the values of the direct arguments if any,
and (if `finalfunc_extra`

is specified)
null values corresponding to the aggregated input(s). As with
normal aggregates, `finalfunc_extra`

is
only really useful if the aggregate is polymorphic; then the extra
dummy argument(s) are needed to connect the final function's result
type to the aggregate's input type(s).

Currently, ordered-set aggregates cannot be used as window functions, and therefore there is no need for them to support moving-aggregate mode.

Optionally, an aggregate function can support *partial aggregation*. The idea of partial
aggregation is to run the aggregate's state transition function
over different subsets of the input data independently, and then to
combine the state values resulting from those subsets to produce
the same state value that would have resulted from scanning all the
input in a single operation. This mode can be used for parallel
aggregation by having different worker processes scan different
portions of a table. Each worker produces a partial state value,
and at the end those state values are combined to produce a final
state value. (In the future this mode might also be used for
purposes such as combining aggregations over local and remote
tables; but that is not implemented yet.)

To support partial aggregation, the aggregate definition must
provide a *combine function*, which takes
two values of the aggregate's state type (representing the results
of aggregating over two subsets of the input rows) and produces a
new value of the state type, representing what the state would have
been after aggregating over the combination of those sets of rows.
It is unspecified what the relative order of the input rows from
the two sets would have been. This means that it's usually
impossible to define a useful combine function for aggregates that
are sensitive to input row order.

As simple examples, `MAX`

and
`MIN`

aggregates can be made to support
partial aggregation by specifying the combine function as the same
greater-of-two or lesser-of-two comparison function that is used as
their transition function. `SUM`

aggregates just need an addition function as combine function.
(Again, this is the same as their transition function, unless the
state value is wider than the input data type.)

The combine function is treated much like a transition function
that happens to take a value of the state type, not of the
underlying input type, as its second argument. In particular, the
rules for dealing with null values and strict functions are
similar. Also, if the aggregate definition specifies a non-null
`initcond`

, keep in mind that that will
be used not only as the initial state for each partial aggregation
run, but also as the initial state for the combine function, which
will be called to combine each partial result into that state.

If the aggregate's state type is declared as `internal`

, it is the combine function's responsibility
that its result is allocated in the correct memory context for
aggregate state values. This means in particular that when the
first input is `NULL`

it's invalid to
simply return the second input, as that value will be in the wrong
context and will not have sufficient lifespan.

When the aggregate's state type is declared as `internal`

, it is usually also appropriate for the
aggregate definition to provide a *serialization function* and a *deserialization function*, which allow such a state
value to be copied from one process to another. Without these
functions, parallel aggregation cannot be performed, and future
applications such as local/remote aggregation will probably not
work either.

A serialization function must take a single argument of type
`internal`

and return a result of type
`bytea`

, which represents the state value
packaged up into a flat blob of bytes. Conversely, a
deserialization function reverses that conversion. It must take two
arguments of types `bytea`

and `internal`

, and return a result of type `internal`

. (The second argument is unused and is
always zero, but it is required for type-safety reasons.) The
result of the deserialization function should simply be allocated
in the current memory context, as unlike the combine function's
result, it is not long-lived.

Worth noting also is that for an aggregate to be executed in
parallel, the aggregate itself must be marked `PARALLEL SAFE`

. The parallel-safety markings on its
support functions are not consulted.

A function written in C can detect that it is being called as an
aggregate support function by calling `AggCheckCallContext`

, for example:

if (AggCheckCallContext(fcinfo, NULL))

One reason for checking this is that when it is true for a
transition function, the first input must be a temporary state
value and can therefore safely be modified in-place rather than
allocating a new copy. See `int8inc()`

for an example. (This is the *only* case where it is safe for a
function to modify a pass-by-reference input. In particular, final
functions for normal aggregates must not modify their inputs in any
case, because in some cases they will be re-executed on the same
final state value.)

The second argument of `AggCheckCallContext`

can be used to retrieve the
memory context in which aggregate state values are being kept. This
is useful for transition functions that wish to use “expanded” objects (see
Section 37.11.1) as
their state values. On first call, the transition function should
return an expanded object whose memory context is a child of the
aggregate state context, and then keep returning the same expanded
object on subsequent calls. See `array_append()`

for an example. (`array_append()`

is not the transition function of
any built-in aggregate, but it is written to behave efficiently
when used as transition function of a custom aggregate.)

Another support routine available to aggregate functions written
in C is `AggGetAggref`

, which returns
the `Aggref`

parse node that defines the
aggregate call. This is mainly useful for ordered-set aggregates,
which can inspect the substructure of the `Aggref`

node to find out what sort ordering they
are supposed to implement. Examples can be found in `orderedsetaggs.c`

in the PostgreSQL source code.

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.