Postgres: To check index and their columns on a table

    SELECT
        t.relname AS table_name,
        i.relname AS index_name,
        string_agg(a.attname, ', ') AS indexed_columns
    FROM
        pg_class t
    JOIN
        pg_index ix ON t.oid = ix.indrelid
    JOIN
        pg_class i ON i.oid = ix.indexrelid
    JOIN
        pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
    WHERE
        t.relkind = 'r' -- 'r' for regular tables
        AND t.relname = 'coupons_BEIN' -- Optional: filter for a specific table
    GROUP BY
        t.relname, i.relname
    ORDER BY
        t.relname, i.relname;

 

 

Same script below but with line spaces:  

 

 

 

 

  SELECT

        t.relname AS table_name,

        i.relname AS index_name,

        string_agg(a.attname, ', ') AS indexed_columns

    FROM

        pg_class t

    JOIN

        pg_index ix ON t.oid = ix.indrelid

    JOIN

        pg_class i ON i.oid = ix.indexrelid

    JOIN

        pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)

    WHERE

        t.relkind = 'r' -- 'r' for regular tables

        AND t.relname = 'coupons' -- Optional: filter for a specific table

    GROUP BY

        t.relname, i.relname

    ORDER BY

        t.relname, i.relname;