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 — Andreas
– Andreas, Commented Apr 1, 2019 at 3:40
@Andreas That's not an exact match because I'm asking for more information than just the column names. — Jonathan Allen
– Jonathan Allen, Commented Apr 1, 2019 at 3:41

Erwin Brandstetter · Accepted Answer · 2019-04-01 18:22:38Z

8

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.

edited Apr 1, 2019 at 18:22

answered Apr 1, 2019 at 5:43

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Jonathan Allen Over a year ago

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.

user330315 Over a year ago

@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

Erwin Brandstetter Over a year ago

@JonathanAllen: OTOH, Postgres does not support true generated columns - yet. There is ongoing work to add the feature in Postgres 12. See: stackoverflow.com/a/8250729/939860

Laurenz Albe · Accepted Answer · 2019-04-01 14:58:38Z

4

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.

edited Apr 1, 2019 at 14:58

answered Apr 1, 2019 at 6:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

Erwin Brandstetter Over a year ago

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.

Laurenz Albe Over a year ago

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.

Jonathan Allen · Accepted Answer · 2019-08-24 21:44:31Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related