如何在PostgreSQL中查询索引的元数据 [英] How to query the metadata of indexes in PostgreSQL

问题描述

我需要能够查询PostgreSQL数据库以获得有关当前索引及其详细信息的信息.

I need to be able to query a PostgreSQL database to obtain information about the indexes present and their details.

在SQL Server上，我可以执行以下操作以获取所有索引的所有表/索引/列的列表:

On SQL Server, I can do the following to get a list of all tables/indexes/columns for all indexes:

select TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME
from INFORMATION_SCHEMA.STATISTICS
where TABLE_SCHEMA = 'my_schema'
order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX

似乎INFORMATION_SCHEMA的STATISTICS表是SQL Server扩展.如何在PostgreSQL中做等效的事情?

It seems that the STATISTICS table of INFORMATION_SCHEMA is a SQL Server extension. How can I do the equivalent in PostgreSQL?

我正在专门尝试返回如下所示的非规范化结果集

TableName, IndexName, UniqueFl, ColumnName

因此，我为所有索引中的每一列返回了一行.

So I get a row back for each column in all indexes.

谢谢， 乔恩

推荐答案

我认为从information_schema来看这是不可能的 请参见此讨论.除了从约束之外创建的索引将不在信息模式中.

I don't think this is possible from the information_schema see this discussion. Indexes created other than from constraints won't be in the information schema.

但是，从系统表中可以 请参见此问题

However from the system tables you can see this question

这篇关于如何在PostgreSQL中查询索引的元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！