SHOW INDEX

Function

Displays the index information of a table.

Precautions

If schema_name is not specified, tables in the current schema are queried.
If the specified table is in schema_name.table_name format and schema_name is specified, the schema of schema_name is used.

Syntax

SHOW { INDEX | INDEXES | KEYS }
   { FROM | IN } table_name
   [{FROM | IN} schema_name ]
   [ WHERE expr ]

Parameter Description

table_name

Table name. You can specify a table name or **schema\_name.table\_name**.

schema_name

Schema name. This parameter is optional. If this parameter is not specified, the current schema is queried.

Output Column Description

Column	Description
Table	Name of the table to which the index belongs
Non_unique	Whether the index is a non-unique index
Key_name	Index name
Seq_in_index	Sequence number of the index column in the index
Column_name	Column name of the index column
Collation	The value can be A (ascending order by default), D (descending order), or NULL (indexes cannot be sorted).
Cardinality	Calculated based on pg_statistic.stadistinct and pg_class.reltuples: stadistinct > 0: stadistinct stadistinct = 0: NULL stadistinct < 0: reltuples stadistinct -1
Sub_part	Index prefix If the column is only partially indexed, the value is the number of index characters. If the entire column is indexed, the value is NULL. Currently, the prefix index is not supported. The value is NULL.
Packed	How to pack the key value. Specify pack_keys when creating a table. Otherwise, NULL is returned. Not supported currently. The value is NULL.
Null	If the value may contain NULL, the value is YES. Otherwise, the value is ''.
Index_type	Index method, such as Btree and HASH.
Comment	If the value of indisusable in the pg_index table is true, disabled is displayed. If the value of indisusable in the pg_index table is false, '' is displayed.
Index_comment	Comment specified by COMMENT when an index is created

Examples

--Create an index and a table.
MogDB=# CREATE SCHEMA tst_schema;
MogDB=# SET SEARCH_PATH TO tst_schema;

MogDB=# CREATE TABLE tst_t1
MogDB-# (
MogDB(# id int primary key,
MogDB(# name varchar(20) NOT NULL
MogDB(# );
MogDB=# CREATE INDEX tst_t1_name_ind on tst_t1(name);

--View the index of a table.
MogDB=# show index from tst_t1 ;
 table  | non_unique |    key_name     | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment 
--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------
 tst_t1 | t          | tst_t1_name_ind |            1 | name        | A         |             |          |        |      | btree      |         | 
 tst_t1 | f          | tst_t1_pkey     |            1 | id          | A         |             |          |        |      | btree      |         | 
(2 rows)

Helpful Links

N/A