• Browse categories
• PostgreSQL releases
  • PostgreSQL 14 (dev)
  • PostgreSQL 13 (latest)
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6
  • PostgreSQL 9.5
  • PostgreSQL 9.4 (EOL)

Useful articles

Useful links

pg_index

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

                  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)
    

                  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)
    

                  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)
    

                  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)
    

        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)
    

        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)
    

        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)
    

        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)
    

        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)
    

        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)
    

       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)
    

       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)
    

       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)
    

       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)
    

Change history

Note: any changes prior to PostgreSQL 7.2 are not reflected in this list.

• 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

Categories

See also