列出PostgreSQL中带索引的列 [英] List columns with indexes in PostgreSQL

问题描述

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

I would like to get the columns that an index is on in PostgreSQL.

在MySQL中你可以使用 SHOW INDEXES FOR表并查看 Column_name 专栏。

In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column.

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是否存在类似的内容？

Does anything like this exist for PostgreSQL?

我在 psql 命令提示符下尝试了 \d （使用 -E 显示SQL的选项）但它没有显示我正在寻找的信息。

I've tried \d at the psql command prompt (with the -E option to show SQL) but it doesn't show the information I'm looking for.

更新： 感谢所有添加答案的人。 cope360 给了我我正在寻找的内容，但有几个人使用了非常有用的链接。有关日后参考，请查看 pg_index 的文档（通过 Milen A. Radev ）和非常有用的文章从PostgreSQL中提取META信息（通过MichałNiklas）。

Update: Thanks to everyone who added their answers. cope360 gave me exactly what I was looking for, but several people chimed in with very useful links. For future reference, check out the documentation for pg_index (via Milen A. Radev) and the very useful article Extracting META information from PostgreSQL (via Michał Niklas).

推荐答案

创建一些测试数据......

Create some test data...

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

这篇关于列出PostgreSQL中带索引的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！