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)

下面是一个包装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>')

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL(显示索引)

SHOW INDEX FROM mytable;

原始信息在pg_index中。

只需要执行:\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)

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

也许您希望获得实际的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)或其他一些 我通常很难重新开始。