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.

davidsbro · Accepted Answer · 2022-01-05 15:30:44Z

37

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 5, 2022 at 15:30

davidsbro

2,7674 gold badges27 silver badges36 bronze badges

answered Aug 27, 2016 at 9:17

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Andrey Vykhodtsev Over a year ago

Hi @a_horse_with_no_name, would you be so kind to share the full command you used to get this query?

user330315 Over a year ago

@AndreyVykhodtsev: as I have written: run psql with the -E option then, use \d mv_name to show the columns.

rocksteady · Accepted Answer · 2019-04-25 21:18:33Z

4

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

answered Apr 25, 2019 at 21:18

rocksteady

2,3821 gold badge16 silver badges20 bronze badges

1 Comment

Rob Over a year ago

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: gist.github.com/banagale/655612942f58ade246af4fcb6c7c2814

Collectives™ on Stack Overflow

PostgreSQL - get materialized view column metadata

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related