我想在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)。
@cope360接受的答案很好，但我想要一些更像Oracle的DBA_IND_COLUMNS, ALL_IND_COLUMNS和USER_IND_COLUMNS(例如，报告表/索引模式和索引在多列索引中的位置)，所以我把接受的答案改编成这样:
with
ind_cols as (
select
n.nspname as schema_name,
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
1 + array_position(ix.indkey, a.attnum) as column_position
from
pg_catalog.pg_class t
join pg_catalog.pg_attribute a on t.oid = a.attrelid
join pg_catalog.pg_index ix on t.oid = ix.indrelid
join pg_catalog.pg_class i on a.attnum = any(ix.indkey)
and i.oid = ix.indexrelid
join pg_catalog.pg_namespace n on n.oid = t.relnamespace
where t.relkind = 'r'
order by
t.relname,
i.relname,
array_position(ix.indkey, a.attnum)
)
select *
from ind_cols
where schema_name = 'test'
and table_name = 'indextest'
order by schema_name, table_name
;
这将给出如下输出:
schema_name | table_name | index_name | column_name | column_position
-------------+------------+------------+-------------+-----------------
test | indextest | testind1 | singleindex | 1
test | indextest | testind2 | firstoftwo | 1
test | indextest | testind2 | secondoftwo | 2
(3 rows)
如果你想保持索引中的列顺序，这里有一个(非常丑陋的)方法:
select table_name,
index_name,
array_agg(column_name)
from (
select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
unnest(ix.indkey) as unn,
a.attnum
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.relnamespace = <oid of the schema you're interested in>
order by
t.relname,
i.relname,
generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name, index_name
列顺序存储在pg_index中。indkey列，我是根据这个数组的下标排序的。
请尝试下面的查询以深入到所需的索引
查询如下-我亲自尝试过，并经常使用它。
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;
