pg_index

A system catalogue table defining indexes

pg_index is a system catalogue table defining indexes.

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 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.

Categories

Indexing, System catalogue

See also

pg_indexes