# man > CREATE_AGGREGATE(7)

---
type: CommandReference
command: CREATE AGGREGATE
mode: man
section: 7
source: man-pages
---

## Quick Reference
- `CREATE AGGREGATE myavg (integer) (SFUNC = int_add, STYPE = int8)` — define a simple aggregate with transition function
- `CREATE AGGREGATE mymax (text) (SFUNC = text_larger, STYPE = text)` — aggregate returning maximum text value
- `CREATE AGGREGATE myavg (numeric) (SFUNC = numeric_avg_accum, STYPE = internal, FINALFUNC = numeric_avg, INITCOND = '(0,0)')` — `avg`‑like with final function
- `CREATE OR REPLACE AGGREGATE mymin (integer) (SFUNC = int4min, STYPE = integer, SORTOP = <)` — replace and enable index optimization
- `CREATE AGGREGATE mypercentile (ORDER BY double precision) (SFUNC = percentile_cont_sfunc, STYPE = internal, FINALFUNC = percentile_cont_final)` — ordered‑set aggregate
- `CREATE AGGREGATE mysum (integer) (SFUNC = int4_sum, STYPE = int8, MSFUNC = int4_sum, MINVFUNC = int4_sum_inv, MSTYPE = int8)` — moving‑aggregate for window functions
- `CREATE AGGREGATE mycount (*) (SFUNC = int8inc_any, STYPE = int8)` — zero‑argument aggregate (like `count(*)`)
- `CREATE AGGREGATE myagg (float8) (SFUNC = float8_accum, STYPE = internal, COMBINEFUNC = float8_combine, SERIALFUNC = float8_serialize, DESERIALFUNC = float8_deserialize, PARALLEL = SAFE)` — parallel‑safe aggregate with serialisation

## Name
`CREATE AGGREGATE` — define a new aggregate function

## Synopsis
sql
CREATE [ OR REPLACE ] AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc, STYPE = state_data_type
    [ , SSPACE = state_data_size ] [ , FINALFUNC = ffunc ] [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ] [ , SERIALFUNC = serialfunc ] [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ] [ , MINVFUNC = minvfunc ] [ , MSTYPE = mstate_data_type ] [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ] [ , MFINALFUNC_EXTRA ] [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ] [ , SORTOP = sort_operator ] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

CREATE [ OR REPLACE ] AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
                               ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc, STYPE = state_data_type
    [ , SSPACE = state_data_size ] [ , FINALFUNC = ffunc ] [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , INITCOND = initial_condition ] [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] [ , HYPOTHETICAL ]
)
Old syntax (not for ordered‑set aggregates):
sql
CREATE [ OR REPLACE ] AGGREGATE name (
    BASETYPE = base_type, SFUNC = sfunc, STYPE = state_data_type
    [ , SSPACE = state_data_size ] [ , FINALFUNC = ffunc ] [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ] [ , SERIALFUNC = serialfunc ] [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ] [ , MINVFUNC = minvfunc ] [ , MSTYPE = mstate_data_type ] [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ] [ , MFINALFUNC_EXTRA ] [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ] [ , SORTOP = sort_operator ]
)
## Description
`CREATE AGGREGATE` defines a new aggregate function. `CREATE OR REPLACE AGGREGATE` either creates a new aggregate or replaces an existing definition, provided the argument types, result type, number of direct arguments, and kind (ordinary, ordered‑set, hypothetical‑set) remain unchanged.

