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?
I think you're very close. Last step would be to join with
pg_type
:The field
tp.typname
would have the datatype. Although it seems a filter must be added to removeline
datatypes, whatever that is:I don't fully understand the underlying data model, so use my solution below with a grain of salt. Anyway, based on your contribution I ended up with the following query which gets column datatypes using namespace (e.g., schema) and relation (e.g., materialized view) name:
You have to change
'your_schema'
and'your_materialized_view'
.