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

Question

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

Answer 1

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.

Answer 2

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.