在PostgreSQL中列出带索引的列

我想在PostgreSQL中获得索引上的列。

在MySQL中，您可以使用SHOW INDEXES FOR表并查看Column_name列。

mysql> show indexes from foos;

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foos  |          0 | PRIMARY             |            1 | id          | A         |       19710 |     NULL | NULL   |      | BTREE      |         | 
| foos  |          0 | index_foos_on_email |            1 | email       | A         |       19710 |     NULL | NULL   | YES  | BTREE      |         | 
| foos  |          1 | index_foos_on_name  |            1 | name        | A         |       19710 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

PostgreSQL中存在类似的东西吗?

我已经在psql命令提示符中尝试了\d(使用-E选项来显示SQL)，但它没有显示我正在寻找的信息。

更新:感谢大家的回答。cope360提供了我想要的东西，但也有一些人提供了非常有用的链接。为了将来的参考，请查看pg_index的文档(通过Milen A. Radev)和非常有用的文章从PostgreSQL提取META信息(通过micharov Niklas)。

当前回答

# \迪

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

2018-01-22 12:51:55

其他回答

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

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;

2012-10-23 16:40:39

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL(显示索引)

SHOW INDEX FROM mytable;

2017-02-10 03:15:43

@cope360接受的答案很好，但我想要一些更像Oracle的DBA_IND_COLUMNS, ALL_IND_COLUMNS和USER_IND_COLUMNS(例如，报告表/索引模式和索引在多列索引中的位置)，所以我把接受的答案改编成这样:

with
 ind_cols as (
select
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    1 + array_position(ix.indkey, a.attnum) as column_position
from
     pg_catalog.pg_class t
join pg_catalog.pg_attribute a on t.oid    =      a.attrelid 
join pg_catalog.pg_index ix    on t.oid    =     ix.indrelid
join pg_catalog.pg_class i     on a.attnum = any(ix.indkey)
                              and i.oid    =     ix.indexrelid
join pg_catalog.pg_namespace n on n.oid    =      t.relnamespace
where t.relkind = 'r'
order by
    t.relname,
    i.relname,
    array_position(ix.indkey, a.attnum)
)
select * 
from ind_cols
where schema_name = 'test'
  and table_name  = 'indextest'
order by schema_name, table_name
;

这将给出如下输出:

 schema_name | table_name | index_name | column_name | column_position 
-------------+------------+------------+-------------+-----------------
 test        | indextest  | testind1   | singleindex |               1
 test        | indextest  | testind2   | firstoftwo  |               1
 test        | indextest  | testind2   | secondoftwo |               2
(3 rows)

2019-02-05 21:30:54

一些样本数据…

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));

使用pg_get_indexdef函数:

select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test'::regclass;

                    pg_get_indexdef
--------------------------------------------------------
 CREATE UNIQUE INDEX pk_test ON test USING btree (a, b)
(1 row)


select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test2'::regclass;
                     pg_get_indexdef
----------------------------------------------------------
 CREATE UNIQUE INDEX uk_test2 ON test2 USING btree (b, c)
(1 row)


select pg_get_indexdef(indexrelid) from pg_index where indrelid ='test3'::regclass;
                      pg_get_indexdef
------------------------------------------------------------
 CREATE UNIQUE INDEX uk_test3b ON test3 USING btree (b)
 CREATE UNIQUE INDEX uk_test3c ON test3 USING btree (c)
 CREATE UNIQUE INDEX uk_test3ab ON test3 USING btree (a, b)
(3 rows)

2010-02-06 00:09:47