Movatterモバイル変換

[0]ホーム
URL:

Add Neon Auth to your app without leaving Cursor or Claude
/PostgreSQL Views/Listing Views

PostgreSQL List Views

Summary: in this tutorial, you will learn how to list views in the PostgreSQL database server using thepsql 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 inpsql or query theinformation_schema.views andpg_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 followingpsql command:

psql -U postgres

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

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

\c dvdrental

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

\dv

In this command,dv stands fordisplayviews. 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)

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 isview 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_nameFROM  information_schema.viewsWHERE  table_schema NOT IN (    'information_schema', 'pg_catalog'  )ORDER BY  table_schema,  table_name;

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)

Listing materialized views

To retrieve allmaterialized views, you can query them from thepg_matviews view:

SELECT* FROM pg_matviews;

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)

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_nameFROM  pg_matviewsORDER BY  materialized_view_name;

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 thepsql program.
  • Use theinformation_schemas.views view to retrieve information about views.
  • Use thepg_matviews view to obtain the materialized views.

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp