> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE AGGREGATE

## Syntax

```sql theme={null}
create_aggregate ::= CREATE AGGREGATE aggregate_name ( aggregate_signature ) (
                        SFUNC = sfunc,
                        STYPE = state_type
                        [ , option [, ...] ]
                     )

aggregate_name ::= [schema.]name

aggregate_signature ::= * | arg_type [, ...] | [ arg_type [, ...] ] ORDER BY arg_type [, ...]

option ::= SSPACE = state_space
         | 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_type
         | MSSPACE = mstate_space
         | MFINALFUNC = mffunc
         | MFINALFUNC_EXTRA
         | MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE }
         | MINITCOND = minitial_condition
         | SORTOP = sort_operator
         | PARALLEL = { SAFE | RESTRICTED | UNSAFE }
         | HYPOTHETICAL
```

pgschema understands the following `CREATE AGGREGATE` features:

* **Schema-qualified names**: Aggregates can be defined in specific schemas, and overloaded aggregates (same name, different argument types) are tracked independently.
* **Transition and final functions**: `SFUNC`/`STYPE` (required), `FINALFUNC`, `FINALFUNC_EXTRA`, and `FINALFUNC_MODIFY`.
* **Initial condition**: `INITCOND`.
* **Parallel aggregation**: `COMBINEFUNC`, `SERIALFUNC`, `DESERIALFUNC`, and `PARALLEL = SAFE | RESTRICTED`.
* **Moving-aggregate support** (for window frames): `MSFUNC`, `MINVFUNC`, `MSTYPE`, `MSSPACE`, `MFINALFUNC`, `MFINALFUNC_EXTRA`, `MFINALFUNC_MODIFY`, and `MINITCOND`.
* **State space hints**: `SSPACE` and `MSSPACE`.
* **Sort operator**: `SORTOP` (for normal aggregates, e.g. `MIN`/`MAX`-style aggregates).
* **Ordered-set and hypothetical-set aggregates**: the `... ORDER BY ...` signature is preserved, and `HYPOTHETICAL` is emitted for hypothetical-set aggregates.
* **Comments**: `COMMENT ON AGGREGATE`.

Support-function references (`SFUNC`, `FINALFUNC`, `COMBINEFUNC`, ...) are schema-qualified only when they live in a different schema than the aggregate itself.

## Canonical Format

When generating migration SQL, pgschema produces aggregates in the following canonical format:

```sql theme={null}
CREATE AGGREGATE [schema.]aggregate_name(aggregate_signature) (
    SFUNC = sfunc,
    STYPE = state_type[,
    ...additional options in pg_dump order...]
);
```

**Key characteristics of the canonical format:**

* `SFUNC` and `STYPE` are always emitted; every other option is emitted only when it differs from its PostgreSQL default, in the same order pg\_dump uses.
* A zero-argument aggregate renders its signature as `(*)`.
* PostgreSQL has no `ALTER AGGREGATE` for the defining properties, so any change to an aggregate's definition is expressed as `DROP AGGREGATE` + `CREATE AGGREGATE`. A comment-only change emits just `COMMENT ON AGGREGATE`.
* For DROP operations: `DROP AGGREGATE IF EXISTS aggregate_name(argument_types);` — the signature contains only the argument types (the identity signature).
* Aggregates are created after their support functions and all tables, and before views that reference them; they are dropped before their support functions.