How to get the Index column order(ASC, DESC, NULLS FIRST....) from Postgresql?
postgresql
Solution
The JDBC driver uses a much simpler query and it does return whether the column is defined as ASC or DESC
The following is more or less a verbatim copy of the source code of the driver. I removed some JDBC only columns to make it a bit more "general".
SELECT ct.relname AS TABLE_NAME,
i.indisunique,
ci.relname AS INDEX_NAME,
(i.keys).n AS ORDINAL_POSITION,
pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS COLUMN_NAME,
CASE am.amcanorder
WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1
WHEN 1 THEN 'DESC'
ELSE 'ASC'
END
ELSE NULL
END AS ASC_OR_DESC,
pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION
FROM 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) AS keys
FROM pg_catalog.pg_index i) i
ON (ct.oid = i.indrelid)
JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid)
JOIN pg_catalog.pg_am am ON (ci.relam = am.oid)
WHERE n.nspname = 'some_schema'
AND ct.relname = 'some_table'
Obsolete Warning: As of PostgreSQL 9.6, the columns on pg_am are no longer available.
Problem
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, ``` "CREATE INDEX test ON ravi1.table_with_index USING btree ("Column1" DESC, "Column3" DESC, "Column4") WITH (fillfactor=60)" ``` Here the definition says the Column1 and Column3 is in Descending order and Column4 is in Ascending order. With this data in String, I have to do parsing to get the column sort order. Is there any alternative way, so that I would be able to get the values ie., the Columns order. Right now am getting the columns associated with individual indexes using the below query ``` 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' ``` In the same query, is the way to look out for the column order as well ? This will be of a great help it worked out, otherwise i have to write some parsers to get the values from `pg_get_indexdef()` function. Thanks, Ravi