How to retrive postgres functional indexes column name

Question

I want to retrieve all the functional indexes in the postgres db along with their column name. But after tying a lot I am not able to get the functional index column names.

From below query I am able to get the single normal index type.

select  t.relname as tableName, 
        i.relname as indexName, 
        STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum)   as columnName             
from pg_class t 
  inner join pg_index ix on t.oid = ix.indrelid 
  inner join pg_class i on i.oid = ix.indexrelid 
  inner join pg_attribute pga on pga.attrelid = i.oid 
  inner join pg_indexes pgidx on pgidx.indexname=i.relname 
where t.relkind = 'r' 
  and pgidx.schemaname = ?
group by t.relname, i.relname 
having count(*) = 1 
order by i.relname

pg_indexes The view pg_indexes provides access to useful information about each index in the database. — Luuk
– Luuk, Commented Jan 13, 2021 at 12:18

Luuk · Accepted Answer · 2021-01-14 18:19:25Z

1

When reading the docs here, i created a (small) test:

luuk=# create table test1 (col1 varchar(20));
luuk=# insert into test1 values('test'),('Test'),('TEST');
luuk=# select * from test1 where lower(col1)='test';
 col1
------
 test
 Test
 TEST
(3 rows)

luuk=# CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
    
luuk=# select indexdef from pg_indexes where indexname='test1_lower_col1_idx';
                                      indexdef
-------------------------------------------------------------------------------------
 CREATE INDEX test1_lower_col1_idx ON public.test1 USING btree (lower((col1)::text))
(1 row)

luuk=#

I do see a complete definition of the functional index created. Now it's only a matter of parsing this string to find all the column names.

answered Jan 14, 2021 at 18:19

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dhruv Raj Singh Over a year ago

Thanks for your response This option I have tried, but to avoid parsing I was looking for fetching columnNames directly. Its ok now I will go ahead with this approach of parsing only.

Collectives™ on Stack Overflow

How to retrive postgres functional indexes column name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related