我想在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)。
类似于接受的答案，但有左连接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
table_name,
index_name,
string_agg(column_name, ',')
FROM (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
(SELECT i
FROM (SELECT
*,
row_number()
OVER () i
FROM unnest(indkey) WITH ORDINALITY AS a(v)) a
WHERE v = 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.relname LIKE 'tablename'
ORDER BY table_name, index_name, i
) raw
GROUP BY table_name, index_name
延伸到@Cope360的好答案。要获取某个表(如果它们是相同的表名但不同的模式)，只需使用表OID。
select
t.relname as table_name
,i.relname as index_name
,a.attname as column_name
,a.attrelid tableid
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 like 'tbassettype'
and a.attrelid = '"dbLegal".tbassettype'::regclass
order by
t.relname,
i.relname;
解释:我有表名'tbassettype'在两个模式'dbAsset'和'dbLegal'。要在dbLegal上只获取表，只需让a.attrelid =它的OID。
一些样本数据…
create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c), constraint uk_test3ab unique (a, b));
使用pg_get_indexdef函数:
select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test'::regclass;
pg_get_indexdef
--------------------------------------------------------
CREATE UNIQUE INDEX pk_test ON test USING btree (a, b)
(1 row)
select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test2'::regclass;
pg_get_indexdef
----------------------------------------------------------
CREATE UNIQUE INDEX uk_test2 ON test2 USING btree (b, c)
(1 row)
select pg_get_indexdef(indexrelid) from pg_index where indrelid ='test3'::regclass;
pg_get_indexdef
------------------------------------------------------------
CREATE UNIQUE INDEX uk_test3b ON test3 USING btree (b)
CREATE UNIQUE INDEX uk_test3c ON test3 USING btree (c)
CREATE UNIQUE INDEX uk_test3ab ON test3 USING btree (a, b)
(3 rows)
