我想在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 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列,我是根据这个数组的下标排序的。
类似于接受的答案,但有左连接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的回答:
create table test (a int, b int, c int, constraint pk_test primary key(c, a, b));
select i.relname as index_name,
ix.indisunique as is_unique,
a.attname as column_name,
from pg_class c
inner join pg_index ix on c.oid=ix.indrelid
inner join pg_class i on ix.indexrelid=i.oid
inner join pg_attribute a on a.attrelid=c.oid and a.attnum=any(ix.indkey)
where c.oid='public.test'::regclass::oid
order by array_position(ix.indkey, a.attnum) asc;
这将显示索引列的正确顺序:
index_name is_unique column_name
pk_test true c
pk_test true a
pk_test true b