List All Views in a PostgreSQL Database with SQL

calendar Apr 7, 2026 / · 3 min read · postgresql administration schema database information_schema pg_views pg_matviews pg_depend  ·
Share on: linkedin copy

How to List All Views in a PostgreSQL Database

Views are saved SQL queries stored in the database. A production database can accumulate dozens or hundreds of views over time — many created by developers, some by tools, and some that are no longer used. Knowing what views exist, which schema they belong to, and what they actually do is essential for documentation, auditing, and cleanup.

This SQL script queries information_schema.views to return every view in the current database, along with its schema and full SQL definition.

SQL Script

 1SELECT
 2    table_schema  AS view_schema,
 3    table_name    AS view_name,
 4    view_definition
 5FROM
 6    information_schema.views
 7WHERE
 8    table_schema NOT IN ('pg_catalog', 'information_schema')
 9ORDER BY
10    table_schema,
11    table_name;

Notes: Works on any version of PostgreSQL. Returns one row per view. The view_definition column contains the full SQL text of each view as stored by PostgreSQL. System views in pg_catalog and information_schema are excluded.

Code Breakdown

information_schema.views

A standard SQL view that lists every view accessible to the current user in the current database. Columns include the schema name, the view name, and the SQL text of the view definition.

table_schema NOT IN ('pg_catalog', 'information_schema')

Excludes PostgreSQL internal views. Without this filter, you would see hundreds of system views alongside your own.

table_schema AS view_schema, table_name AS view_name

Column aliases to make the output self-explanatory. information_schema.views uses the table_schema and table_name naming convention even for views.

view_definition

The full SQL text of the view as stored in the system catalog. PostgreSQL normalises this text when storing it — the formatting may differ from what was originally written with CREATE VIEW.

ORDER BY table_schema, table_name

Groups views by schema first, then sorts alphabetically within each schema. Useful when a database has multiple application schemas.

Key Points

  • Only views accessible to the current user are returned. Superusers see all views.
  • The view_definition text is the normalised form PostgreSQL stores internally, not the original CREATE VIEW statement verbatim.
  • Views in the public schema appear with view_schema = 'public'.
  • An empty result means the current database has no user-created views.

Insights and Explanations

Views are invisible overhead unless you actively track them. Common problems found during a view audit:

  • Stale views that reference tables or columns that were dropped or renamed. These views exist in the catalog but will error when queried.
  • Redundant views created by multiple developers for the same purpose under slightly different names.
  • Undocumented views used by applications but never added to schema documentation or migration scripts.
  • Security-sensitive views that expose more columns than intended to lower-privileged users.

To check whether a view is still valid, use:

1EXPLAIN SELECT * FROM your_schema.your_view_name;

If the view references a missing table or column, EXPLAIN will return an error and identify the problem.

To get a more readable view definition, use pg_get_viewdef():

 1SELECT
 2    schemaname,
 3    viewname,
 4    pg_get_viewdef(schemaname || '.' || viewname, true) AS definition
 5FROM
 6    pg_views
 7WHERE
 8    schemaname NOT IN ('pg_catalog', 'information_schema')
 9ORDER BY
10    schemaname,
11    viewname;

The second argument true adds line breaks to make the output more readable.

Additional Considerations

  • Permissions: Any user with access to information_schema can run this query. Results are filtered to views the user has SELECT privilege on.
  • Materialized views: This query does not return materialized views. Those are stored in pg_matviews and are not part of information_schema.views. Query pg_matviews separately if needed.
  • Dependent views: Views can depend on other views. Dropping a view that another view depends on will fail unless you use CASCADE. Use pg_depend to map view dependencies before dropping anything.

References

Posts in this series