我想在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)。
结合其他代码并创建一个视图:
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;
类似于接受的答案，但有左连接pg_attribute作为正常连接或查询pg_attribute不给出索引，如:
为用户创建唯一索引unique_user_name_index (lower(name))
select
row_number() over (order by c.relname),
c.relname as index,
t.relname as table,
array_to_string(array_agg(a.attname), ', ') as column_names
from pg_class c
join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%'
join pg_class t on t.oid = i.indrelid
left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey)
group by t.relname, c.relname order by c.relname;
@cope360的精彩回答，转换为使用连接语法。
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
join pg_index ix
on t.oid = ix.indrelid
join pg_class i
on i.oid = ix.indexrelid
join pg_attribute a
on a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'test%'
group by t.relname
, i.relname
order by t.relname
, i.relname
;
结合其他代码并创建一个视图:
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;
请尝试下面的查询以深入到所需的索引
查询如下-我亲自尝试过，并经常使用它。
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner",
c2.relname as "Table"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
AND c2.relname like '%agg_transaction%' --table name
AND nspname = 'edjus' -- schema name
ORDER BY 1,2;
