pg_index
pg_index
is a system catalogue table containing index definiitions.
pg_index
has been present in all PostgreSQL versions.
psql commands
\di
lists all indexes\d [PATTERN]
describes an index
Definition by PostgreSQL version
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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: pg_index
Change history
Note: any changes prior to PostgreSQL 7.2 are not reflected in this list.
- PostgreSQL 15
- column
indnullsnotdistinct
added (commit 94aa7cc5)
- column
- PostgreSQL 11
- column
indnkeyatts
added (commit 8224de4f)
- column
- PostgreSQL 9.4
- column
indisreplident
added (commit 07cacba9)
- column
- PostgreSQL 9.3
- column
indislive
added (commit 3c840464)
- column
- PostgreSQL 9.1
- PostgreSQL 9.0
- column
indimmediate
added (commit 25d9bf2e)
- column
- PostgreSQL 8.3
- PostgreSQL 8.2
- column
indisvalid
added (commit e093dcdd)
- column
- PostgreSQL 8.1
- column order changed (commit 70c9763d)
- PostgreSQL 7.4
- PostgreSQL 7.2
References
- PostgreSQL documentation: pg_index