我想在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)
下面是一个包装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>')
类似于接受的答案，但有左连接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;
select t.relname as table_name,
i.relname as index_name,
array_position(ix.indkey,a.attnum) pos,
a.attname as column_name
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 'orders'
order by t.relname, i.relname, array_position(ix.indkey,a.attnum)
