pg_am
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 15)
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 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 bothi
andt
(commit 8586bf7e)
- support added for table access methods, meaning column
- 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)
- following columns removed (commit 65c5fcd3) and replaced by SQL-accessible functions (commit ed0097e4):
- PostgreSQL 9.2
- PostgreSQL 9.1
- PostgreSQL 8.4
- PostgreSQL 8.3
- column
amsearchnulls
added (commit f02a82b6)
- column
- PostgreSQL 8.2
- PostgreSQL 8.1
- PostgreSQL 7.4
- column
amvacuumcleanup
added (commit 799bc58d)
- column
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)
References
- PostgreSQL documentation: pg_am