在 PostgreSQL 中列出带有索引的列
在 PostgreSQL 中列出带有索引的列
我需要获取在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)和非常有用的文章从PostgreSQL中提取META信息(通过Michał Niklas)。
admin 更改状态以发布 2023年5月23日
PostgreSQL (pg_indexes):
SELECT * FROM pg_indexes WHERE tablename = 'mytable';
MySQL (SHOW INDEX):
SHOW INDEX FROM mytable;
创建一些测试数据...
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