pg_index
是一个包含索引定义的系统目录表。
pg_index
出现在所有 PostgreSQL 版本中。
psql 命令
\di
列出所有索引\d [PATTERN]
描述一个索引
用法
pg_indexes
提供了 pg_index
内容的一部分的人类可读视图。
按 PostgreSQL 版本定义
pg_index (PostgreSQL 18)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indnullsnotdistinct | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 17)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indnullsnotdistinct | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 16)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indnullsnotdistinct | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 15)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indnullsnotdistinct | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 14)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ----------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" PRIMARY KEY, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 13)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ----------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 12)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ----------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | C | | indpred | pg_node_tree | C | | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 11)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ----------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indnkeyatts | smallint | | not null | indisunique | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | | | indpred | pg_node_tree | | | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 10)
Table "pg_catalog.pg_index" Column | Type | Collation | Nullable | Default ----------------+--------------+-----------+----------+--------- indexrelid | oid | | not null | indrelid | oid | | not null | indnatts | smallint | | not null | indisunique | boolean | | not null | indisprimary | boolean | | not null | indisexclusion | boolean | | not null | indimmediate | boolean | | not null | indisclustered | boolean | | not null | indisvalid | boolean | | not null | indcheckxmin | boolean | | not null | indisready | boolean | | not null | indislive | boolean | | not null | indisreplident | boolean | | not null | indkey | int2vector | | not null | indcollation | oidvector | | not null | indclass | oidvector | | not null | indoption | int2vector | | not null | indexprs | pg_node_tree | | | indpred | pg_node_tree | | | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.6)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+--------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisexclusion | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indislive | boolean | not null indisreplident | boolean | not null indkey | int2vector | not null indcollation | oidvector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | pg_node_tree | indpred | pg_node_tree | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.5)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+--------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisexclusion | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indislive | boolean | not null indisreplident | boolean | not null indkey | int2vector | not null indcollation | oidvector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | pg_node_tree | indpred | pg_node_tree | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.4)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+--------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisexclusion | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indislive | boolean | not null indisreplident | boolean | not null indkey | int2vector | not null indcollation | oidvector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | pg_node_tree | indpred | pg_node_tree | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.3)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+--------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisexclusion | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indislive | boolean | not null indkey | int2vector | not null indcollation | oidvector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | pg_node_tree | indpred | pg_node_tree | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.2)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+--------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisexclusion | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indkey | int2vector | not null indcollation | oidvector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | pg_node_tree | indpred | pg_node_tree | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.1)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+--------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisexclusion | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indkey | int2vector | not null indcollation | oidvector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | pg_node_tree | indpred | pg_node_tree | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 9.0)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indimmediate | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indkey | int2vector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | text | indpred | text | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 8.4)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indkey | int2vector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | text | indpred | text | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 8.3)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indcheckxmin | boolean | not null indisready | boolean | not null indkey | int2vector | not null indclass | oidvector | not null indoption | int2vector | not null indexprs | text | indpred | text | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
pg_index (PostgreSQL 8.2)
Table "pg_catalog.pg_index" Column | Type | Modifiers ----------------+------------+----------- indexrelid | oid | not null indrelid | oid | not null indnatts | smallint | not null indisunique | boolean | not null indisprimary | boolean | not null indisclustered | boolean | not null indisvalid | boolean | not null indkey | int2vector | not null indclass | oidvector | not null indexprs | text | indpred | text | Indexes: "pg_index_indexrelid_index" UNIQUE, btree (indexrelid) "pg_index_indrelid_index" btree (indrelid)
文档: pg_index
更改历史记录
注意:此列表中未反映PostgreSQL 7.2 之前的任何更改。
- PostgreSQL 18
- 添加了对 TOAST 表支持的规定 (提交 b52c4fc3)
- PostgreSQL 15
- 添加了列
indnullsnotdistinct
(提交 94aa7cc5)
- 添加了列
- PostgreSQL 11
- 添加了列
indnkeyatts
(提交 8224de4f)
- 添加了列
- PostgreSQL 9.4
- 添加了列
indisreplident
(提交 07cacba9)
- 添加了列
- PostgreSQL 9.3
- 添加了列
indislive
(提交 3c840464)
- 添加了列
- PostgreSQL 9.1
- PostgreSQL 9.0
- 添加了列
indimmediate
(提交 25d9bf2e)
- 添加了列
- PostgreSQL 8.3
- PostgreSQL 8.2
- 添加了列
indisvalid
(提交 e093dcdd)
- 添加了列
- PostgreSQL 8.1
- 更改了列顺序 (提交 70c9763d)
- PostgreSQL 7.4
- PostgreSQL 7.2
示例
示例 pg_index
条目
postgres=# CREATE TABLE foo (id INT PRIMARY KEY, val TEXT); CREATE TABLE postgres=# SELECT * FROM pg_index WHERE indexrelid = 'foo_pkey'::regclass; -[ RECORD 1 ]-------+------ indexrelid | 16398 indrelid | 16393 indnatts | 1 indnkeyatts | 1 indisunique | t indnullsnotdistinct | f indisprimary | t indisexclusion | f indimmediate | t indisclustered | f indisvalid | t indcheckxmin | f indisready | t indislive | t indisreplident | f indkey | 1 indcollation | 0 indclass | 1978 indoption | 0 indexprs | indpred |
参考文献
- PostgreSQL 文档: pg_index