PostgreSQL - get materialized view column metadata

Question

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

Does anyone have something like this? Column names in pg_attribute are very cryptic.

score 28 · Accepted Answer · edited Jan 05 '22 at 15:30

28

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;

edited Jan 05 '22 at 15:30

davidsbro

2,761
4
23
33

answered Aug 27 '16 at 09:17

1

Hi @a_horse_with_no_name, would you be so kind to share the full command you used to get this query? – Andrey Vykhodtsev Aug 31 '16 at 08:32
3

@AndreyVykhodtsev: as I have written: run `psql` with the `-E` option then, use `\d mv_name` to show the columns. – Aug 31 '16 at 08:35

score 3 · Answer 2 · answered Apr 25 '19 at 21:18

I spent some time on this today - I was building a metadata view that showed all schemas, tables, and columns in a database.

I had to do a bit of digging to make the metadata for my materialized views available in the same way as tables or regular views (since materialized views are not included in information_schema), but here's where I ended up:

SELECT pg_namespace.nspname AS table_schema
    , pg_class.relname AS table_name
    , 'materialized view'::TEXT AS table_type
    , pg_attribute.attname AS column_name
    , pg_attribute.attnum AS ordinal_position
FROM pg_catalog.pg_class
    INNER JOIN pg_catalog.pg_namespace
        ON pg_class.relnamespace = pg_namespace.oid
    INNER JOIN pg_catalog.pg_attribute
        ON pg_class.oid = pg_attribute.attrelid
-- Keeps only materialized views, and non-db/catalog/index columns 
WHERE pg_class.relkind = 'm'
    AND pg_attribute.attnum >= 1
ORDER BY table_schema
    , table_name
    , column_name

Thanks for this. Note, this can be tweaked into letting you search column names by adding `AND pg_attribute.attname like '%[SEARCH_KEYWORD]%'` to the `WHERE` section. Snippet here: https://gist.github.com/banagale/655612942f58ade246af4fcb6c7c2814 — Rob, Nov 17 '22 at 03:59

PostgreSQL - get materialized view column metadata

2 Answers2

Linked