PostgreSQL - 获取物化视图列元数据 [英] PostgreSQL - get materialized view column metadata
本文介绍了PostgreSQL - 获取物化视图列元数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这类似于物化视图的列数据类型?但我需要更多数据(不仅仅是数据类型).我想对表/视图执行相同类型的查询,但对实体化视图执行.
This is similar to Column data types for materialized views? but I need more data (not just data type). I would like to have the same kind of query that I do for tables/views but for materialized views.
SELECT column_name, data_type, character_maximum_length,
character_octet_length, numeric_precision, numeric_precision_radix,
numeric_scale, datetime_precision, interval_type, interval_precision
FROM information_schema.columns
WHERE table_schema = '{}'
AND table_name = '{}'
order by ordinal_position
有人遇到过这种情况吗?pg_attribute 中的列名非常神秘.
Does anyone have something like this? Column names in pg_attribute are very cryptic.
推荐答案
在运行带有 -E
("回显隐藏查询") 选项.
Queries for this kind of question can easily be retrieve when running psql
with the -E
("echo hidden queries") option.
以下查询应该可以满足您的要求:
The following query should do what you want:
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attnotnull
FROM pg_attribute a
JOIN pg_class t on a.attrelid = t.oid
JOIN pg_namespace s on t.relnamespace = s.oid
WHERE a.attnum > 0
AND NOT a.attisdropped
AND t.relname = 'mv_name' --<< replace with the name of the MV
AND s.nspname = 'public' --<< change to the schema your MV is in
ORDER BY a.attnum;
这篇关于PostgreSQL - 获取物化视图列元数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文