在PostgreSQL中列出具有索引的列
在PostgreSQL中列出具有索引的列
提问于 2010-02-05 07:39:44
我想要在PostgreSQL中获取索引所在的列。
在MySQL中，您可以使用SHOW INDEXES FOR table并查看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)和非常有用的文章Extracting META information from PostgreSQL (通过Michał Niklas)。
回答 24
Stack Overflow用户
回答已采纳
发布于 2010-02-06 21:17:34
创建一些测试数据...
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 | cStack Overflow用户
发布于 2017-02-10 11:15:43
PostgreSQL ():
SELECT * FROM pg_indexes WHERE tablename = 'mytable';MySQL ():
SHOW INDEX FROM mytable;Stack Overflow用户
发布于 2010-02-05 15:03:58
\d table_name显示了来自psql的这些信息，但是如果您想使用SQL从数据库中获取这些信息，那么可以看看Extracting META information from PostgreSQL。
我在utility to report some info from db schema中使用这些信息来比较测试和生产环境中的PostgreSQL数据库。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2204058
复制相关文章
相似问题
腾讯云开发者
