在PostgreSQL中列出带索引的列

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

当前回答

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL(显示索引)

SHOW INDEX FROM mytable;

2017-02-10 03:15:43

其他回答

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

2016-11-12 09:45:53

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

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

2010-02-05 07:03:58

有一个简单的解决方案:

SELECT 
  t.relname table_name,
  ix.relname index_name,
  indisunique,
  indisprimary, 
  regexp_replace(pg_get_indexdef(indexrelid), '.*\((.*)\)', '\1') columns
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE t.relname LIKE 'test%'

2015-12-10 01:20:27

我认为这个版本在这个线程上还不存在:它提供了列名列表和索引的ddl。

CREATE OR REPLACE VIEW V_TABLE_INDEXES AS

SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,i.indisunique AS "is_unique"
    ,array_to_string(array_agg(a.attname), ', ') as "columns"
    ,pg_get_indexdef(i.indexrelid) as "ddl"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE c.relkind = 'i'
      and n.nspname not in ('pg_catalog', 'pg_toast')
      and pg_catalog.pg_table_is_visible(c.oid)
GROUP BY
    n.nspname
    ,t.relname
    ,c.relname
    ,i.indisunique
    ,i.indexrelid
ORDER BY
    n.nspname
    ,t.relname
    ,c.relname;

我发现使用函数的索引不链接到列名，所以偶尔你会发现一个索引列表，例如一个列名，而实际上是使用3。

例子:

CREATE INDEX ui1 ON table1 (coalesce(col1,''),coalesce(col2,''),col3)

该查询仅返回'col3'作为索引上的列，但DDL显示了索引中使用的全部列。

2017-02-02 19:47:23

\d tablename显示了8.3.8版本中的列名。

 "username_idx" UNIQUE, btree (username), tablespace "alldata1"

2010-02-05 00:00:26

在PostgreSQL中列出带索引的列

推荐文章

最新文章

标签