An aggregate is identified by its name and input data type(s); overloading works like ordinary functions (see [CREATE FUNCTION](http://localhost/phpMan.php/man/CREATE_FUNCTION/7)). The aggregate is created in the specified schema or, if omitted, the current schema.

A simple aggregate uses a state transition function `sfunc` and an optional final function `ffunc`. For each input row, `sfunc(current_state, new_row_values)` returns the next state. After all rows, `ffunc(final_state)` computes the result. Without a final function, the state value is returned directly. An initial condition can be supplied as a textual constant; otherwise the state starts as null.

If `sfunc` is strict: null input rows are skipped; if the initial state is null, the first non‑null row’s first argument becomes the initial state (only when `state_data_type` matches `arg_data_type`). A non‑strict `sfunc` must handle nulls itself. A strict final function returns null when the final state is null; otherwise it may return null.

Moving‑aggregate mode (for window functions with moving frame start) requires `MSFUNC`, `MINVFUNC`, `MSTYPE`. The forward function adds values, the inverse function removes them. The inverse function must not be called when no rows remain; if it returns NULL, the aggregation restarts from scratch.

Ordered‑set aggregates (synopsis with `ORDER BY`) operate on sorted groups. `HYPOTHETICAL` makes them hypothetical‑set aggregates: the last direct arguments must match the aggregated arguments’ types.

Partial aggregation (including parallel) is enabled by `COMBINEFUNC`; for `internal` state type, also provide `SERIALFUNC` and `DESERIALFUNC`. Mark the aggregate `PARALLEL SAFE` to allow parallelism. Ordered‑set aggregates do not support partial aggregation.

To create an aggregate, you need `USAGE` privilege on argument, state, and return types, and `EXECUTE` privilege on all supporting functions.

## Options
### Basic Parameters
- `name` — Name of the aggregate (optionally schema‑qualified)
- `argmode` — `IN` or `VARIADIC` (default `IN`; only the last argument can be `VARIADIC`)
- `argname` — Name of an argument (documentation only)
- `arg_data_type` — Input data type; use `*` for a zero‑argument aggregate
- `SFUNC = sfunc` — State transition function name
- `STYPE = state_data_type` — Data type of the aggregate’s state value
- `SSPACE = state_data_size` — Approximate average state size in bytes (for planner memory estimates)
- `FINALFUNC = ffunc` — Final function name (optional)
- `FINALFUNC_EXTRA` — Pass extra NULL arguments matching the aggregate’s regular arguments to the final function
- `FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }` — Declare final function behaviour (default: `READ_ONLY` for normal, `READ_WRITE` for ordered‑set)
- `INITCOND = initial_condition` — Initial state as a string constant (default NULL)
- `PARALLEL = { SAFE | RESTRICTED | UNSAFE }` — Parallel safety (default `UNSAFE`)

### Partial Aggregation Parameters
- `COMBINEFUNC = combinefunc` — Function to combine two partial states (supports parallelisation)
- `SERIALFUNC = serialfunc` — Serialise state to `bytea` (required when `STYPE = internal`)
- `DESERIALFUNC = deserialfunc` — Deserialise `bytea` back to the state type

### Moving‑Aggregate Parameters
- `MSFUNC = msfunc` — Forward state transition for moving‑aggregate mode
- `MINVFUNC = minvfunc` — Inverse state transition function
- `MSTYPE = mstate_data_type` — State data type for moving‑aggregate mode
- `MSSPACE = mstate_data_size` — Approximate average state size for moving‑aggregate mode
- `MFINALFUNC = mffunc` — Final function for moving‑aggregate mode
- `MFINALFUNC_EXTRA` — Extra dummy arguments for the moving‑aggregate final function
- `MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }` — Behaviour for the moving‑aggregate final function
- `MINITCOND = minitial_condition` — Initial state for moving‑aggregate mode (default NULL)

### Index Optimisation Parameter
- `SORTOP = sort_operator` — Sort operator (e.g., `<` for `MIN`) enabling index‑scan optimisation (single‑argument normal aggregates only)

### Ordered‑Set / Hypothetical‑Set Flags
- `HYPOTHETICAL` — Tags the aggregate as hypothetical‑set (only with `ORDER BY`). Affects parse‑time type resolution.

### Legacy Syntax
- `BASETYPE = base_type` — Input data type (old style, single parameter). Use `"ANY"` for zero‑argument.

## Examples
See [Section 38.12 of the PostgreSQL documentation](https://www.postgresql.org/docs/14/xaggr.html) for extensive examples.

### Basic aggregate (sum of integers)
sql
CREATE AGGREGATE int_sum (integer) (
    SFUNC = int4pl,
    STYPE = integer,
    INITCOND = '0'
);
### Aggregate with final function (average)
sql
CREATE AGGREGATE myavg (numeric) (
    SFUNC = numeric_avg_accum,
    STYPE = internal,
    FINALFUNC = numeric_avg,
    INITCOND = '(0,0)'
);
### Ordered‑set aggregate (percentile)
sql
CREATE AGGREGATE my_percentile (ORDER BY double precision) (
    SFUNC = percentile_cont_sfunc,
    STYPE = internal,
    FINALFUNC = percentile_cont_final
);
### Moving‑aggregate for window functions
sql
CREATE AGGREGATE moving_sum (integer) (
    SFUNC = int4_sum,
    STYPE = int8,
    MSFUNC = int4_sum,
    MINVFUNC = int4_sum_inv,
    MSTYPE = int8
);
### Parallel‑safe aggregate with serialisation
sql
CREATE AGGREGATE parallel_avg (float8) (
    SFUNC = float8_accum,
    STYPE = internal,
    FINALFUNC = float8_avg,
    COMBINEFUNC = float8_combine,
    SERIALFUNC = float8_serialize,
    DESERIALFUNC = float8_deserialize,
    PARALLEL = SAFE
);
## See Also
- [ALTER AGGREGATE](http://localhost/phpMan.php/man/ALTER_AGGREGATE/7)
- [DROP AGGREGATE](http://localhost/phpMan.php/man/DROP_AGGREGATE/7)
- [CREATE FUNCTION](http://localhost/phpMan.php/man/CREATE_FUNCTION/7)
- [PostgreSQL 14.23 Documentation](https://www.postgresql.org/docs/14/sql-createaggregate.html)