Putting such a query in your app is a great way to guarantee that a future maintainer will hate you forever. If you must do it, define a view in the DB that can at least be changed easily - and please propose a new entry in information_schema for this so it can be made accessible in a nice sane way in future.
I'm not surprised you're having trouble. After re- reading the docs on pg_index, pg_am, etc I thought it'd be an indoption. That was easy to confirm by creating two identical indexes, one asc, one desc. Making sure to interpret them correctly though...
I landed up looking at the source code, src/backend/utils/adt/ruleutils.c function pg_get_indexdef_worker.
This shows that it first tests whether pg_am.amcanorder is true, and if so decodes the bits in indoption.
This will get you the reloptions for columns that are orderable, assuming you want indexes for a table named blah2:
SELECT
i.relname, i.indrelid, k AS ordinalpos, i.indoption[k-1]
FROM (
SELECT
pg_class.relname,
pg_index.indrelid, pg_index.indclass, pg_index.indoption,
unnest(indkey) as k
FROM pg_index
INNER JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE pg_index.indrelid = 'blah2'::regclass
) i
INNER JOIN pg_opclass on (pg_opclass.oid = i.indclass[k-1])
INNER JOIN pg_am ON (pg_opclass.opcmethod = pg_am.oid)
WHERE pg_am.amcanorder;
The definition of the index option bits is in src/include/catalog/pg_index.h:
/*
* Index AMs that support ordered scans must support these two indoption
* bits. Otherwise, the content of the per-column indoption fields is
* open for future definition.
*/
#define INDOPTION_DESC 0x0001 /* values are in reverse order */
#define INDOPTION_NULLS_FIRST 0x0002 /* NULLs are first instead of last */
Because they are not exposed at the SQL level you cannot rely on this not changing. Using this information might cause your app to stop working after a PostgreSQL upgrade. Though the JDBC driver uses them as horse points out, so they're not likely to be changed without a lot of thought.
You can decode the asc/desc bit like this:
CASE WHEN i.indoption[k-1] & 1 = 1 THEN 'DESC' ELSE 'ASC' END AS descasc,
but you must also handle the nulls first/last bit, whose meaning flips based on whether it's an ascending or descending index:
CASE WHEN (i.indoption[k-1] & 2 = 2) THEN 'NULLS FIRST' ELSE 'NULLS LAST' END
but then it gets messy once you start thinking about other index access methods / opclasses, non-orderable indexes (so you can't just inner join and filter), etc. Eventually I landed up at:
SELECT
t.relname AS tablename,
i.relname AS indexname, pg_attribute.attname AS colname,
k AS col_order,
CASE WHEN NOT amcanorder THEN '' WHEN i.indoption[k-1] & 1 = 1 THEN 'DESC' ELSE 'ASC' END AS descasc,
CASE WHEN NOT amcanorder THEN '' WHEN (i.indoption[k-1] & 2 = 2) THEN 'NULLS FIRST' ELSE 'NULLS LAST' END AS nulls
FROM (
SELECT
pg_class.relname,
pg_index.indrelid, pg_index.indclass, pg_index.indoption,
unnest(pg_index.indkey) AS k
FROM pg_index
INNER JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE pg_index.indrelid = 'blah2'::regclass
) i
INNER JOIN pg_opclass on (pg_opclass.oid = i.indclass[k-1])
INNER JOIN pg_am ON (pg_opclass.opcmethod = pg_am.oid)
INNER JOIN pg_class t ON i.indrelid = t.oid
INNER JOIN pg_attribute ON (pg_attribute.attrelid = i.indrelid AND pg_attribute.attnum = k);
... but haven't comprehensively tested it against GiST, GIN, custom index methods, all index definition variations, etc. It certainly doesn't deal with:
- unique indexes
- custom collations
- partial indexes
- omitting defaults (
ASC, NULLS LAST for ASC, and NULLS FIRST for DESC)
and probably more. Of course you'll want to parameterize the table name filter too.
@a_horsE_with_no_name likely has the right idea: crib the JDBC driver's query and be done with it.
