title: How to get the Index column order(ASC, DESC, NULLS FIRST....) from Postgresql?
body: <p>I have to retrieve the order of the columns involved in the index. Using the function pg_get_indexdef() I could get the definition of the index as shown below,</p>
  
  <pre><code>"CREATE INDEX test ON ravi1.table_with_index USING btree ("Column1" DESC, "Column3" DESC, "Column4") WITH (fillfactor=60)"
  </code></pre>
  
  <p>Here the definition says the Column1 and Column3 is in Descending order and Column4 is in Ascending order.</p>
  
  <p>With this data in String, I have to do parsing to get the column sort order.</p>
  
  <p>Is there any alternative way, so that I would be able to get the values ie., the Columns order.</p>
  
  <p>Right now am getting the columns associated with individual indexes using the below query</p>
  
  <pre><code>SELECT ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM
  generate_subscripts(idx.indkey, 1) as k ORDER BY k ) as index_members,
  idx.indexprs IS NOT NULL as indexprs
  FROM pg_index as idx
  JOIN pg_class as i ON i.oid = idx.indexrelid
  JOIN pg_namespace as ns ON ns.oid = i.relnamespace
  JOIN pg_class as t ON t.oid = idx.indrelid
  where ns.nspname = 'schema' and t.relname ='table' and i.relname ='index'
  </code></pre>
  
  <p>In the same query, is the way to look out for the column order as well ?</p>
  
  <p>This will be of a great help it worked out, otherwise i have to write some parsers to get the values from <code>pg_get_indexdef()</code> function.</p>
  
  <p>Thanks,</p>
  
  <p>Ravi</p>