PostgreSQL - 获取物化视图列元数据

这类似于物化视图的列数据类型？ https://stackoverflow.com/questions/31119260/column-data-types-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 中的列名非常神秘。

运行时可以轻松检索此类问题的查询psql与-E（“回显隐藏查询”）选项。

以下查询应该执行您想要的操作：

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;