title: Postgres \d+ (show relation information) equivalent via sql
body: <p>I have seen several examples like <a href="https://stackoverflow.com/questions/109325/postgresql-describe-table">here</a> or <a href="http://code.activestate.com/recipes/576557/" rel="noreferrer">here</a></p>
  
  <p>and I am able to do most of what I can do but not all of them.</p>
  
  <p>if I have :</p>
  
  <pre><code>\d+ myschema.mytable
  </code></pre>
  
  <p>I have all the columns listed and the indexes. Format for the index is for example :</p>
  
  <pre><code>Indexes:
      "id_pkey" PRIMARY KEY, btree (id,name,timestamp)
      "ix05_id" btree (name,company)
  </code></pre>
  
  <p>I was able to get the columns via sql and for the indexes and the keys i have found some sql from the above post but not exactly to bring in that format.</p>
  
  <p>So for keys I was able to do :</p>
  
  <pre><code>SELECT               
    pg_attribute.attname, 
    format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
  FROM pg_index, pg_class, pg_attribute, pg_namespace 
  WHERE 
    pg_class.oid = 'mytable'::regclass AND 
    indrelid = pg_class.oid AND 
    nspname = 'myschema' AND 
    pg_class.relnamespace = pg_namespace.oid AND 
    pg_attribute.attrelid = pg_class.oid AND 
    pg_attribute.attnum = any(pg_index.indkey)
   AND indisprimary
  </code></pre>
  
  <p>So this one fetches the columns the key applies to, but not the name of the index/key? How can I add that in the above query? With what table do I need to join?</p>
  
  <p>For the indexes I was able to do </p>
  
  <pre><code>SELECT * FROM pg_indexes WHERE tablename = 'mytable' AND schemaname = 'myschema' ;
  </code></pre>
  
  <p>Which fetches the indexes, but does not explicitly list the column they apply to - I only see the indexdef which is how they created? Can I get that info by joining with other tables or do I have to parse the indexdef and get the columns index applies to?</p>