我想在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)。


当前回答

只需要执行:\d table_name

但是我不确定你说没有列的信息是什么意思。

例如:

# \d pg_class
       Table "pg_catalog.pg_class"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 relname         | name      | not null
 relnamespace    | oid       | not null
 reltype         | oid       | not null
 reloftype       | oid       | not null
 relowner        | oid       | not null
 relam           | oid       | not null
 relfilenode     | oid       | not null
 reltablespace   | oid       | not null
 relpages        | integer   | not null
 reltuples       | real      | not null
 reltoastrelid   | oid       | not null
 reltoastidxid   | oid       | not null
 relhasindex     | boolean   | not null
 relisshared     | boolean   | not null
 relistemp       | boolean   | not null
 relkind         | "char"    | not null
 relnatts        | smallint  | not null
 relchecks       | smallint  | not null
 relhasoids      | boolean   | not null
 relhaspkey      | boolean   | not null
 relhasexclusion | boolean   | not null
 relhasrules     | boolean   | not null
 relhastriggers  | boolean   | not null
 relhassubclass  | boolean   | not null
 relfrozenxid    | xid       | not null
 relacl          | aclitem[] |
 reloptions      | text[]    |
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

它清楚地显示给定索引在该表上的列。

其他回答

延伸到@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 INDEX语句,以便您可以删除它们并在以后作为管理流程的一部分重新创建(这是我的情况)。在这种情况下,您可以使用pg_dump仅转储post-data部分,然后从其中grep 'CREATE INDEX'。

PGPASSWORD=<pwd> pg_dump --host <host> --port <port> -U <user> -d <database> --section=post-data > post-data.sql
grep 'CREATE INDEX' postdata.sql > create_index.sql

这可能是有帮助的,因为一些索引可能很难重新创建,例如那些使用特定op类的索引(例如gin_trgm_ops)或其他一些 我通常很难重新开始。

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

原始信息在pg_index中。

类似于接受的答案,但有左连接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;