我想在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列,我是根据这个数组的下标排序的。

其他回答

\d table_name显示了来自psql的这些信息,但是如果你想使用SQL从数据库中获取这些信息,那么可以看看从PostgreSQL中提取META信息。

我在我的实用程序中使用这些信息来报告来自db schema的一些信息,以比较测试和生产环境中的PostgreSQL数据库。

稍微修改一下@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

@cope360的精彩回答,转换为使用连接语法。

select t.relname as table_name
     , i.relname as index_name
     , array_to_string(array_agg(a.attname), ', ') as column_names
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 'test%'
group by t.relname
       , i.relname
order by t.relname
       , i.relname
;

延伸到@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));

列出索引的索引和列:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname 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 like 'test%'
order by
    t.relname,
    i.relname;

 table_name | index_name | column_name
------------+------------+-------------
 test       | pk_test    | a
 test       | pk_test    | b
 test2      | uk_test2   | b
 test2      | uk_test2   | c
 test3      | uk_test3ab | a
 test3      | uk_test3ab | b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

卷起列名:

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
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 'test%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

 table_name | index_name | column_names
------------+------------+--------------
 test       | pk_test    | a, b
 test2      | uk_test2   | b, c
 test3      | uk_test3ab | a, b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c