How to List Views (CREATE VIEW) Without Materialized Views in Amazon Redshift?


I'm working with Amazon Redshift, which is based on PostgreSQL 8.0.2, and I'm implementing a functionality to list all the views of a given schema while excluding materialized views created with "CREATE MATERIALIZED VIEW."

In PostgreSQL, it's relatively straightforward to differentiate between regular views and materialized views as they are stored with different values in the pg_catalog.pg_class table under the relkind column (i.e., 'v' for views and 'm' for materialized views).

However, in Amazon Redshift, both regular views and materialized views are stored with relkind as 'v'. To differentiate, Redshift provides a system table called SVV_MV_INFO that exclusively lists materialized views, but there doesn't appear to be an equivalent table to list regular views.

I attempted to retrieve all views from the pg_class table and then filter out materialized views by joining it with SVV_MV_INFO, but I encountered an error:

Query 1: To fetch all views from pg_catalog

SELECT c.oid, c.relname AS name
FROM pg_class c
WHERE c.relkind = 'v'::char AND c.relnamespace = [schema oid, eg:101]::oid
ORDER BY c.relname;

Query 2: To fetch just materialized views

FROM svv_mv_info
WHERE schema_name = '[schema name, eg:abc]';

Query 3: After joining both Query 1 and Query 2

SELECT c.oid, c.relname AS name
FROM pg_class c
WHERE c.relkind = 'v'::char AND c.relnamespace = 101::oid
          SELECT 1
          FROM svv_mv_info s
          WHERE s.schema_name = 'abc' AND c.relname =
ORDER BY c.relname;

The error I encountered is (for query 3):

[Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.

Is there a method to fetch just regular views (created with "CREATE VIEW") from Amazon Redshift without encountering this error?

Any insights or suggestions on how to achieve this would be greatly appreciated. Thank you.

asked 8 months ago807 views
1 Answer

Following query will give you list of all views excluding materialized views.

select table_schema as schema_name,
       table_name as view_name,
from information_schema.views
where table_schema in ('public')
and view_definition not like '%MATERIALIZED VIEW%'
order by schema_name,
answered 7 months ago
  • Kindly use ilike instead of like and this query should work fine

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions