Add command for listing indices

[owenconti]

Add new command for listing indices:

schema:indices {table}

[willvincent]

For postgres:

SELECT kcu.column_name as Field, 
       i.indexname as Index,
       i.indexdef as Definition 
FROM pg_indexes i
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = i.indexname
WHERE i.tablename = 'users';

Outputs:

Field	Index	Definition
id	users_pkey	CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
email	users_email_unique	CREATE UNIQUE INDEX users_email_unique ON public.users USING btree (email)

[willvincent]

Re: options for fetching indexes in mysql: https://tableplus.com/blog/2018/08/mysql-how-to-list-all-indexes-from-table-or-schema.html

[dinhquochan]

For MySQL:

SHOW INDEX FROM users;

Output:

+-------+------------+-----------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY                     |            1 | id            | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          0 | users_email_unique          |            1 | email         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | users_user_group_id_foreign |            1 | user_group_id | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

[willvincent]

there are several other attributes that could be included in the postgres select, but nothing else seemed super relevant