postgres mysql 索引_列出PostgreSQL数据库的所有索引名称,列名称及其表名

这将输出所有包含详细信息的索引(从我的视图定义中提取)：

SELECT i.relname as indname,

i.relowner as indowner,

idx.indrelid::regclass,

am.amname as indam,

idx.indkey,

ARRAY(

SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)

FROM generate_subscripts(idx.indkey, 1) as k

ORDER BY k

) as indkey_names,

idx.indexprs IS NOT NULL as indexprs,

idx.indpred IS NOT NULL as indpred

FROM pg_index as idx

JOIN pg_class as i

ON i.oid = idx.indexrelid

JOIN pg_am as am

ON i.relam = am.oid;

可以向结尾添加一个额外的连接，以修剪命名空间：

SELECT i.relname as indname,

i.relowner as indowner,

idx.indrelid::regclass,

am.amname as indam,

idx.indkey,

ARRAY(

SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)

FROM generate_subscripts(idx.indkey, 1) as k

ORDER BY k

) as indkey_names,

idx.indexprs IS NOT NULL as indexprs,

idx.indpred IS NOT NULL as indpred

FROM pg_index as idx

JOIN pg_class as i

ON i.oid = idx.indexrelid

JOIN pg_am as am

ON i.relam = am.oid

JOIN pg_namespace as ns

ON ns.oid = i.relnamespace

AND ns.nspname = ANY(current_schemas(false));