pg_index

A system table containing index definitions

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)
PostgreSQL 11
- column indnkeyatts added (commit 8224de4f)
PostgreSQL 9.4
- column indisreplident added (commit 07cacba9)
PostgreSQL 9.3
- column indislive added (commit 3c840464)
PostgreSQL 9.1
- column indisexclusion added (commit bd1ad1b0)
- column indcollation added (commit 414c5a2e)
PostgreSQL 9.0
- column indimmediate added (commit 25d9bf2e)
PostgreSQL 8.3
- column indcheckxmin added (commit 282d2a03)
- column indisready added (commit 282d2a03)
- column indoption added (commit 44317582)
PostgreSQL 8.2
- column indisvalid added (commit e093dcdd)
PostgreSQL 8.1
- column order changed (commit 70c9763d)
PostgreSQL 7.4
- column indproc removed (commit fc8d970c)
- column indreference removed (commit fc8d970c)
- column indnatts added (commit fc8d970c)
- column indexprs added (commit fc8d970c)
PostgreSQL 7.2
- column indhaskeytype removed (commit f933766b)
- column indislossy removed (commit f933766b)

References

PostgreSQL documentation: pg_index

Version charts

Useful overviews

Useful links

Contents

pg_index

psql commands

Definition by PostgreSQL version

pg_index (PostgreSQL 16)

pg_index (PostgreSQL 15)

pg_index (PostgreSQL 14)

pg_index (PostgreSQL 13)

pg_index (PostgreSQL 12)

pg_index (PostgreSQL 11)

pg_index (PostgreSQL 10)

pg_index (PostgreSQL 9.6)

pg_index (PostgreSQL 9.5)

pg_index (PostgreSQL 9.4)

pg_index (PostgreSQL 9.3)

pg_index (PostgreSQL 9.2)

pg_index (PostgreSQL 9.1)

pg_index (PostgreSQL 9.0)

pg_index (PostgreSQL 8.4)

pg_index (PostgreSQL 8.3)

pg_index (PostgreSQL 8.2)

Change history

References

Categories

See also