Contents

pg_am

A system catalogue table which stores information about relation access methods

pg_am is a system catalogue table which stores information about relation access methods.

In PostgreSQL 11 and earlier, these were restricted to index access methods; PostgreSQL 12 adds support for table access methods.

As is visible from the change history below, this catalogue table was subject to frequent changes as new index attributes needed to be added. In PostgreSQL 9.6 the table was vastly simplified, and index attributes can now be queried via functions such as pg_index_column_has_property().

Definition by PostgreSQL version

pg_am (PostgreSQL 13)

               Table "pg_catalog.pg_am"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 oid       | oid     |           | not null |
 amname    | name    |           | not null |
 amhandler | regproc |           | not null |
 amtype    | "char"  |           | not null |
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 12)

               Table "pg_catalog.pg_am"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 oid       | oid     |           | not null |
 amname    | name    |           | not null |
 amhandler | regproc |           | not null |
 amtype    | "char"  |           | not null |
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 11)

               Table "pg_catalog.pg_am"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 amname    | name    |           | not null |
 amhandler | regproc |           | not null |
 amtype    | "char"  |           | not null |
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 10)

               Table "pg_catalog.pg_am"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 amname    | name    |           | not null |
 amhandler | regproc |           | not null |
 amtype    | "char"  |           | not null |
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.6)

    Table "pg_catalog.pg_am"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 amname    | name    | not null
 amhandler | regproc | not null
 amtype    | "char"  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.5)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanorderbyop  | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amsearcharray   | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 ampredlocks     | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambuildempty    | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcanreturn     | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.4)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanorderbyop  | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amsearcharray   | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 ampredlocks     | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambuildempty    | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcanreturn     | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.3)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanorderbyop  | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amsearcharray   | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 ampredlocks     | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambuildempty    | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcanreturn     | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.2)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanorderbyop  | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amsearcharray   | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 ampredlocks     | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambuildempty    | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcanreturn     | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.1)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanorderbyop  | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 ampredlocks     | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambuildempty    | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 9.0)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amindexnulls    | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 8.4)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanbackward   | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amindexnulls    | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 amkeytype       | oid      | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetbitmap     | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 8.3)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amcanorder      | boolean  | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amindexnulls    | boolean  | not null
 amsearchnulls   | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetmulti      | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

pg_am (PostgreSQL 8.2)

        Table "pg_catalog.pg_am"
     Column      |   Type   | Modifiers
-----------------+----------+-----------
 amname          | name     | not null
 amstrategies    | smallint | not null
 amsupport       | smallint | not null
 amorderstrategy | smallint | not null
 amcanunique     | boolean  | not null
 amcanmulticol   | boolean  | not null
 amoptionalkey   | boolean  | not null
 amindexnulls    | boolean  | not null
 amstorage       | boolean  | not null
 amclusterable   | boolean  | not null
 aminsert        | regproc  | not null
 ambeginscan     | regproc  | not null
 amgettuple      | regproc  | not null
 amgetmulti      | regproc  | not null
 amrescan        | regproc  | not null
 amendscan       | regproc  | not null
 ammarkpos       | regproc  | not null
 amrestrpos      | regproc  | not null
 ambuild         | regproc  | not null
 ambulkdelete    | regproc  | not null
 amvacuumcleanup | regproc  | not null
 amcostestimate  | regproc  | not null
 amoptions       | regproc  | not null
Indexes:
    "pg_am_name_index" UNIQUE, btree (amname)
    "pg_am_oid_index" UNIQUE, btree (oid)

Documentation: pg_am

Change history

  • PostgreSQL 12
    • support added for table access methods,meaning column amtype can contain both i and t (commit 8586bf7e)
  • PostgreSQL 9.6
    • following columns removed (commit 65c5fcd3) and replaced by SQL-accessible functions (commit ed0097e4):
      • amstrategies
      • amsupport
      • amcanorder
      • amcanorderbyop
      • amcanbackward
      • amcanunique
      • amcanmulticol
      • amoptionalkey
      • amsearcharray
      • amsearchnulls
      • amstorage
      • amclusterable
      • ampredlocks
      • amkeytype
      • aminsert
      • ambeginscan
      • amgettuple
      • amgetbitmap
      • amrescan
      • amendscan
      • ammarkpos
      • amrestrpos
      • ambuild
      • ambuildempty
      • ambulkdelete
      • amvacuumcleanup
      • amcanreturn
      • amcostestimate
      • amoptions
    • column amhandler added (commit 65c5fcd3)
  • PostgreSQL 9.2
    • column amsearcharray added
    • column amcanreturn added
  • PostgreSQL 9.1
    • column ampredlocks added
    • column ambuildempty added
  • PostgreSQL 8.4
    • column amcanbackward added
    • column amkeytype added
  • PostgreSQL 8.3
    • column amsearchnulls added
  • PostgreSQL 8.2
    • column amstorage added
    • column amclusterable added
    • column amconcurrent removed
  • PostgreSQL 8.1
    • column amgettuple added
    • column amgetmulti added
    • column amowner removed
  • PostgreSQL 7.4
    • column amvacuumcleanup added

Note: list does not include any changes prior to PostgreSQL 7.3

Examples

Default values in PostgreSQL 12:

postgres=# SELECT * FROM pg_am;
 oid  | amname |      amhandler       | amtype
------+--------+----------------------+--------
    2 | heap   | heap_tableam_handler | t
  403 | btree  | bthandler            | i
  405 | hash   | hashhandler          | i
  783 | gist   | gisthandler          | i
 2742 | gin    | ginhandler           | i
 4000 | spgist | spghandler           | i
 3580 | brin   | brinhandler          | i
(7 rows)
  • PostgreSQL documentation: pg_am

Categories

Storage, System catalogue

See also

default_table_access_method, pg_amop, pg_amproc, pg_index_column_has_property(), pg_index_has_property(), pg_indexam_has_property()