有一个简单的解决方案:

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%'

`

查询结果:

table |     column     |          type          | notnull |  index_name  | is_index | primarykey | uniquekey | default
-------+----------------+------------------------+---------+--------------+----------+-   -----------+-----------+---------
 nodes | dns_datacenter | character varying(255) | f       |              | f        | f          | f         |
 nodes | dns_name       | character varying(255) | f       | dns_name_idx | t        | f          | f         |
 nodes | id             | uuid                   | t       | nodes_pkey   | t        | t          | t         |
(3 rows)

查询:

SELECT  
c.relname AS table,
f.attname AS column,  
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
f.attnotnull AS notnull,  
i.relname as index_name,
CASE  
    WHEN i.oid<>0 THEN 't'  
    ELSE 'f'  
END AS is_index,  
CASE  
    WHEN p.contype = 'p' THEN 't'  
    ELSE 'f'  
END AS primarykey,  
CASE  
    WHEN p.contype = 'u' THEN 't' 
    WHEN p.contype = 'p' THEN 't' 
    ELSE 'f'
END AS uniquekey,
CASE
    WHEN f.atthasdef = 't' THEN d.adsrc
END AS default  FROM pg_attribute f  
JOIN pg_class c ON c.oid = f.attrelid  
JOIN pg_type t ON t.oid = f.atttypid  
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid 
LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid 

WHERE c.relkind = 'r'::char  
AND n.nspname = 'public'  -- Replace with Schema name 
--AND c.relname = 'nodes'  -- Replace with table name, or Comment this for get all tables
AND f.attnum > 0
ORDER BY c.relname,f.attname;

类似于接受的答案，但有左连接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;

# \迪

最简单和最短的方法是\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中，你可以很容易地找到关于输入\?

创建一些测试数据…

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

列出索引的索引和列:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname 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 like 'test%'
order by
    t.relname,
    i.relname;

 table_name | index_name | column_name
------------+------------+-------------
 test       | pk_test    | a
 test       | pk_test    | b
 test2      | uk_test2   | b
 test2      | uk_test2   | c
 test3      | uk_test3ab | a
 test3      | uk_test3ab | b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

卷起列名:

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
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 like 'test%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

 table_name | index_name | column_names
------------+------------+--------------
 test       | pk_test    | a, b
 test2      | uk_test2   | b, c
 test3      | uk_test3ab | a, b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

结合其他代码并创建一个视图:

CREATE OR REPLACE VIEW view_index AS 
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
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
WHERE c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname
    ,t.relname
    ,c.relname;