TransWikia.com

Postgres - Find index order ASC or DESC

Database Administrators Asked by Bhuvanesh on February 28, 2021

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?

One Answer

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 ;

Answered by Bhuvanesh on February 28, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP