我想在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)。
请尝试下面的查询以深入到所需的索引
查询如下-我亲自尝试过，并经常使用它。
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;
请尝试下面的查询以深入到所需的索引
查询如下-我亲自尝试过，并经常使用它。
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;
下面是一个包装cope360答案的函数:
CREATE OR REPLACE FUNCTION getIndices(_table_name varchar)
RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$
BEGIN
RETURN QUERY
select
t.relname::varchar as table_name,
i.relname::varchar as index_name,
a.attname::varchar 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 = _table_name
order by
t.relname,
i.relname;
END;
$$ LANGUAGE plpgsql;
用法:
select * from getIndices('<my_table>')
