CODEWITHSUNDEEP
postgresqldatabase-indexesdatabase-metadata
Jonathan Allen
Jonathan Allen

Reputation: 70307

How to get the list of column names for all indexes on a table in PostgreSQL?

I have this query to get the list of indexes on a table:

SELECT
    ns.nspname as schema_name,
    tab.relname as table_name,
    cls.relname as index_name,
    am.amname as index_type,
    idx.indisprimary as is_primary,
    idx.indisunique as is_unique
FROM
    pg_index idx
INNER JOIN pg_class cls ON cls.oid=idx.indexrelid
INNER JOIN pg_class tab ON tab.oid=idx.indrelid
INNER JOIN pg_am am ON am.oid=cls.relam
INNER JOIN pg_namespace ns on ns.oid=tab.relnamespace
WHERE ns.nspname = @Schema AND tab.relname = @Name

It seems to be working right. But now I need a query for the list of columns and I'm having trouble understanding how the system views work.

Specifically what I'm looking for are:

Ideally I would like to get the above items for all indexes of a given table at one time.

Note that I'm looking for more than just the column names.

Upvotes: 4

Views: 4847

Answers (3)

Jonathan Allen
Jonathan Allen

Reputation: 70307

Version 10.4

SELECT idx.indexrelid::regclass AS indexname,                                    
       k.i AS index_order,                                                     
       --i.indnkeyatts,                                                          
       coalesce(att.attname,                                                     
                (('{' || pg_get_expr(                                          
                            idx.indexprs,                                        
                            idx.indrelid                                         
                         )                                                     
                      || '}')::text[]                                          
                )[k.i]                                                         
               ) AS index_column,                                              
       pg_index_column_has_property(idx.indexrelid,k.i::int,'asc') AS ascending,                                  
       k.i != -1 AS is_key                                          
FROM pg_index idx                                                                
   CROSS JOIN LATERAL unnest(idx.indkey) WITH ORDINALITY AS k(attnum, i)         
   LEFT JOIN pg_attribute AS att                                                 
      ON idx.indrelid = att.attrelid AND k.attnum = att.attnum

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

Use the system catalog information function pg_get_indexdef(index_oid) to get complete information (including the list of index expressions) - in a query against pg_index to get all indexes for a given table:

SELECT pg_get_indexdef(indexrelid) || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;  -- optionally schema-qualified

Related:

If you rely on an unqualified table name (without schema) you depend on the current search_path setting and might get results for a table of the same name in a different schema.

Alternatively, you can join to pg_attribute by hand to get individual columns like demonstrated in these related answers:

Key ingredient is to join like this:

FROM   pg_index idx
LEFT   JOIN pg_attribute a ON a.attrelid = idx.indrelid
                          AND a.attnum = ANY(idx.indkey)
                          AND a.attnum > 0

The manual about pg_index.indkey:

This is an array of indnatts values that indicate which table columns this index indexes. For example a value of 1 3 would mean that the first and the third table columns make up the index entries. Key columns come before non-key (included) columns. A zero in this array indicates that the corresponding index attribute is an expression over the table columns, rather than a simple column reference.

Adding AND a.attnum > 0 is not technically necessary as there is no a.attnum = 0. But it makes the query clearer and it won't hurt. The manual:

Ordinary columns are numbered from 1 up. System columns, such as oid, have (arbitrary) negative numbers.

Be aware, that there the "list of column names" can actually contain expressions, too. And since Postgres 11 there are also "included" columns (no expressions there). pg_get_indexdef() deals with all possible complications out of the box.

Upvotes: 8

Laurenz Albe
Laurenz Albe

Reputation: 246493

You can puzzle it together from the system catalogs, as Erwin Brandstetter detailed.

Here is a query that will return the information you want:

SELECT i.indexrelid::regclass AS indexname,                                    
       k.i AS index_order,                                                     
       i.indnkeyatts,                                                          
       coalesce(a.attname,                                                     
                (('{' || pg_get_expr(                                          
                            i.indexprs,                                        
                            i.indrelid                                         
                         )                                                     
                      || '}')::text[]                                          
                )[k.i]                                                         
               ) AS index_column,                                              
       i.indoption[k.i - 1] = 0 AS ascending,                                  
       k.i <= i.indnkeyatts AS is_key                                          
FROM pg_index i                                                                
   CROSS JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS k(attnum, i)         
   LEFT JOIN pg_attribute AS a                                                 
      ON i.indrelid = a.attrelid AND k.attnum = a.attnum                       
WHERE i.indrelid = 'schemaname.tablename'::regclass;

This query will only work from PostgreSQL v11 on (but there are no covering Indexes before v11).

Also, the query will fail if the indexed expression contains a comma; I don't know how to fix that.

Upvotes: 4

Related Questions