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?
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP