How to get the Index column order(ASC, DESC, NULLS FIRST....) from Postgresql? -

i have retrieve order of columns involved in index. using function pg_get_indexdef() definition of index shown below,

"create index test on ravi1.table_with_index using btree ("column1" desc, "column3" desc, "column4") (fillfactor=60)" 

here definition says column1 , column3 in descending order , column4 in ascending order.

with data in string, have parsing column sort order.

is there alternative way, able values ie., columns order.

right getting columns associated individual indexes using below query

select array(select pg_get_indexdef(idx.indexrelid, k + 1, true) generate_subscripts(idx.indkey, 1) k order k ) index_members, idx.indexprs not null indexprs pg_index idx join pg_class on i.oid = idx.indexrelid join pg_namespace ns on ns.oid = i.relnamespace join pg_class t on t.oid = idx.indrelid ns.nspname = 'schema' , t.relname ='table' , i.relname ='index' 

in same query, way out column order ?

this of great worked out, otherwise have write parsers values pg_get_indexdef() function.

thanks,

ravi

the jdbc driver uses simpler query , return whether column defined asc or desc

the following more or less verbatim copy of source code of driver. removed jdbc columns make bit more "general".

select ct.relname table_name,         i.indisunique,         ci.relname index_name,         (i.keys).n ordinal_position,         pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) column_name,         case am.amcanorder           when true case i.indoption[(i.keys).n - 1] & 1             when 1 'desc'             else 'asc'           end           else null         end asc_or_desc,       pg_catalog.pg_get_expr(i.indpred, i.indrelid) filter_condition  pg_catalog.pg_class ct    join pg_catalog.pg_namespace n on (ct.relnamespace = n.oid)    join (select i.indexrelid, i.indrelid, i.indoption,            i.indisunique, i.indisclustered, i.indpred,            i.indexprs,            information_schema._pg_expandarray(i.indkey) keys          pg_catalog.pg_index i)      on (ct.oid = i.indrelid)    join pg_catalog.pg_class ci on (ci.oid = i.indexrelid)    join pg_catalog.pg_am on (ci.relam = am.oid)  n.nspname = 'some_schema' , ct.relname = 'some_table'