从 PostgreSQL
中的 information_schema 和 pg_catalog 可以检索到大量信息。我想检索关于某个索引引用的列的信息,类似于在 sqlite3 中使用 pragma index_info(<index_name>)
实现的内容。如何在不解析 create index
语句的情况下实现这一目标?
从 PostgreSQL
中的 information_schema 和 pg_catalog 可以检索到大量信息。我想检索关于某个索引引用的列的信息,类似于在 sqlite3 中使用 pragma index_info(<index_name>)
实现的内容。如何在不解析 create index
语句的情况下实现这一目标?
这些东西很容易找到。
只需使用-E
选项运行psql,它就会显示所使用的SQL语句。因此,在运行\d index_name时,以下语句(等等)将用于检索索引列:
SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT SUBSTRING(pg_catalog.pg_get_expr(d.adbin,d.adrelid) FOR 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND NOT d.adisdropped), a.attnotnull, a.attnum, pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT oid FROM pg_class WHERE relname = 'index_name') AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;
\d
来显示索引信息,这个非常有帮助,还有-E
选项。非常感谢。 - gruszczy接受的答案对我没有用(执行时出错)。
无论如何,您可以列出数据库中的所有列,并以某种方式标记所有索引列(在注释中提到了限制结果行集的能力):
WITH
table_select as (
select row_number() over(ORDER BY relname) as rownum,
c.relname, c.oid, c.reltuples
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'r'::"char"
--AND n.nspname = '%MyNameSpaceHere%'
ORDER BY c.relname
),
indxs as (
select distinct t.relname as table_name, a.attname 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 like 'mytable here'
and cast (i.oid::regclass as text) like '%MyNameSpaceHere%'
order by
t.relname --, i.relname
),
cols as (
select a.attname, a.attrelid, c.oid, col.TABLE_NAME, col.COLUMN_NAME
FROM table_select c
JOIN pg_attribute a ON (a.attrelid = c.oid) AND (a.attname <> 'tableoid')
LEFT JOIN information_schema.columns col ON
(col.TABLE_NAME = c.relname AND col.COLUMN_NAME = a.attname )
WHERE
( a.attnum >= 0 ) --attnum > 0 for real columns
)
--select * from table_select t
select c.TABLE_NAME, c.COLUMN_NAME,
case when i.column_name is not null then 'Y' else '' end as is_indexed
from cols c
left join indxs i on (i.table_name = c.table_name and i.column_name = c.column_name)
示例结果:
table_name column_name is_indexed
'events id "Y"
events type "Y"
events descr "" '