pg_aggregate — PostgreSQL system catalog

The PostgreSQL pg_aggregate system catalog: full column reference (names, types, descriptions), catalog relationships and version support.

The Sev-1 Admin
The Sev-1 Admin  ·  3 min read

Summary

The catalog pg_aggregate stores information about aggregate functions. An aggregate function is a function that operates on a set of values (typically one column from each row that matches a query condition) and returns a single value computed from all these values. Typical aggregate functions are sum, count, and max. Each entry in pg_aggregate is an extension of an entry in pg_proc. The pg_proc entry carries the aggregate’s name, input and output data types, and other information that is similar to ordinary functions.

(Description quoted from the official PostgreSQL documentation.)

Columns

The pg_aggregate system catalog exposes the following columns (names, types and descriptions are taken verbatim from the PostgreSQL documentation):

  • aggfnoid regproc references pg_proc.oid
    pg_proc OID of the aggregate function
  • aggkind char
    Aggregate kind: n for “normal” aggregates, o for “ordered-set” aggregates, or h for “hypothetical-set” aggregates
  • aggnumdirectargs int2
    Number of direct (non-aggregated) arguments of an ordered-set or hypothetical-set aggregate, counting a variadic array as one argument. If equal to pronargs, the aggregate must be variadic and the variadic array describes the aggregated arguments as well as the final direct arguments. Always zero for normal aggregates.
  • aggtransfn regproc references pg_proc.oid
    Transition function
  • aggfinalfn regproc references pg_proc.oid
    Final function (zero if none)
  • aggcombinefn regproc references pg_proc.oid
    Combine function (zero if none)
  • aggserialfn regproc references pg_proc.oid
    Serialization function (zero if none)
  • aggdeserialfn regproc references pg_proc.oid
    Deserialization function (zero if none)
  • aggmtransfn regproc references pg_proc.oid
    Forward transition function for moving-aggregate mode (zero if none)
  • aggminvtransfn regproc references pg_proc.oid
    Inverse transition function for moving-aggregate mode (zero if none)
  • aggmfinalfn regproc references pg_proc.oid
    Final function for moving-aggregate mode (zero if none)
  • aggfinalextra bool
    True to pass extra dummy arguments to aggfinalfn
  • aggmfinalextra bool
    True to pass extra dummy arguments to aggmfinalfn
  • aggfinalmodify char
    Whether aggfinalfn modifies the transition state value: r if it is read-only, s if the aggtransfn cannot be applied after the aggfinalfn, or w if it writes on the value
  • aggmfinalmodify char
    Like aggfinalmodify, but for the aggmfinalfn
  • aggsortop oid references pg_operator.oid
    Associated sort operator (zero if none)
  • aggtranstype oid references pg_type.oid
    Data type of the aggregate function’s internal transition (state) data
  • aggtransspace int4
    Approximate average size (in bytes) of the transition state data. A positive value provides an estimate; zero means to use a default estimate. A negative value indicates the state data can grow unboundedly in size, such as when the aggregate accumulates input rows (e.g., array_agg, string_agg).
  • aggmtranstype oid references pg_type.oid
    Data type of the aggregate function’s internal transition (state) data for moving-aggregate mode (zero if none)
  • aggmtransspace int4
    Approximate average size (in bytes) of the transition state data for moving-aggregate mode, or zero to use a default estimate
  • agginitval text
    The initial value of the transition state. This is a text field containing the initial value in its external string representation. If this field is null, the transition state value starts out null.
  • aggminitval text
    The initial value of the transition state for moving-aggregate mode. This is a text field containing the initial value in its external string representation. If this field is null, the transition state value starts out null.

Related catalogs

This object references the following other system catalogs:

Version applicability

Present in PostgreSQL 17, 18, 19 (verified against each release’s documentation). This is a long-standing system object that also exists in earlier PostgreSQL releases.

Related & references

Reference: PostgreSQL documentation — pg_aggregate.