StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POList columns with indexes in PostgreSQL
text
Body
copied!<p>I would like to get the columns that an index is on in PostgreSQL. </p> <p>In MySQL you can use <code>SHOW INDEXES FOR table</code> and look at the <code>Column_name</code> column. </p> <pre><code>mysql> show indexes from foos; +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | foos | 0 | PRIMARY | 1 | id | A | 19710 | NULL | NULL | | BTREE | | | foos | 0 | index_foos_on_email | 1 | email | A | 19710 | NULL | NULL | YES | BTREE | | | foos | 1 | index_foos_on_name | 1 | name | A | 19710 | NULL | NULL | | BTREE | | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> <p>Does anything like this exist for PostgreSQL?</p> <p>I've tried <code>\d</code> at the <code>psql</code> command prompt (with the <code>-E</code> option to show SQL) but it doesn't show the information I'm looking for.</p> <p><strong>Update:</strong> Thanks to everyone who added their answers. <a href="https://stackoverflow.com/users/48044/cope360">cope360</a> gave me exactly what I was looking for, but several people chimed in with very useful links. For future reference, check out the documentation for <a href="http://www.postgresql.org/docs/current/static/catalog-pg-index.html" rel="noreferrer">pg_index</a> (via <a href="https://stackoverflow.com/users/15785/milen-a-radev">Milen A. Radev</a>) and the very useful article <a href="http://www.alberton.info/postgresql_meta_info.html" rel="noreferrer">Extracting META information from PostgreSQL</a> (via <a href="https://stackoverflow.com/users/22595/michal-niklas">Michał Niklas</a>).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload