• Browse categories
• PostgreSQL releases
  • PostgreSQL 14 (dev)
  • PostgreSQL 13 (latest)
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6
  • PostgreSQL 9.5
  • PostgreSQL 9.4 (EOL)

Useful articles

Useful links

pg_aggregate

pg_aggregate is a system catalogue table storing information about aggregate functions.

Definition by PostgreSQL version

               Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
 aggfnoid         | regproc  |           | not null |
 aggkind          | "char"   |           | not null |
 aggnumdirectargs | smallint |           | not null |
 aggtransfn       | regproc  |           | not null |
 aggfinalfn       | regproc  |           | not null |
 aggcombinefn     | regproc  |           | not null |
 aggserialfn      | regproc  |           | not null |
 aggdeserialfn    | regproc  |           | not null |
 aggmtransfn      | regproc  |           | not null |
 aggminvtransfn   | regproc  |           | not null |
 aggmfinalfn      | regproc  |           | not null |
 aggfinalextra    | boolean  |           | not null |
 aggmfinalextra   | boolean  |           | not null |
 aggfinalmodify   | "char"   |           | not null |
 aggmfinalmodify  | "char"   |           | not null |
 aggsortop        | oid      |           | not null |
 aggtranstype     | oid      |           | not null |
 aggtransspace    | integer  |           | not null |
 aggmtranstype    | oid      |           | not null |
 aggmtransspace   | integer  |           | not null |
 agginitval       | text     | C         |          |
 aggminitval      | text     | C         |          |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

               Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
 aggfnoid         | regproc  |           | not null |
 aggkind          | "char"   |           | not null |
 aggnumdirectargs | smallint |           | not null |
 aggtransfn       | regproc  |           | not null |
 aggfinalfn       | regproc  |           | not null |
 aggcombinefn     | regproc  |           | not null |
 aggserialfn      | regproc  |           | not null |
 aggdeserialfn    | regproc  |           | not null |
 aggmtransfn      | regproc  |           | not null |
 aggminvtransfn   | regproc  |           | not null |
 aggmfinalfn      | regproc  |           | not null |
 aggfinalextra    | boolean  |           | not null |
 aggmfinalextra   | boolean  |           | not null |
 aggfinalmodify   | "char"   |           | not null |
 aggmfinalmodify  | "char"   |           | not null |
 aggsortop        | oid      |           | not null |
 aggtranstype     | oid      |           | not null |
 aggtransspace    | integer  |           | not null |
 aggmtranstype    | oid      |           | not null |
 aggmtransspace   | integer  |           | not null |
 agginitval       | text     | C         |          |
 aggminitval      | text     | C         |          |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

               Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
 aggfnoid         | regproc  |           | not null |
 aggkind          | "char"   |           | not null |
 aggnumdirectargs | smallint |           | not null |
 aggtransfn       | regproc  |           | not null |
 aggfinalfn       | regproc  |           | not null |
 aggcombinefn     | regproc  |           | not null |
 aggserialfn      | regproc  |           | not null |
 aggdeserialfn    | regproc  |           | not null |
 aggmtransfn      | regproc  |           | not null |
 aggminvtransfn   | regproc  |           | not null |
 aggmfinalfn      | regproc  |           | not null |
 aggfinalextra    | boolean  |           | not null |
 aggmfinalextra   | boolean  |           | not null |
 aggfinalmodify   | "char"   |           | not null |
 aggmfinalmodify  | "char"   |           | not null |
 aggsortop        | oid      |           | not null |
 aggtranstype     | oid      |           | not null |
 aggtransspace    | integer  |           | not null |
 aggmtranstype    | oid      |           | not null |
 aggmtransspace   | integer  |           | not null |
 agginitval       | text     |           |          |
 aggminitval      | text     |           |          |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

               Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
 aggfnoid         | regproc  |           | not null |
 aggkind          | "char"   |           | not null |
 aggnumdirectargs | smallint |           | not null |
 aggtransfn       | regproc  |           | not null |
 aggfinalfn       | regproc  |           | not null |
 aggcombinefn     | regproc  |           | not null |
 aggserialfn      | regproc  |           | not null |
 aggdeserialfn    | regproc  |           | not null |
 aggmtransfn      | regproc  |           | not null |
 aggminvtransfn   | regproc  |           | not null |
 aggmfinalfn      | regproc  |           | not null |
 aggfinalextra    | boolean  |           | not null |
 aggmfinalextra   | boolean  |           | not null |
 aggsortop        | oid      |           | not null |
 aggtranstype     | oid      |           | not null |
 aggtransspace    | integer  |           | not null |
 aggmtranstype    | oid      |           | not null |
 aggmtransspace   | integer  |           | not null |
 agginitval       | text     |           |          |
 aggminitval      | text     |           |          |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

     Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Modifiers
