PostgreSQL List Views

Summary: in this tutorial, you will learn how to list views in the PostgreSQL database server using the psql command and SQL statements.

In PostgreSQL, views are named queries stored directly within the database server. These views allow you to encapsulate complex SQL queries, enabling you to retrieve specific subset data from underlying tables easily.

PostgreSQL offers some options for listing views within the current database. You can either use the \dv command in psql or query the information_schema.views and pg_matviews views directly.

Listing views using psql

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL using the following psql command:

psql -U postgresCode language: plaintext (plaintext)

It’ll prompt you to enter a password for the postgres user.

Second, change the current database to the desired one where you want to list the views, for example, dvdrental:

\c dvdrentalCode language: plaintext (plaintext)

Finally, list the view in the database using the \dv command:

\dvCode language: plaintext (plaintext)

In this command, dv stands for display views. The \dv command allows you to quickly examine the views in the database without having to write SQL queries.

Output:

                   List of relations
 Schema |            Name            | Type |  Owner
--------+----------------------------+------+----------
 public | actor_info                 | view | postgres
 public | contact                    | view | postgres
 public | customer_info              | view | postgres
 public | customer_list              | view | postgres
 public | customer_usa               | view | postgres
 public | film_list                  | view | postgres
 public | nicer_but_slower_film_list | view | postgres
 public | sales_by_film_category     | view | postgres
 public | sales_by_store             | view | postgres
 public | staff_list                 | view | postgres
(10 rows)Code language: plaintext (plaintext)

The output has four columns:

• Schema: Indicates the schema of the view. When you create a view without a schema, it defaults to public.
• Name: Specifies the name of the view.
• Type: Denotes the type of the object, which is view in this case.
• Owner: Shows the user account that created the view.

Listing view using SQL statement

PostgreSQL offers various database views that contain information about objects defined in the current database through the information schema.

To retrieve the information about database views, you can execute the following SQL statement:

SELECT 
  table_schema, 
  table_name
FROM 
  information_schema.views 
WHERE 
  table_schema NOT IN (
    'information_schema', 'pg_catalog'
  ) 
ORDER BY 
  table_schema, 
  table_name;Code language: SQL (Structured Query Language) (sql)

The output will display the schema and the name of the views:

 table_schema |         table_name
--------------+----------------------------
 public       | actor_info
 public       | contact
 public       | customer_info
 public       | customer_list
 public       | customer_usa
 public       | film_list
 public       | nicer_but_slower_film_list
 public       | sales_by_film_category
 public       | sales_by_store
 public       | staff_list
 web          | film_rating
(11 rows)Code language: plaintext (plaintext)

Listing materialized views

To retrieve all materialized views, you can query them from the pg_matviews view:

SELECT * FROM pg_matviews\GCode language: SQL (Structured Query Language) (sql)

Output:

 schemaname |    matviewname     | matviewowner | tablespace | hasindexes | ispopulated |                          definition
------------+--------------------+--------------+------------+------------+-------------+---------------------------------------------------------------
 public     | rental_by_category | postgres     | null       | t          | t           |  SELECT c.name AS category,                                  +
            |                    |              |            |            |             |     sum(p.amount) AS total_sales                             +
            |                    |              |            |            |             |    FROM (((((payment p                                       +
            |                    |              |            |            |             |      JOIN rental r ON ((p.rental_id = r.rental_id)))         +
            |                    |              |            |            |             |      JOIN inventory i ON ((r.inventory_id = i.inventory_id)))+
            |                    |              |            |            |             |      JOIN film f ON ((i.film_id = f.film_id)))               +
            |                    |              |            |            |             |      JOIN film_category fc ON ((f.film_id = fc.film_id)))    +
            |                    |              |            |            |             |      JOIN category c ON ((fc.category_id = c.category_id)))  +
            |                    |              |            |            |             |   GROUP BY c.name                                            +
            |                    |              |            |            |             |   ORDER BY (sum(p.amount)) DESC;
(1 row)Code language: PHP (php)

The output includes detailed information about materialized views, including their definitions.

If you solely want to get the names of the materialized view, you can use the following query:

SELECT 
  matviewname AS materialized_view_name 
FROM 
  pg_matviews 
ORDER BY 
  materialized_view_name;Code language: SQL (Structured Query Language) (sql)

This query will return only the names of the materialized views.

Output:

 materialized_view_name
------------------------
 rental_by_category
(1 row)

Summary

• Utilize the \dv command to list all views of a database using the psql program.
• Use the information_schemas.views view to retrieve information about views.
• Use the pg_matviews view to obtain the materialized views.