列出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屋!