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.

질문됨 6달 전599회 조회
1개 답변

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,
답변함 6달 전
  • Kindly use ilike instead of like and this query should work fine

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인