How to list all views in SQL in PostgreSQL?

Question

How do I list all views for a database using an SQL command in PostgreSQL?

I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,

SELECT ...;
my_view_1
my_view_2
my_view_3

I'm running PostgreSQL v9.1.4 on Ubuntu Linux.

Answer 1

From the documentation:

 select table_name from INFORMATION_SCHEMA.views;

If you don't want the system views is your result, try this:

 select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))

• Rob Bednark: Thanks @Phil. However, that command returns 128 rows, whereas \dv returns 57 rows. It seems to be giving me system views as well, like "tables", "columns", "domains", "pg_role", etc. How do I get just the views that I created?
• Philᵀᴹ: It gives you a list of the ones you have access to. To get ones for a given schema, add where table_schema='USERNAME'to the query
• András Váczi: @phil This works only if there is a schema named identically to the user. By default this is not the case, however, there is the public schema.
• Cao Minh Tu: INFORMATION_SCHEMA.views just shows the views that current user has right(s) on. If there are views in the database that current user doesn't have right(s) on, the name of those views will not show in the result. From the document in @Phil's link: Only those views are shown that the current user has access to (by way of being the owner or having some privilege).

Answer 2

You can query pg_catalog.pg_views for your desired information:

select viewname from pg_catalog.pg_views;

Refined query to get schema name also - just in case you have multiple views with the same name in different schemas - and left out those system views:

select schemaname, viewname from pg_catalog.pg_views
where schemaname NOT IN ('pg_catalog', 'information_schema')
order by schemaname, viewname;

IMHO, this way is better than query INFORMATION_SCHEMA.views for reasons stated in my comment to Phil's answer.

Answer 3

If you only need this interactively while in psql, you can also use \dv to show views, or \dm for materialized views. Or use with +, like \dm+ for example to show some additional information (mostly useful to see materialized view size).

• Pak: \dv *.* and \dm *.* for those information on all schemas !
• F. P. Freely: Use \sv ... to view the source.

Answer 4

Try:

SELECT n.nspname AS table_schema,
 pg_catalog.pg_get_userbyid(c.relowner) AS table_owner,
 c.relname AS table_name
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
 WHERE c.relkind = 'v'
;

If you want more detail you can modify the following to suit your needs:

SELECT n.nspname AS table_schema,
 pg_catalog.pg_get_userbyid(c.relowner) AS table_owner,
 c.relname AS table_name,
 s.n_live_tup AS row_count,
 count (a.attname) AS column_count,
 pg_catalog.obj_description(c.oid, 'pg_class') AS comments,
 CASE c.relkind
 WHEN 'v'
 THEN pg_catalog.pg_get_viewdef(c.oid, true)
 ELSE null
 END AS query
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
 LEFT JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid AND a.attnum > 0 AND NOT a.attisdropped)
 LEFT JOIN pg_catalog.pg_stat_all_tables s ON (c.oid = s.relid)
 WHERE c.relkind = 'v'
GROUP BY n.nspname,
 c.relowner,
 c.relkind,
 c.relname,
 s.n_live_tup,
 c.oid
ORDER BY n.nspname,
 c.relname
;

Answer 5

Here's a query that will bring up your materialized views as well and show you the views' dependencies.

-- Get a list of views that have dependencies on other views
with view_oids as (
 select
 distinct(dependent_view.oid) as view_oid
 from pg_depend
 JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
 JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
 JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
 WHERE
 dependent_ns.nspname = 'public'
), view_dependencies as (
 select
 dependent_view.oid as dependent_oid,
 dependent_ns.nspname as dependent_schema,
 dependent_view.relname as dependent_view,
 source_table.oid as dependency_oid,
 source_ns.nspname as source_schema,
 source_table.relname as source_view
 from pg_depend
 JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
 JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
 JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
 JOIN view_oids on source_table.oid = view_oids.view_oid
 JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
 JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
 WHERE
 source_ns.nspname = 'public'
 group by
 dependent_view.oid,
 dependent_ns.nspname,
 dependent_view.relname,
 source_table.oid,
 source_ns.nspname,
 source_table.relname
)
select 
 view_dependencies.*
from view_dependencies
;

• SebastianH: This is a really really great post! I stumbled upon this question when I was looking for a way to analyse view dependencies in postgres. Many thanks for this input. It saved me hours of work to figure this out. For those interested: I changed the SQL a bit to include Views and Tables as source objects, then exported the Information to Excel and Imported it in the graph editor yEd. The result is a great visualization of the view dependency tree.

Answer 6

I created a view to list a catalog of views:

create or replace view show_views as 
select table_name from INFORMATION_SCHEMA.views 
WHERE table_schema = ANY (current_schemas(false));

And when I want to see all views in the database I write:

select * from show_views;

Answer 7

This can show all user-defined views in the current database:

\dv

And, this can show all user-defined views in the current database in detail:

\dv+

*There is no way to show all user-defined views in all databases at once.

In addition, this can show all user-defined and system views in the current database:

\dvS

And, this can show all user-defined and system views in the current database in detail:

\dvS+

And, you can show all user-defined and system views in the current database with information_schema.views and pg_proc as shown below:

SELECT table_name FROM information_schema.views;

SELECT viewname FROM pg_views;