Postgres - Find index order ASC or DESC Im trying to list all the indexes in my Postgres along with their order like ASC or DESC. I found some great answers from this StackOverflow question. But unfortunately, the pg_am table got removed a lot more columns.
So Im  not able to find any alternate system table to check this. Can someone help me with that?
I found a small workaround for this.
SELECT     tnsp.nspname                           AS schema_name, 
           trel.relname                           AS table_name, 
           irel.relname                           AS index_name, 
           a.attname                              AS column_name, 
           1 + Array_position(i.indkey, a.attnum) AS column_position, 
           CASE o.OPTION 
                                 &amp; 1 
                      WHEN 1 THEN 'DESC' 
                      ELSE 'ASC' 
           END      AS order 
FROM       pg_index AS i 
join       pg_class AS trel 
ON         trel.oid = i.indrelid 
join       pg_namespace AS tnsp 
ON         trel.relnamespace = tnsp.oid 
join       pg_class AS irel 
ON         irel.oid = i.indexrelid 
cross join lateral unnest (i.indkey) WITH ordinality    AS c (colnum, ordinality) 
left join  lateral unnest (i.indoption) WITH ordinality AS o (OPTION, ordinality) 
ON         c.ordinality = o.ordinality 
join       pg_attribute AS a 
ON         trel.oid = a.attrelid 
AND        a.attnum = c.colnum 
WHERE      tnsp.nspname='public' -- can be replaced
AND        trel.relname='test1' -- can be replaced
GROUP BY   tnsp.nspname, 
           trel.relname, 
           irel.relname, 
           a.attname, 
           array_position(i.indkey, a.attnum), 
           o.OPTION ;