目录

pg_am

存储有关关系访问方法信息的系统目录表

pg_am 是一个 系统目录 表，其中存储了关系访问方法的信息。

在 PostgreSQL 11 及更早版本中，这些方法仅限于索引访问方法；PostgreSQL 12 增加了对表访问方法的支持。

正如下面的更改历史所示，该目录表经历了频繁的更改，因为需要添加新的索引属性。在 PostgreSQL 9.6 中，该表得到了极大的简化，现在可以通过 pg_index_column_has_property() 等函数查询索引属性。

按 PostgreSQL 版本定义

pg_am (PostgreSQL 19)

               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)

文档: pg_am

pg_am (PostgreSQL 18)

               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)

文档: pg_am

pg_am (PostgreSQL 17)

               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)

文档: pg_am

pg_am (PostgreSQL 16)

               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)

文档: pg_am

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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: 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)

文档: pg_am

变更历史

  • PostgreSQL 12
    • 支持表访问方法，这意味着 amtype 列可以包含 i 和 t（提交 8586bf7e）
  • PostgreSQL 9.6
    • 移除了以下列（提交 65c5fcd3），并用 SQL 可访问的函数替换（提交 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
    • 添加了 amhandler 列（提交 65c5fcd3）
  • PostgreSQL 9.2
    • 添加了 amsearcharray 列（提交 9e8da0f7）
    • 添加了 amcanreturn 列（初始提交 a2822fb9）
  • PostgreSQL 9.1
    • 添加了 ampredlocks 列（提交 dafaa3ef）
    • 添加了 ambuildempty 列（提交 7e2f9062）
    • 移除了 amindexnulls 列（提交 7e2f9062）
  • PostgreSQL 8.4
    • 添加了 amcanbackward 列（提交 e4fb8ff0）
    • 添加了 amkeytype 列（提交 4adc2f72）
  • PostgreSQL 8.3
    • 添加了 amsearchnulls 列（提交 f02a82b6）
  • PostgreSQL 8.2
    • 添加了 amstorage 列（提交 e5734597）
    • 添加了 amclusterable 列（提交 e5734597）
    • 移除了 amconcurrent 列（提交 09d3670d）
  • PostgreSQL 8.1
    • 添加了 amgettuple 列（提交 bf3dbb58）
    • 添加了 amgetmulti 列（提交 bf3dbb58）
    • 移除了 amowner 列（提交 bf3dbb58）
  • PostgreSQL 7.4
    • 添加了 amvacuumcleanup 列（提交 799bc58d）

注意：列表不包括 PostgreSQL 7.3 之前的任何更改。

示例

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 文档： pg_am

分类

存储, 系统目录

另请参阅

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

反馈

请在此 提交关于“pg_am”的任何评论、建议或更正。