How to sort/filter the result of `SHOW INDEXES` in CockroachDB?

In Cockroach, you can get a list of all indexes in a database with

SHOW INDEXES FROM DATABASE db_name;

As far as I can tell, this is the only way to do so -- there's no table like information_schema."indexes".

I want to select only a subset of columns, but I can't figure out how. A query like

SELECT
  t.table_name,
  t.index_name,
  -- ...
FROM (
  SHOW INDEXES FROM DATABASE db_name
) AS t;

reports ERROR: at or near "from": syntax error, which is extraordinarily unhelpful. I would also like to be able to add a WHERE and/or ORDER BY if need be. How can I manipulate the result of SHOW INDEXES as if it were a table?

For some context of why I'm doing this, I want to compare the indexes present on two different versions of a database using two different versions of Cockroach (22.2.3 vs 24.2.4). SHOW INDEXES returns a different set of columns between these versions, which makes a simple diff infeasible.

Solution

You can use a WITH clause before the word SELECT:

WITH t AS (
    SHOW INDEXES FROM DATABASE db_name
)
SELECT
    table_name,
    index_name,
    -- ...
FROM t;

Note that the AS is backwards compared to if it were a nested query in the FROM clause.

As a word of warning given this particular use case, Cockroach 22.2.3 includes sequences in the output of SHOW INDEXES, while Cockroach 24.2.4 does not.