I need to get metadata about all MATERIALIZED VIEW columns, including their data type. I can do this with this query:


trim(leading '_' from pg_type.typname) as data_type,
pg_namespace.nspname as schema_name, 
pg_namespace.nspname as udt_name, 
attnum AS ordinal_position,
pg_attribute.attname as column_name,
pg_class.relname as matview_name, 
'-------' AS spacer,

from pg_catalog.pg_attribute
join pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
join pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
join pg_catalog.pg_type ON pg_type.typelem = pg_attribute.atttypid
    pg_class.relkind = 'm' AND
    not pg_attribute.attisdropped and 
    cast(pg_type.typanalyze as text) = 'array_typanalyze' and 
    pg_attribute.attnum > 0
order by 

However it doesn't list any array columns, due to them not existing in the pg_catalog.pg_type table (which is the table I'm getting the data types from).

How can I also get metadata (including type) for array columns too?

I'm using postgres 13.3.


You can make your life easier using the format_type() function:

SELECT format_type(att.atttypid, att.atttypmod) as data_type, 
       nsp.nspname as schema_name, 
       att.attnum AS ordinal_position,
       att.attname as column_name,
       mv.relname as matview_name
from pg_catalog.pg_attribute att
  join pg_catalog.pg_class mv ON mv.oid = att.attrelid
  join pg_catalog.pg_namespace nsp ON nsp.oid = mv.relnamespace
where mv.relkind = 'm' 
  AND not att.attisdropped 
  and att.attnum > 0
order by att.attnum

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.