CODEWITHSUNDEEP
postgresqlmaterialized-views
1969877
1969877

Reputation: 303

Column names and data types for materialized views in PostgreSQL?

For general tables and views, I can see their data type by running the following query:

select data_type from information_schema.columns
where .....

However it does not seem that any information about materialized views appear here.

I am able to get a list of columns for a materialized view by running:

    SELECT
      a.attname as column_name
  FROM
      pg_catalog.pg_attribute a
      INNER JOIN
       (SELECT c.oid,
          n.nspname,
          c.relname
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ ('^(materializedview)$')
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 2, 3) b
      ON a.attrelid = b.oid
      INNER JOIN
       (SELECT
            a.attrelid,
            max(a.attnum) as max_attnum
        FROM pg_catalog.pg_attribute a
        WHERE a.attnum > 0
          AND NOT a.attisdropped
        GROUP BY a.attrelid) e
      ON a.attrelid=e.attrelid
  WHERE a.attnum > 0
    AND NOT a.attisdropped
  ORDER BY a.attnum

But, I have not been able to figure out if I can determine what the underlying column/data type is.

Is there a way to view this information?

Upvotes: 7

Views: 4180

Answers (1)

André C. Andersen
André C. Andersen

Reputation: 9375

I think you're very close. Last step would be to join with pg_type:

join pg_catalog.pg_type as tp on tp.oid = attr.atttypid

The field tp.typname would have the datatype.

The following query gets column datatypes using namespace (e.g., schema) and relation (e.g., materialized view) name:

select 
    attr.attnum,
    ns.nspname as schema_name, 
    cls.relname as table_name, 
    attr.attname as column_name,
    tp.typname as datatype
from pg_catalog.pg_attribute as attr
join pg_catalog.pg_class as cls on cls.oid = attr.attrelid
join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace
join pg_catalog.pg_type as tp on tp.oid = attr.atttypid
where 
    ns.nspname = 'your_schema'
    and cls.relname = 'your_materialized_view'
    and attr.attnum >= 1
order by 
    attr.attnum

You have to change 'your_schema'and 'your_materialized_view'.

Upvotes: 10

Related Questions