类似于接受的答案，但有左连接pg_attribute作为正常连接或查询pg_attribute不给出索引，如: 为用户创建唯一索引unique_user_name_index (lower(name))

select 
    row_number() over (order by c.relname),
    c.relname as index, 
    t.relname as table, 
    array_to_string(array_agg(a.attname), ', ') as column_names 
from pg_class c
join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%' 
join pg_class t on t.oid = i.indrelid
left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey) 
group by t.relname, c.relname order by c.relname;

我认为这个版本在这个线程上还不存在:它提供了列名列表和索引的ddl。

CREATE OR REPLACE VIEW V_TABLE_INDEXES AS

SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,i.indisunique AS "is_unique"
    ,array_to_string(array_agg(a.attname), ', ') as "columns"
    ,pg_get_indexdef(i.indexrelid) as "ddl"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE c.relkind = 'i'
      and n.nspname not in ('pg_catalog', 'pg_toast')
      and pg_catalog.pg_table_is_visible(c.oid)
GROUP BY
    n.nspname
    ,t.relname
    ,c.relname
    ,i.indisunique
    ,i.indexrelid
ORDER BY
    n.nspname
    ,t.relname
    ,c.relname;

我发现使用函数的索引不链接到列名，所以偶尔你会发现一个索引列表，例如一个列名，而实际上是使用3。

例子:

CREATE INDEX ui1 ON table1 (coalesce(col1,''),coalesce(col2,''),col3)

该查询仅返回'col3'作为索引上的列，但DDL显示了索引中使用的全部列。

如果你想保持索引中的列顺序，这里有一个(非常丑陋的)方法:

select table_name,
    index_name,
    array_agg(column_name)
from (
    select
        t.relname as table_name,
        i.relname as index_name,
        a.attname as column_name,
        unnest(ix.indkey) as unn,
        a.attnum
    from
        pg_class t,
        pg_class i,
        pg_index ix,
        pg_attribute a
    where
        t.oid = ix.indrelid
        and i.oid = ix.indexrelid
        and a.attrelid = t.oid
        and a.attnum = ANY(ix.indkey)
        and t.relkind = 'r'
        and t.relnamespace = <oid of the schema you're interested in>
    order by
        t.relname,
        i.relname,
        generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name, index_name

列顺序存储在pg_index中。indkey列，我是根据这个数组的下标排序的。

下面是一个包装cope360答案的函数:

CREATE OR REPLACE FUNCTION getIndices(_table_name varchar)
  RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$
  BEGIN
    RETURN QUERY
    select
    t.relname::varchar as table_name,
    i.relname::varchar as index_name,
    a.attname::varchar as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname = _table_name
order by
    t.relname,
    i.relname;
  END;
  $$ LANGUAGE plpgsql;

用法:

select * from getIndices('<my_table>')

# \迪

最简单和最短的方法是\di，它将列出当前数据库中的所有索引。

$ \di
                      List of relations
 Schema |            Name             | Type  |  Owner   |     Table     
--------+-----------------------------+-------+----------+---------------
 public | part_delivery_index         | index | shipper  | part_delivery
 public | part_delivery_pkey          | index | shipper  | part_delivery
 public | shipment_by_mandator        | index | shipper  | shipment_info
 public | shipment_by_number_and_size | index | shipper  | shipment_info
 public | shipment_info_pkey          | index | shipper  | shipment_info
(5 rows)

\di是\d命令的“小兄弟”，该命令将列出当前数据库的所有关系。因此\di当然代表“显示我这个数据库索引”。

输入\diS将列出系统范围内使用的所有索引，这意味着您也将获得所有pg_catalog索引。

$ \diS
                                      List of relations
   Schema   |                   Name                    | Type  |  Owner   |          Table
------------+-------------------------------------------+-------+----------+-------------------------
 pg_catalog | pg_aggregate_fnoid_index                  | index | postgres | pg_aggregate
 pg_catalog | pg_am_name_index                          | index | postgres | pg_am
 pg_catalog | pg_am_oid_index                           | index | postgres | pg_am
 pg_catalog | pg_amop_fam_strat_index                   | index | postgres | pg_amop
 pg_catalog | pg_amop_oid_index                         | index | postgres | pg_amop
 pg_catalog | pg_amop_opr_fam_index                     | index | postgres | pg_amop
 pg_catalog | pg_amproc_fam_proc_index                  | index | postgres | pg_amproc
 pg_catalog | pg_amproc_oid_index                       | index | postgres | pg_amproc
 pg_catalog | pg_attrdef_adrelid_adnum_index            | index | postgres | pg_attrdef
--More-- 

使用这两个命令，您可以在它之后添加一个+，以获得更多信息，如大小-磁盘空间-索引需要和描述(如果可用)。

$ \di+
                                 List of relations
 Schema |            Name             | Type  |  Owner   |     Table     | Size  | Description 
--------+-----------------------------+-------+----------+---------------+-------+-------------
 public | part_delivery_index         | index | shipper  | part_delivery | 16 kB | 
 public | part_delivery_pkey          | index | shipper  | part_delivery | 16 kB | 
 public | shipment_by_mandator        | index | shipper  | shipment_info | 19 MB | 
 public | shipment_by_number_and_size | index | shipper  | shipment_info | 19 MB | 
 public | shipment_info_pkey          | index | shipper  | shipment_info | 53 MB | 
(5 rows)

在psql中，你可以很容易地找到关于输入\?

有一个简单的解决方案:

SELECT 
  t.relname table_name,
  ix.relname index_name,
  indisunique,
  indisprimary, 
  regexp_replace(pg_get_indexdef(indexrelid), '.*\((.*)\)', '\1') columns
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE t.relname LIKE 'test%'

`