------------------+----------+-----------
 aggfnoid         | regproc  | not null
 aggkind          | "char"   | not null
 aggnumdirectargs | smallint | not null
 aggtransfn       | regproc  | not null
 aggfinalfn       | regproc  | not null
 aggcombinefn     | regproc  | not null
 aggserialfn      | regproc  | not null
 aggdeserialfn    | regproc  | not null
 aggmtransfn      | regproc  | not null
 aggminvtransfn   | regproc  | not null
 aggmfinalfn      | regproc  | not null
 aggfinalextra    | boolean  | not null
 aggmfinalextra   | boolean  | not null
 aggsortop        | oid      | not null
 aggtranstype     | oid      | not null
 aggtransspace    | integer  | not null
 aggmtranstype    | oid      | not null
 aggmtransspace   | integer  | not null
 agginitval       | text     |
 aggminitval      | text     |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

     Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Modifiers
------------------+----------+-----------
 aggfnoid         | regproc  | not null
 aggkind          | "char"   | not null
 aggnumdirectargs | smallint | not null
 aggtransfn       | regproc  | not null
 aggfinalfn       | regproc  | not null
 aggmtransfn      | regproc  | not null
 aggminvtransfn   | regproc  | not null
 aggmfinalfn      | regproc  | not null
 aggfinalextra    | boolean  | not null
 aggmfinalextra   | boolean  | not null
 aggsortop        | oid      | not null
 aggtranstype     | oid      | not null
 aggtransspace    | integer  | not null
 aggmtranstype    | oid      | not null
 aggmtransspace   | integer  | not null
 agginitval       | text     |
 aggminitval      | text     |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

     Table "pg_catalog.pg_aggregate"
      Column      |   Type   | Modifiers
------------------+----------+-----------
 aggfnoid         | regproc  | not null
 aggkind          | "char"   | not null
 aggnumdirectargs | smallint | not null
 aggtransfn       | regproc  | not null
 aggfinalfn       | regproc  | not null
 aggmtransfn      | regproc  | not null
 aggminvtransfn   | regproc  | not null
 aggmfinalfn      | regproc  | not null
 aggfinalextra    | boolean  | not null
 aggmfinalextra   | boolean  | not null
 aggsortop        | oid      | not null
 aggtranstype     | oid      | not null
 aggtransspace    | integer  | not null
 aggmtranstype    | oid      | not null
 aggmtransspace   | integer  | not null
 agginitval       | text     |
 aggminitval      | text     |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid)
    

  Table "pg_catalog.pg_aggregate"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 aggfnoid     | regproc | not null
 aggtransfn   | regproc | not null
 aggfinalfn   | regproc | not null
 aggsortop    | oid     | not null
 aggtranstype | oid     | not null
 agginitval   | text    |
Indexes:
    "pg_aggregate_fnoid_index" UNIQUE, btree (aggfnoid oid_ops)
    

Change history

• PostgreSQL 11 - following columns added:
  • aggfinalmodify (commit 4de2d4fb)
  • aggmfinalmodify (commit 4de2d4fb)
• PostgreSQL 9.6 - following columns added:
  • aggcombinefn (commit a7de3dc5)
  • aggserialfn (commit 5fe5a2ce)
  • aggdeserialfn (commit 5fe5a2ce)
• PostgreSQL 9.4 - following columns added:
  • aggkind (commit 8d65da1f)
  • aggnumdirectargs (commit 8d65da1f)
  • aggmtransfn (commit a9d9acbf)
  • aggminvtransfn (commit a9d9acbf)
  • aggmfinalfn (commit a9d9acbf)
  • aggfinalextra (commit f0fedfe8)
  • aggmfinalextra (commit f0fedfe8)
  • aggtransspace (commit 6cb86143)
  • aggmtransspace (commit a9d9acbf)
  • aggmtranstype (commit a9d9acbf)
  • aggminitval (commit a9d9acbf)
• PostgreSQL 8.1
  • column aggsortop added (commit 2e7a6889)
• PostgreSQL 7.2
  • column aggname removed
  • column aggowner removed
  • column aggbasetype removed
  • column aggfinaltype removed
  • column aggfnoid added

Examples

postgres=# SELECT * FROM pg_aggregate WHERE aggfnoid::TEXT ='pg_catalog.count' LIMIT 1\x\g\x
Expanded display is on.
-[ RECORD 1 ]----+-----------------
aggfnoid         | pg_catalog.count
aggkind          | n
aggnumdirectargs | 0
aggtransfn       | int8inc_any
aggfinalfn       | -
aggcombinefn     | int8pl
aggserialfn      | -
aggdeserialfn    | -
aggmtransfn      | int8inc_any
aggminvtransfn   | int8dec_any
aggmfinalfn      | -
aggfinalextra    | f
aggmfinalextra   | f
aggfinalmodify   | r
aggmfinalmodify  | r
aggsortop        | 0
aggtranstype     | 20
aggtransspace    | 0
aggmtranstype    | 20
aggmtransspace   | 0
agginitval       | 0
aggminitval      | 0

References

Categories