0

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?

1 Answer 1

1

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 
                                 & 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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.