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 14)

               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_oid_index" PRIMARY KEY, btree (oid)
    "pg_am_name_index" UNIQUE CONSTRAINT, btree (amname)

Documentation: pg_am

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 commit 9e8da0f7)
    • column amcanreturn added (initial commit a2822fb9)
  • PostgreSQL 9.1
    • column ampredlocks added (commit dafaa3ef)
    • column ambuildempty added (commit 7e2f9062)
    • column amindexnulls removed (commit 7e2f9062)
  • PostgreSQL 8.4
    • column amcanbackward added (commit e4fb8ff0)
    • column amkeytype added (commit 4adc2f72)
  • PostgreSQL 8.3
    • column amsearchnulls added (commit f02a82b6)
  • PostgreSQL 8.2
    • column amstorage added (commit e5734597)
    • column amclusterable added (commit e5734597)
    • column amconcurrent removed (commit 09d3670d)
  • PostgreSQL 8.1
    • column amgettuple added (commit bf3dbb58)
    • column amgetmulti added (commit bf3dbb58)
    • column amowner removed (commit bf3dbb58)
  • PostgreSQL 7.4
    • column amvacuumcleanup added (commit 799bc58d)

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()