内容

pg_am

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

pg_am 是一个 系统目录 表，用于存储有关关系访问方法的信息。

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

从下面的更改历史记录可以看出，随着需要添加新的索引属性，此目录表经历了频繁的更改。在 PostgreSQL 9.6 中，该表被大大简化，并且现在可以通过诸如 pg_index_column_has_property() 之类的函数查询索引属性。

按 PostgreSQL 版本定义

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”的评论、建议或更正 此处。