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

Question

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:

[index name or id for matching to the first query]
Order in index
Column name
ascending or descending
sorted column or included column

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.

Possible duplicate of [List columns with indexes in PostgreSQL](https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql) — Andreas, Apr 01 '19 at 03:40
@Andreas That's not an exact match because I'm asking for more information than just the column names. — Jonathan Allen, Apr 01 '19 at 03:41

Erwin Brandstetter · Answer 1 · 2019-04-01T18:22:38.210

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

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.

Thanks for the warning. Other databases I've seen require calculated columns at the table level first, you can't put the calculations directly into the index. — Jonathan Allen, Apr 01 '19 at 17:02
@JonathanAllen: Oracle, DB2 and to a certain Firebird as well also support also creating an index on an expression. This is not unique to Postgres — , Apr 01 '19 at 18:28
@JonathanAllen: OTOH, Postgres does not support true generated columns - yet. There is ongoing work to add the feature in Postgres 12. See: https://stackoverflow.com/a/8250729/939860 — Erwin Brandstetter, Apr 01 '19 at 18:35

Laurenz Albe · Answer 2 · 2019-04-01T14:58:38.470

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.

Joining to an unnested set is more accurate than my short `a.attnum = ANY(idx.indkey)` - in case of (questionable!) duplicated columns in the list of index expressions). But you have a couple regressions, too. 1. `indnkeyatts`: needs to be `indnatts` before pg 11, *included* columns missing in pg 11. 2. schema-qualify `'tablename'::regclass` to be sure. 3. `pg_get_expr()` returns a list of index expressions which may contain commas that invalidate your concatenated array literal. Consider the example: `to_tsvector('simple', col)`. Fixing that gets messy. `pg_get_indexdef()` avoids the trouble. — Erwin Brandstetter, Apr 01 '19 at 13:04
The question is specifically about PostgreSQL v11, I guess, because it asks about "included columns". But you are right about the comma in the expression. I'll see if I can do anything about that. — Laurenz Albe, Apr 01 '19 at 15:14

score 1 · Answer 3 · answered Aug 24 '19 at 21:44

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

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

3 Answers3

Linked